5-1. Главный календарь (Master Calendar)

Зачем нужен главный календарь

Главный календарь (Мастер календарь, Master Calendar) в Qlik — специальная таблица-справочник, которая содержит измерения, относящиеся к анализу периодов времени.

Недели, месяцы, кварталы, годы и т.д. — все это обычно находится в главном календаре. Также, там часто располагаются поля-флаги или счетчики, для быстрых отборов определенных периодов, вроде прошлого года, текущего года и т.д.

В зависимости от задач и модели, в приложении может быть несколько календарей, для различных полей дат.

Помимо удобного хранения временных разрезов, главный календарь позволяет решать следующие задачи:

  • Учитывать в аналитике даты, пропущенные в данных. Например, продажа была 01.01.2021, следующая продажа была 05.01.2021. Числа со 2 по 4 пропущены в данных, но мы можем восстановить их в календаре, чтобы потом считать полное кол-во дней периода.
  • Делать визуализации с вычислением накопительного итогов разной глубины (по неделям, месяцам, года, за все время). У движка Qlik есть особенность — накопительный итог по датам можно расчитать только при определенной структуре календаря.

Однако, все по порядку. Для начала, сделаем календарь с базовым функционалом.

Базовый календарь

Скачайте файл с данными для практики. Добавьте загрузку данных из файла в скрипт.

И так, мы готовы делать главный календарь. Он создается в несколько шагов. Шаг первый — получить минимальную и максимальную дату из поля, для которого мы делаем календарь.

Есть разные способы решения этой задачи, например загрузить таблицу с полями min(Дата) и max(Дата) из таблицы платежи. Но мы применим более хитрый и производительный способ:

MinMaxDate:
load
date(min(FieldValue('Дата',recno()))) as MinDate,
date(max(FieldValue('Дата',recno()))) as MaxDate
AutoGenerate FieldValueCount('Дата');

Результат будет такой же, как и в первом варианте, но вычисляться он будет быстрее. В чем разница: первом варианте Qlik должен искать минимум и максимум поля во всех записях таблицы. А их может быть много миллионов, при том что самих дат гораздо меньше — 365 на каждый год.

Второй вариант работает так: функция FieldValue() может получить значение любого поля модели данных, через 2 аргумента — название поля и номер значения. Номер значения берется из индексной таблицы клика, которая хранит только уникальные значения полей. Т.к. мы не можем сослаться на конкретный источник для получения индексных значений поля Дата, мы будем использовать способ загрузки Autogenerate, который выполнит загрузку полей указанное кол-во раз. Кол-во раз для загрузки определяем через функцию FieldValueCount(), которая возвращает кол-во уникальных значений поля.

Т.к. в функции FieldValue() вторым аргументом указан recno(), т.е. номер строки, то на каждую итерацию autogenerate мы будем получать очередное уникальное значение поля Дата.

Такое способ получения минимальной и максимальной даты работает ощутимо быстрее уже на объемах записей в сотни тысяч. Поэтому пользоваться нужно именно им.

Результат:

Генерация полного перечня дат

Теперь нам нужно получить полный список дат между минимальной и максимальной. Самый простой способ это сделать — воспользоваться внутристроковым циклом IterNo().

Для этого достаточно добавить preceding load перед получением минимальной и максимальной даты.

MinMaxDate:
load	
date(MinDate+iterno()-1) as [Дата]
while date(MinDate+iterno()-1)<=MaxDate;

load
date(min(FieldValue('Дата',recno()))) as MinDate,
date(max(FieldValue('Дата',recno()))) as MaxDate
AutoGenerate FieldValueCount('Дата');

Напомню: preceding load — это функционал, при котором в случае отсутствия указания у load источника данных (resident, from autogenerate и т.д.) он образует вложенный запрос к источнику с load’ом, который следует за ним.

Как работает IterNo(). По сути это счетчик, который возвращает инкрементально увеличивающееся число для каждой итерации — 1, 2, 3, 4 и т.д. И так происходит для каждой строки таблицы.

Как контролируется кол-во итераций? Это происходит с помощью условия while. While в целом работает как Where, только позволяет дополнительно устанавливать условия по кол-ву откруток цикла внутри строки. Условие

date(MinDate+iterno()-1)<=MaxDate

говорит, что цикл должен продолжаться до тех пор, пока выражение генерируемой даты меньше или равно максимальной дате. Строка в нажей таблице всего одна, так что на выходе будет просто полный список дат.

При загрузке вы увидите, что в таблице с полным списком дат записей больше чем в исходной таблице. Потому что пропущенные даты были восстановлены.

