Измерение времени или календарь присутствует практически в любом аналитическом приложении и очень часто используется при составлении отчетов. Например, Аналитическое приложение может содержать значения показателей за определенный период времени или отражать динамику показателей в течение определенного периода времени. И в том, и в другом случае потребуется измерение времени, чтобы задать и отображать временные интервалы.
В этом посте я расскажу об основах и особенностях создания календаря в QlikView.
Сначала познакомимся с тем, как реализовано:
- хранение временных данных в QlikView,
- затем создадим календарь с помощью скриптов QlikView
- и рассмотрим возможные варианты календаря (финансовый и альтернативный календари).
Представление временных данных в QlikView
Для того чтобы лучше понимать и использовать возможности управления данными в QlikView, познакомимся (или еще раз вспомним), как хранятся данные в приложении QlikView.
Все данные, в том числе и даты, в QlikView хранятся в виде двух представлений — числового и текстового (такой тип данных в QlikView называется dual). Данные можно отображать в различных форматах, но по умолчанию идет текстовое представление. Числовые значения дат нужны для эффективных расчетов, ведь когда все данные в измерении рассматриваются как последовательные числа, их можно легко сравнивать, сортировать, складывать и вычитать друг из друга. Для даты числовое представление определяется как количество дней, прошедших с 31 декабря 1899 года (например, дате 17 февраля 2015 года соответствует число 42052).
Создание календаря
Теперь перейдем непосредственно к созданию календаря.
Можно сказать, что календарь (измерение времени) в приложении — это непрерывный диапазон уникальных значений времени. Таким образом, при создании измерения времени необходимо указать начальную и конечную даты диапазона и сгенерировать непрерывную последовательность дат в заданных границах.
Начальная и конечная дата определяются либо, исходя из бизнес-задач аналитического приложения, либо на основании данных источника. Данные источника для создания значений измерения времени редко можно использовать без трансформации: в источнике часто могут отсутствовать данные за некоторый период, а в аналитическом приложении должна быть возможность работы с этим периодом. Таким образом, в скрипте QlikView необходимо выполнить формирование последовательности дат.
Для примера мы создадим в QlikView небольшое приложение для анализа продаж, включающее календарь. Для этого нужно создать и сохранить в QlikView новое приложение и открыть редактор скрипта (сочетание клавиш Ctrl+E).
Сначала в приложении напишем скрипт, создающий таблицу с данными продаж (таблицу фактов).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SET ThousandSep=' '; SET DecimalSep=','; SET MoneyThousandSep=' '; SET MoneyDecimalSep=','; SET MoneyFormat='# ##0,00р.;-# ##0,00р.'; SET TimeFormat='h:mm:ss'; SET DateFormat='DD.MM.YYYY'; SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]'; SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек'; SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс'; [Продажи]: LOAD Date(Дата) as Дата, [Заказ ID], [Товар ID], [Количество, шт.], [Цена за ед., руб.] ; LOAD * INLINE [ Дата, 'Заказ ID', 'Товар ID', 'Количество, шт.', 'Цена за ед., руб.' 03.02.2014, Z11, T1 4, 200 03.02.2014, Z11, T7, 2, 700 11.02.2014, Z12, T7, 3, 700 15.02.2014, Z14, T4, 5, 500 15.02.2014, Z14, T1, 3, 200 12.03.2014, Z15, T3, 2, 300 14.04.2014, Z16, T1, 2, 200 14.04.2014, Z16, T5, 3, 950 20.03.2014, Z17, T3, 2, 300 20.03.2014, Z17, T4, 2, 500 20.03.2014, Z17, T8, 1, 850 16.04.2014, Z20, T1, 2, 200 16.04.2014, Z20, T3, 1, 300 18.03.2014, Z21, T3, 2, 300 ]; |
НА ЗАМЕТКУ!
У первого выражения LOAD нет источника данных. В таких случаях QlikView использует результат следующего выражения LOAD как источник. Такое использование оператора LOAD называется Предшествующей Загрузкой (Preceding Load). В данном скрипте комбинация операторов LOAD позволяет загрузить в приложение даты в нужном формате.
Затем для удобства создаем отдельную вкладку «Календарь» (меню «Вкладка»-> «Добавить Вкладку…»). На эту вкладку добавим скрипт для создания календаря, который вначале создает временную таблицу TempMinMaxDates, состояющую из 1 строки с минимальной и максимальной датами из таблицы фактов. Далее эти даты записываются в переменные и используются с оператором AUTOGENERATE для создания таблицы TempCalendar с 1 столбцом, содержащим количество строк ( $(varMaxDate) — $(varMinDate) + 1) и включающим диапазон дат от MaxDate до MinDate. В завершении скрипта на основании диапазона дат из TempCalendar создается основной календарь, содержащий иерархии периодов.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
// Определение дат начала и окончания диапазона TempMinMaxDates: LOAD Min(Дата) as MinDate, Max(Дата) as MaxDate RESIDENT Продажи; LET varMinDate = Num(FieldValue('MinDate', 1)); LET varMaxDate = Num(FieldValue('MaxDate', 1)); Drop Table TempMinMaxDates; // Формирование последовательности дат в диапазоне TempCalendar: LOAD date ($(varMinDate) + rowno() - 1) as TempDate AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1; // Формирование календаря с иерархией периодов Календарь: LOAD [TempDate] as Дата, Day([TempDate]) as [День], Week([TempDate]) as [Неделя], WeekName([TempDate]) as [НеделяГод], WeekDay([TempDate]) as [День Недели], Month([TempDate]) as [Месяц], MonthName([TempDate]) as [МесяцГод], Dual('КВ'&Text(Div(Month([TempDate]),4)+1),Div(Month([TempDate]),4)+1) as [Квартал], QuarterName([TempDate]) as [КварталГод], Year([TempDate]) as [Год] Resident TempCalendar; DROP Table TempCalendar; |
Иерархия в календаре
Иерархия отражает связи между различными периодами времени и связь этих периодов с основной датой.
Иерархия периодов календаря формируется в соответствии со сложившимися отчетными периодами и с учетом периодичности поступления данных — год, квартал, месяц, неделя, день, час и т.д. Вот, какой календарь получился у меня:
Обратите внимание на поле «Месяц» — это хороший пример представления данных в dual. Числовое представление значений поля – это числа от 1 до 12, а текстовое представление – это названия месяцев с января по декабрь. У поля «МесяцГод» — числовое представление – это число, соответствующее дате начала месяца, а текстовое представление – это краткое название месяца + год. Эти периоды были сформированы с помощью встроенных функций QlikView для работы с датами.
Набор этих функций весьма обширен, но если вдруг не окажется нужной функции, всегда можно сформировать период с помощью функции
dual(текстовое_представление, числовое_представление)
связав даты начала некоторого периода с названиями этих периодов, как это делается для поля «Квартал»:
|
1 |
Dual('КВ'&Text(Div(Month([TempDate]),4)+1),Div(Month([TempDate]),4)+1) as [Квартал] |
При необходимости можно создать даже такие периоды времени, как «Полугодие», «Триместр» или «Декада».
Финансовый календарь
Используя функции QlikView можно также создать календарь, в котором отсчет времени ведется относительно так называемого «финансового» года, когда началом года является, например, 1 апреля. В этом случае достаточно поменять разбиение временного измерения на периоды. Скрипт для формирования календаря с финансовыми периодами может быть таким:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
// Определение дат начала и окончания диапазона TempMinMaxDates: LOAD Min(Дата) as MinDate, Max(Дата) as MaxDate RESIDENT Продажи; LET varMinDate = Num(FieldValue('MinDate', 1)); LET varMaxDate = Num(FieldValue('MaxDate', 1)); Drop Table TempMinMaxDates; // Формирование последовательности дат в диапазоне TempCalendar: LOAD date ($(varMinDate) + rowno() - 1) as TempDate AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1; // Формирование календаря с иерархией периодов Календарь: LOAD [TempDate] as Дата, Year(TempDate) as [Год], Month(TempDate) as [Месяц], Year(YearStart(TempDate, 0, 4)) As [Финансовый Год], Dual(Year(YearStart(TempDate, 0, 4)) & '-' & Num(Month(MonthStart(TempDate, -3)), '00'), MonthStart(TempDate)) As [Финансовый Период] Resident TempCalendar; |
Здесь дата начала финансового года сдвигается на 3 месяца вперед (с 1-го месяца (января) на 4-ый месяц (апрель) ), а номер финансового периода определяется как номер обычного календарного месяца минус 3. Вот, что получится:
Альтернативный календарь
Выше мы рассмотрели вариант скрипта для создания непрерывного календаря. Если же в календаре требуется использовать только те даты, что есть в источнике, можно использовать другой вариант.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Календарь: LOAD Дата, Week(Дата) as [Неделя], WeekName(Дата) as [НеделяГод], WeekDay(Дата) as [День Недели], Month(Дата) as [Месяц], MonthName(Дата) As [МесяцГод], Day(Дата) as [День], Dual('КВ'&Text(Div(Month(Дата),4)+1),Div(Month(Дата),4)+1) As [Квартал], QuarterName(Дата) as [КварталГод], Year(Дата) as [Год]; LOAD FieldValue('Дата',IterNo()) as Дата AUTOGENERATE(1) WHILE NOT IsNull(FieldValue('Дата',IterNo())); |
В данном скрипте календарь создается за один шаг с использованием функции FieldValue в цикле While. Функция FieldValue считывает по одному значению из поля «Дата», пока не будет считано последнее значение. Таким образом, поле «Дата» служит источником данных для оператора LOAD. Данный скрипт позволяет создать календарь, выполнив меньшее количество операций, и сократить время выполнения скрипта (что бывает важно в случае большого количества данных).
Спасибо за внимание. Ваши комментарии и предложения по работе с календарем жду здесь! 😀
СКАЧАТЬ: Приложение QlikView Calendar
Василий Петренко
B3
Максим
Stepan
Irina
Доброго времени суток,
почему при расчёте квартала используется деление на 4?
Потому что квартал это одна четвертая года)))