LFL Календарь. Накопительный календарь Qlik. The As-Of Table. YTD, MTD, WTD, QTD / LYTQ, LQTD, LMTD, LWTD

Опубликовано qliksense-expert в

Подписывайся на мой канал "Data Engineering" в телеграме

В этой статье речь пойдет про накопительный календарь и календарь с флагами для расчета LFL показателей, например, продажи за последние 90 дней текущего месяца к продажам за аналогичный период предыдущего года (т.е. 90 дней прошлого года).

Для начала рассмотрим упрощенный пример LFL Календаря.

LFL Календарь Qlik Sense (для расчета показателей год к году)

//===========================================================================
// Автор & Дата: Шамаев Иван 2019.06.01
// Описание: Создание календаря на основе максимальной и минимальной дате
// **************************************************************************
// Изменение:
// <номер изменения, дата, автор> - <описание>
//===========================================================================

// Получаем из поля Дата минимальное и максимальное значения
[TEMP Максимальная Минимальная Дата]:
LOAD
     Min(FieldValue('Дата',RecNo())) 	As [Минимальная Дата],
     Max(FieldValue('Дата',RecNo())) 	As [Максимальная Дата]
AutoGenerate FieldValueCount('Дата');

// Минимальную и максимальную даты помещаем в переменные, 
// для того, чтобы использовать их в условии While
Let пНачалоКалендаря_Число 	  = Num(Peek('Минимальная Дата', 0, 'TEMP Максимальная Минимальная Дата'));
Let пОкончаниеКалендаря_Число = Num(Peek('Максимальная Дата', 0, 'TEMP Максимальная Минимальная Дата'));
Let пСегодняДата_Число        = Num(Today());

// Удаляем временную таблицу
DROP Table [TEMP Максимальная Минимальная Дата];  

// Создаем набор дат в промежутке от минимальной до максимальной даты
[TEMP Набор дат в интервале MinDate - MaxDate]:  
LOAD 
     Date($(пНачалоКалендаря_Число) + IterNo() - 1) as [TEMP Дата]
AutoGenerate 1
While $(пНачалоКалендаря_Число) + IterNo() -1 <= $(пОкончаниеКалендаря_Число);  

// Формируем таблицу с календарем LFL в моделе Qlik Sense
[Календарь]:
Load *,
     if(Дата<=Date($(пСегодняДата_Число)) 
     	and Дата>=AddMonths(Date($(пСегодняДата_Число)),-6),1)					As [Текущий год 180 Дней],
     if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
		and Дата>=AddMonths(AddYears(Date($(пСегодняДата_Число)),-1),-6),1) 	As [Предыдущий год 180 Дней],
     if(Дата<=Date($(пСегодняДата_Число))
		and Дата>=AddMonths(Date($(пСегодняДата_Число)),-3),1) 					As [Текущий год 90 Дней],
     if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
		and Дата>=AddMonths(AddYears(Date($(пСегодняДата_Число)),-1),-3),1) 	As [Предыдущий год 90 Дней],
     if(Дата<=Date($(пСегодняДата_Число)) 
     	and Дата>=AddMonths(Date($(пСегодняДата_Число)),-1),1)					As [Текущий год 30 Дней],
     if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
     	and Дата>=AddMonths(AddYears(Date($(пСегодняДата_Число)),-1),-1),1)		As [Предыдущий год 30 Дней],
     if(Дата<=Date($(пСегодняДата_Число))
		and Дата>=Date($(пСегодняДата_Число)-7),1)								As [Текущий год 7 Дней],
     if(Дата<=Date($(пСегодняДата_Число)-7)
     	and Дата>=Date($(пСегодняДата_Число)-14),1)								As [Предыдущие 7 Дней],
     if(Дата<=Date($(пСегодняДата_Число))
     	and Дата>=MakeDate(Year(Date($(пСегодняДата_Число)))),1)				As [Текущий год],
     if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
     	and Дата>=MakeDate(Year(AddYears(Date($(пСегодняДата_Число)),-1))),1)	As [Прошлый год]
     ;
Load 
     Date([TEMP Дата]) 													As Дата,
     Date([TEMP Дата],'DD MMMM YYYY')									As [Дата длинный месяц],
     Text(Date([TEMP Дата],'WWWW MMM YYYY'))							As [День недели МесяцГод],
     'Кв'& Ceil(Num(Month([TEMP Дата]))/3)								As Квартал,
     'Кв'& Ceil(Num(Month([TEMP Дата]))/3) & '-' & Year([TEMP Дата]) 	As КварталГод,
     Text(Date([TEMP Дата],'YYYY.MM')) 									As [Год Месяц],
     Text(Date([TEMP Дата],'MMM YYYY')) 								As [Месяц Год],
     Year([TEMP Дата]) 													As Год,
     Year([TEMP Дата])&'.'&Week([TEMP Дата])							As [Год Неделя],
     Week([TEMP Дата])													As Неделя,
     Day([TEMP Дата])													As День