Что важно знать для генерации списка дат

  • Данные в поле, для которого генерируется календарь, должны содержать только дату, а не timestamp (даты с меткой времени). Это легко проверить по наличию тега $integer (целое число) в предпросмотре поля в модели данных. Если его там нет, то нужно при загрузке табилцы с данными округлить даты вниз до целого, через функцию floor(). Если этого не сделать, календарь будет работать не корректно из-за несовпадающих значений в нем (целые) и в таблице с данными (числа с дробной частью, чем по факту являются значения timestamp).
  • Вы можете использовать для календаря собственные диапазоны дат. Например, взяв в качестве максимальной даты не максимум из данных, а задав свое число.
  • Чтобы поле работало с календарем, данные в нем должны быть в формате даты (неожиданно!). Имеется в виду, что если вы грузите например планы, которые разбиты по месяцам, а не по дням, чтобы привязать их к календарю, нужно привести наименования месяцев к датам. Например, Январь 2021 к 01.01.2021. Это необходимо делать только в случае если Январь 2021 определется как текст. Если же у поля с этими значениями есть тег $date, значит все впорядке.

Создаем измерения календаря

Теперь нам остается создать дополнительные временыне разрезы. Это делается с помощью функции дат, с которыми вы можете ознакомиться в справке.

Мы же задействуем основные из них. Пристроим еще один этаж preceding load, и переименуем табилцу в Календарь.

Календарь:
load
Дата,
//с привязкой к году
date(monthstart(Дата),'MMMM YY') as МесяцГод,
date(weekstart(Дата)) as НеделяГод,
QuarterName(Дата) as КварталГод,
date(YearStart(Дата),'YYYY') as Год,

//без привязки к году
month(Дата) as Месяц,
Week(Дата) as Неделя,
'Q' & ceil(MONTH(Дата)/3) as Квартал,

//флаги
InYearToDate([Дата],today(),0) as InYTD,
year(Дата)-year(today()) as Лет_Назад,
(year([Дата])*12+month(Дата))-(year(today())*12+month(today())) as Месяцев_назад
;


load	
date(MinDate+iterno()-1) as [Дата]
while date(MinDate+iterno()-1)<=MaxDate;

load
date(min(FieldValue('Дата',recno()))) as MinDate,
date(max(FieldValue('Дата',recno()))) as MaxDate
AutoGenerate FieldValueCount('Дата');

Обычно измерения календаря делятся на 3 типа:

Измерения с привязкой к году. Они обычно делаются через преобразование даты в первую дату нужного периода. Так, monthstart() превратит 13.02.2021 в 01.02.2021. Дополнительно, такие поля могут обрабатываться функцией date для задания отображаемого формата данных. date(monthstart(Дата),’MMMM YY’) выдаст значения вроде Январь 2021. Наименования подставляемых названий месяцев задаются в системных переменных в начале приложения. В названиях таких измерений обычно пишется слово «Год». НеделяГод. МесяцГод и т.д.

Измерения без привязки к году. Номера недель, месяцев, кварталов. Определение номеров недель может потребовать доп. настроек переменных. Т.к. недели не совпадают полностью с началом и концом месяцев, определение первого и последнего номеров недели в году может варьироваться в зависимости от принятых стандартов в вашей компании. В Qlik за эту логику отвечают 2 системных переменных: BrokenWeeks и ReferenceDay

Если значение BrokenWeeks установлено как 0, то Qlik отсчитывает номер недели строго с понедельника. Это значит, что последняя неделя года может вылезти на январь. А первая неделя года начаться не с 1 января.

Если значение BrokenWeeks установлено как 1, то Qlik смотрит в переменную ReferenceDay, число в которой определяет, сколько дней нового года должно присутствовать в неделе, чтобы Qlik считал ее первой неделей года. На примере скриншота, первой неделй года будет считаться та, в которой присутствует минимум 5 январских дней.

Параметры и флаги. Здесь идут всякие доп. свойства, для облегчения написания выражений анализа множеств, выборок и т.д. Мы посчитали параметры Лет назад и Месяцев назад, которые содержат относительные числа лет и месяцев соответствено, относительно сегодняшней даты. Так, для визуализации показателей прошлого месяца можно написать формулу типа такой:

sum({<Месяцев_назад={-1}>} Платежи)

Результат:

Теперь вы можете создать визуализации с этими измерениями.

Обязательно контролируйте, чтобы отсуствтующие данные отображались как null(), а не 0 или пустые ячейки. Иначе рискуете получить календарь от 1 января 1900 года.

2 комментария

  1. Евгений, спасибо за материал. Без понимания как делать календарь, не построить нормальную аналитику.

    Опечатки: расчитать , нажей ,табилцы , определется , впорядке , временыне , табилцу , неделй , соответствено , отсуствтующие ,

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *