5-3. Календарь накопительного итога. As-of-calendar

Накопительный итог в Qlik Sense — это целая песня.

В аналитике периодически бывает необходимо считать меры накопительным итогом. Это бывает нужно для расчета показателей, связанных с остатками (сумма движений за все время накопительным итогом). Или например отслеживание выполнения планов накопительно внутри года. Или создавать более хитрые сценарии визуализации. Например, считать внутри месяца среднее значение показателя за 3 прошлых месяца, визуализируя все это на графике. Или показывать вместе с линией продаж текущего года линию продаж прошлого года. При том, что в качестве измерения используется МесяцГод, а не Месяцы, разбитые по годам.

Частично эта задача может быть решена с помощью модификаторов мер. С их помощью можно настроить некоторые сценарии накопительного итога.

Но вот незадача. Накопление всегда начинается от начала текущей выборки. Т.е. пока вы не применили фильтр по периоду, все выглядит ок. Но как только вы выбрали, например, текущий год — хвост данных из прошлого года исчезает. Вы видите накопление от начала текущего года.

Иногда это то что вам нужно, а иногда (почти всегда) — нет. Чтобы данные накопительного итога не отрезались фильтрами, нужно применить специальную технику моделирования — календарь накопительного итога, или as-of-calendar. Этим мы и займемся.

Концепция As-Of-Date

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

Начнем же. Можно использовать скрипт приложений из прошлых занятий. Начнем с того, что уберем из календаря генерацию измерений, оставив только генерацию полного списка дат. При этом название генерируемого поля нужно поменять с Дата на День. Это важно. Именно это поле будет исопльзоваться как базовое поле календаря.

Добавляем под генерацию календаря следующую команду:

left join load День as Дата Resident Календарь where Exists(Дата,День);

Мы делаем присоединение к существующему списку дат самого себя, но с другим именем поля. При джойне с отсутствующими общими полями, мы получим декартово произведение: пересечение всех возможных значений одного поля со всеми возможными значениями другого поля. Результатом станет таблица на 500 с лишним тысяч строк. Типичный результат джойна без ключевых полей. Но в этот раз это именно то что нам нужно.

Обратите внимание на условие where Exists(Дата,День). Оно нужно, чтобы немного сократить кол-во значений таблицы пересечений. Т.к. джойниться будут не все даты диапазона от минимлаьного и максимального, а только те, которые присутствуют в данных. Это также спасет вас от бесконечного выполнения скрипта, если в данных попадутся ошибочные даты типа 01.01.2201.

Теперь эту таблицу мы будем использовать для нарезки слоев календаря накопительного итога. Вот код:

Календарь_НИ:
load
День,
Дата,
'Без накопления' as Накопительный_итог
Resident Календарь where Дата=День;

load
День,
Дата,
'По неделям' as Накопительный_итог
Resident Календарь where Дата<=День and Week(Дата)=Week(День) and year(Дата)=year(День);

load
День,
Дата,
'По месяцам' as Накопительный_итог
Resident Календарь where Дата<=День and monthstart(Дата)=monthstart(День);

load
День,
Дата,
'По кварталам' as Накопительный_итог
Resident Календарь where Дата<=День and quarterstart(Дата)=quarterstart(День);

load
День,
Дата,
'По годам' as Накопительный_итог
Resident Календарь where Дата<=День and year(Дата)=year(День);

load
День,
Дата,
'Полный' as Накопительный_итог
Resident Календарь where Дата<=День;

drop table Календарь;

Смысл в том, что мы загружаем несколько слоев, ограничивая глубину дат относительно дней. Для сценария без накопительного итога мы грузим только те записи, где Дата=День. Для накопительного итога внутри недель, месяцев, кваратлов, лет, грузим записи с условием Дата<=День, и то что Дата и День попадают на общий период. Для полного накопления грузим просто все записи, где Дата<=День.

Обязательно создает поле Накопительный_итог, которым мы будем управлять глубиной накопительного итога в визуальном слое.

Теперь можно создавать меры с формулами типа Sum({<[Накопительный_итог]={‘По месяцам’}>}[Платежи]), и таким образом контролировать накопительные итоги.