Resident [TEMP Набор дат в интервале MinDate - MaxDate];

Drop Table [TEMP Набор дат в интервале MinDate - MaxDate];

Принскрин скрипта LFL календаря Qlik Sense (Календарь с флагами на даты):

Пример LFL календаря Qlik Sense с флагами: Текущий год 180 дней, Прошлый год 180 дней, Последние 7 дней, 7 дней на прошлой неделе и т.п.

Теперь для того, чтобы отобразить продажи за период можно использовать в Set Analysis предварительно рассчитанные флаги. Следующие примеры будут использовать модификатор 1 для того, чтобы исключить установленные в модели фильтры (т.е. на полном множестве данных):

Подписывайся на мой канал "Data Engineering" в телеграме
 
//Продажи за 180 дней в текущем году:
sum({1<[Текущий год 180 Дней]={1}>} [Продажи руб])

//Продажи за 180 дней в прошлом году (аналогичный период годом ранее):
sum({1<[Предыдущий год 180 Дней]={1}>} [Продажи руб])

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

//Продажи за 180 дней в текущем году:
sum({<[Текущий год 180 Дней]={1},[Дата длинный месяц]=,Дата=,[День недели МесяцГод]=,Квартал=,КварталГод=,[Год Месяц]=,[Месяц Год]=,Год=,[Год Неделя]=,Неделя=,День=>} [Продажи руб])

//Продажи за 180 дней в прошлом году (аналогичный период годом ранее):
sum({<[Предыдущий год 180 Дней]={1},[Дата длинный месяц]=,Дата=,[День недели МесяцГод]=,Квартал=,КварталГод=,[Год Месяц]=,[Месяц Год]=,Год=,[Год Неделя]=,Неделя=,День=>} [Продажи руб])

Еще один вариант календаря Qlik Sense (поправил первый вариант)

//===========================================================================
// Автор & Дата: Шамаев Иван 2020.05.01
// Описание: Создание календаря на основе максимальной и минимальной дате
// **************************************************************************
// Изменение:
// <номер изменения, дата, автор> - <описание>
//===========================================================================

// Получаем из поля Дата минимальное и максимальное значения
[TEMP Максимальная Минимальная Дата]:
LOAD
     Min(FieldValue('Дата',RecNo())) 	As [Минимальная Дата],
     Max(FieldValue('Дата',RecNo())) 	As [Максимальная Дата]
AutoGenerate FieldValueCount('Дата');

// Минимальную и максимальную даты помещаем в переменные, 
// для того, чтобы использовать их в условии While
Let пНачалоКалендаря_Число 	  = Num(Peek('Минимальная Дата', 0, 'TEMP Максимальная Минимальная Дата'));
Let пОкончаниеКалендаря_Число = Num(Peek('Максимальная Дата', 0, 'TEMP Максимальная Минимальная Дата'));
Let пСегодняДата_Число        = Num(Today());

// Удаляем временную таблицу
DROP Table [TEMP Максимальная Минимальная Дата];  

// Создаем набор дат в промежутке от минимальной до максимальной даты
[TEMP Набор дат в интервале MinDate - MaxDate]:  
LOAD 
     Date($(пНачалоКалендаря_Число) + IterNo() - 1) as [TEMP Дата]
AutoGenerate 1
While $(пНачалоКалендаря_Число) + IterNo() -1 <= $(пОкончаниеКалендаря_Число);  

// Формируем таблицу с календарем LFL в моделе Qlik Sense
[Календарь]:
Load *,
     //Генерация флагов для более простого сравнительного анализа по периодам
        if(Дата<=Date($(пСегодняДата_Число)) 
            and Дата>Date($(пСегодняДата_Число)-360),1)                              As [Флаг Текущий год 360 Дней],
        if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
            and Дата>Date(AddYears(Date($(пСегодняДата_Число)),-1)-360),1)           As [Флаг Предыдущий год 360 Дней],
        if(Дата<=Date($(пСегодняДата_Число)) 
            and Дата>Date($(пСегодняДата_Число)-180),1)                              As [Флаг Текущий год 180 Дней],
        if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
            and Дата>Date(AddYears(Date($(пСегодняДата_Число)),-1)-180),1)           As [Флаг Предыдущий год 180 Дней],
        if(Дата<=Date($(пСегодняДата_Число))
            and Дата>Date($(пСегодняДата_Число)-90),1)                               As [Флаг Текущий год 90 Дней],
        if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
            and Дата>Date(AddYears(Date($(пСегодняДата_Число)),-1)-90),1)            As [Флаг Предыдущий год 90 Дней],
        if(Дата<=Date($(пСегодняДата_Число)) 
            and Дата>Date($(пСегодняДата_Число)-30),1)                               As [Флаг Текущий год 30 Дней],
        if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
            and Дата>Date(AddYears(Date($(пСегодняДата_Число)),-1)-30),1)            As [Флаг Предыдущий год 30 Дней],
        if(Дата<=Date($(пСегодняДата_Число))
            and Дата>Date($(пСегодняДата_Число)-7),1)                                As [Флаг Текущие 7 дней],
        if(Дата<=Date($(пСегодняДата_Число)-7)
            and Дата>Date($(пСегодняДата_Число)-14),1)                               As [Флаг Предыдущие 7 Дней],
        if(Дата<=Date($(пСегодняДата_Число))
            and Дата>=MakeDate(Year(Date($(пСегодняДата_Число)))),1)                 As [Флаг Текущий год],
        if(Дата<=AddYears(Date($(пСегодняДата_Число)),-1)
            and Дата>=MakeDate(Year(AddYears(Date($(пСегодняДата_Число)),-1))),1)    As [Флаг Прошлый год],
        if(Дата>=MonthStart($(пСегодняДата_Число))
            and Дата<=Date($(пСегодняДата_Число)),1)                                 As [Флаг Текущий MTD],
        if(Дата>=AddMonths(MonthStart($(пСегодняДата_Число)),-1)
            and Дата<=AddMonths(Date($(пСегодняДата_Число)),-1),1)                   As [Флаг Предыдущий MTD]
     ;
Load 
     Date([TEMP Дата]) 								      As Дата,
     Date([TEMP Дата],'DD MMMM YYYY')						      As [Дата длинный месяц],
     Text(Date([TEMP Дата],'WWWW MMM YYYY'))					      As [День недели МесяцГод],
     'Кв'& Ceil(Num(Month([TEMP Дата]))/3)					      As Квартал,
     'Кв'& Ceil(Num(Month([TEMP Дата]))/3) & '-' & Year([TEMP Дата]) 	              As КварталГод,
     Text(Date([TEMP Дата],'YYYY.MM')) 						      As [Год Месяц],
     Text(Date([TEMP Дата],'MMM YYYY')) 					      As [Месяц Год],
     Year([TEMP Дата]) 								      As Год,
     Year([TEMP Дата])&'.'&Week([TEMP Дата])					      As [Год Неделя],
     Week([TEMP Дата])								      As Неделя,
     Day([TEMP Дата])								      As День
Resident [TEMP Набор дат в интервале MinDate - MaxDate];

Drop Table [TEMP Набор дат в интервале MinDate - MaxDate];

Пример формулы с использованием флагов:

sum({<[Флаг Текущие 7 дней]={1}>} [Продажи, руб])
sum({<[Флаг Предыдущие 7 Дней]={1}>} [Продажи, руб])
4.5 4 голосов
Рейтинг статьи
Подписывайся на мой канал "Data Engineering" в телеграме
Рубрики: Уровень 1
Метки:
Подписаться
Уведомление о
guest
0 комментариев
Oldest
Newest Most Voted
Встроенная Обратная Связь
Просмотр всех комментариев

Похожие записи

Уровень 1

Урок 1. Регистрация в Qlik Sense Business Cloud. Как начать работать с Qlik в облачном сервисе?

Подписывайся на мой канал "Data Engineering" в телеграме Подписаться Урок 1. Регистрация в Qlik Sense Business Cloud. Как начать работать с Qlik в облачном сервисе? В этом уроке будет описан процесс создания аккаунта в облачном Читать дальше…

Уровень 1

Связать данные по интервалам. Пример IntervalMatch Qlik Sense

Подписывайся на мой канал "Data Engineering" в телеграме Подписаться В этой статье будет рассмотрены примеры использования функции IntervalMatch. Рассмотрим пример [tester_signal]: LOAD Signal_datetime, Signal_symbol, "symbol", indicator, "1d", "6h", "1h" FROM [lib://DataFiles/tester_signal.qvd] (qvd); NoConcatenate [TEMP tester_price_changes]: Читать дальше…

Уровень 1

Дизайн дашборда Qlik Sense. Проектирование, разработка, MAD (DAR) концепции

Подписывайся на мой канал "Data Engineering" в телеграме Подписаться В этой статье будут рассмотрены основные подходы, методы дизайна дашборда Qlik Sense. Из каких элементов состоит дашборд Qlik Sense, концепция DAR (Dashboard – Analysis – Report). Читать дальше…

0
Оставьте, пожалуйста, комментарий!x