Попробуйте собрать 6 формул накопительного итога. Обратите внимание, что в качестве измерения нужно использовать поле День, а не Дата. Т.к. именно поле День содержит в себе ссылки на прошлые даты

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

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

left join (Календарь_НИ) load distinct

День,
//с привязкой к году
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 Месяцев_назад
Resident Календарь_НИ;

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

Вычисления со сдвигом дат

Накопительным итогом мы считать научились, теперь попробуем выводить. Добавим в фрагмент загрузки полного накопительного итога следующие записи:

День-Дата as AsOfDate,
(year(День)*12+month(День))-(year(Дата)*12+month(Дата)) as AsOfMonth,
(year(День)*4+ceil(month(День)/3))-(year(Дата)*4+ceil(month(Дата)/3)) as AsOfQuarter,
year(День)-year(Дата) as AsOfYear,

Также, перед этим load’ом допишем команду concatenate, чтобы эти строки добавились к предыдущей таблице. Потому что автоматически склеиваются только табилцы с полностью одинаковым набором полей. В остальных случаях нужно указывать команду concatenate.

Теперь у нас появились параметры, отображающие сдвиг дат относительно главной оси времени. AsOfDate, AsOfMonth, AsOfQuarter, AsOfYear отвечают за сдвиг дней, месяцев, кварталов, лет соответственно. Что нам это дает?

Создайте линейный график с измерением МесяцГод, и мерой Sum({<[Накопительный_итог]={‘Без накопления’}>}[Платежи]).

Второй мерой добавьте Sum({<[Накопительный_итог]={‘Полный’},AsOfMonth={12}>}[Платежи]).

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

Не забывайте раздел Представление > Стиль, чтобы повысить премиальность нажего графика за счет более изящного дизайна.

Например, вспомогательные линии можно отображать штриховкой.

В зависимости от измерения на графике (День, Месяц, Квартал, Год), вам нужно будет использовать AsOfDate, AsOfMonth, AsOfQuarter, AsOfYear соответственно.

Как вы поняли, мы специально делаем вторую меру с полным накоплением, т.к. на каждый ее День содержатся данные каждой Даты. Но за счет дополнительного параметра оставляем только те даты, которые соответствуют нужному сдвигу периода.

Вот еще фишка. Посчитаем во второй мере скользящее среднее за 3 месяца в каждой точке. Для начала, создадим график с мерой:

Sum({<[Накопительный_итог]={'Полный'},AsOfMonth={"<=2"}>}[Платежи])

и измерением МесяцГод. Как видите, мы используем условие AsOfMonth={«<=2»}, что будет возвращать нам сумму операций за текущий (0) и 2 предыдыщих месяца.

Теперь ее надо усреднить по кол-ву месяцев. Для этого нам понадобится формула, считающая кол-во месяцев в интервале. Подойдет такой вариант:

Count(distinct {<[Накопительный_итог]={'Полный'},AsOfMonth={"<=2"}>} [Дата.AxisViz.Месяц])

Интересный факт: для наших целей необходимо, чтобы данное поле присутствоавало в модели, либо было derived-полем, как в моем случае. Почему? Потому что по какой-то причине деление на формулу, считающее значение monthstart(Дата), т.е. приводящее дату к месяцу прямо внутри формулы, выдавало неадекватные разультаты.

Поэтому я добавил поле Дата в derived-поля, чтобы получить значение месяца, и с ним все работало ок.

Результат:

Выводы

Календарь накопительного итога — мощный инструмент для увеличения вариативности сценариев аналитики. Но за все приходится платить.

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

Во-вторых, нужно внимательно следить за качеством дат, не допускать появления ошибочных слишком больших или маленьких дат — это просадит произвоительность приложения или вообще не даст выполниться скрипту.

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

В-четвертых, не стоит генерировать все возможные сценарии накопительного итога, если в этом нет необходимости. Нужен только стандартный сценарий и накопление по годам? Просто не загружайте другие фрагменты календаря.

В-пятых подумайте, действительно ли вам нужен календарь накопительного итога, или можно обойтись модификаторами мер на графиках? Действительно ли вам необходим учет данных за пределами выборки на временной оси?

Скачать приложение курса.