Содержание урока по Qlik Sense
Пару слов о Timestamp – Это число с дробной частью, которое соответствует какой-то дате со временем. Т.е. это число можно представить в нужном формате Дата-Время.
Дата – это целая часть числа с дробной частью (про даты речь пойдет дальше). Время – это дробная часть числа (все, что после разделителя).
1 секунда ~ 0,0000115740740740740
23 часа 59 минут 59 секунд = 0,99998842592593
Для того, чтобы научиться работать с датами в Qlik Sense, необходимо понять исходный формат хранения дат.
Дата в Qlik – это целое число, которое может быть загружено в модель в формате даты, например, 20/03/2019 или 20.03.2019, или 20-03-2019, или 20+03+2019 (все зависит от Ваших потребностей и фантазии). Если данные с датами загружаются из файла, где формат даты уже задан, то этот формат будет загружен в модель. Если данные загружаются не из QVD файлов, а из сторонних файлов/систем/баз данных, то рекомендуется делать преобразование:
Формат даты источника данных => Число => Формат даты Qlik Sense.
Если избегать преобразования даты в число, а затем в дату, то можно наткнуться на следующие проблемы:
Что такое исходный формат хранения даты? Дата в Qlik Sense – это целое число. Проверить это можно с помощью формул в Qlik Sense приложении (например вбить в текстовом объекте листа формулу =Date(3000)).
Исходный формат даты в Qlik – целое число:
Date(-693959) = 01.01.0000 Date(-100000) = 16.03.1626 Date(-10000) = 13.08.1872 Date(-5000) = 22.04.1886 Date(-1000) = 04.04.1897 Date(-2) = 28.12.1899 Date(-1) = 29.12.1899 Date(0) = 30.12.1899 Date(1) = 31.12.1899 Date(2) = 01.01.1900 Date(43500) = 04.02.2019
Формат даты по умолчанию в рамках 1 приложения настраивается с помощью переменной на вкладке MAIN в скрипте:
SET DateFormat='DD.MM.YYYY';
К этой настройке относитесь очень внимательно, т.к. функции дат, например Date(), без обозначения формата даты используют формат даты по-умолчанию.
Рассмотрим примеры записи форматов даты:
Date(43500,'DD.MM.YYYY') = 04.02.2019 Date(43500,'DD.MM.YY') = 04.02.19 Date(43500,'DDMMYYYY') = 04022019 Date(43500,'DD/MM/YYYY') = 04/02/2019 Date(43500,'DD+MM+YYYY') = 04+02+2019 Date(43500,'DD|MM|YYYY') = 04|02|2019 Date(43500,'DD MM YYYY') = 04 02 2019 Date(43500,'DD.MMM.YYYY') = 04.февр..2019 Date(43500,'DD MMM YYYY') = 04 февр. 2019 Date(43500,'DD/MMM/YYYY') = 04/февр./2019 Date(43500,'D/MMM/YYYY') = 4/февр./2019 Date(43500,'D MMMM YYYY') = 4 февраль 2019 Date(43500,'D MMMM YY') = 4 февраль 19 Date(43500,'YYYY.MM.DD') = 2019.02.04 Date(43500,'YYYYMMDD') = 20190204 Date(43500,'YYYY-MM') = 2019-02 Date(43500,'YYYY-DD-MM') = 2019-04-02
В рамках краткого обзора функций для преобразования дат, я рассмотрю наиболее часто используемые на практике функции. На самом деле, таких функции очень много. Полный список можно посмотреть в официальном Qlik Sense HELP.
Понимание основ работы с данными и знание нескольких функций закроет на первое время все потребности при разработке скриптов загрузки данных.
Функции нужны для преобразования дат, форматов, вычленения из даты года, месяца, дня, номера недели. К дате можно прибавить заданное количество дней, месяцев или лет. Для начала я кратко опишу каждую функцию и приведу примеры использования этих функций. Затем я рассмотрю несколько кейсов работы с датами в нестандартных случаях.
Мой топ функций для работы с датами:
Date#() используется для интерпретации данных. Например
Date#('2018_24_10','YYYY_DD_MM') говорит о том, что первые четыре цифры – это год, цифры между двумя ‘_ – это дата, а последние две – месяц. Причем, функция Date# не меняет формат даты, т.е. дата просто будет переведена из текста в число по правилу ‘YYYY_DD_MM’
Date() используется, чтобы сказать, как показать данные. Например:
Date(Today(),'YYYY^DD^MM') возвращает 2019^08^05 Date(Today(),'MM/DD/YY') возвращает 05/08/19
Сочетание обеих функций:
Date(Date#('2018_24_10','YYYY_DD_MM'),'MM/DD/YYYY') возвращает 10/24/2018 Для того, чтобы получить длинное название месяца, необходимо применить следующий код (длинное название месяца берется из системной переменной LongMonthNames):
Text(Date(MakeDate(2018,9,21),'MMMM YYYY'))
Это выражение вернет ‘сентябрь 2018’. Обязательно оборачивайте в Text, иначе будет ошибка. Объясняю почему:
Date(MakeDate(2018,9,21),’MMMM YYYY’) тоже вернет ‘сентябрь 2018′, но храниться будет как дата 21.09.2018. И если Вы сравните две даты ’22 сентября 2018′ и ’21 сентября 2018’, то в формате ‘MMMM YYYY’ они будут не равны.
if(
Date(MakeDate(2018,9,21),'MMMM YYYY')=Date(MakeDate(2018,9,22),'MMMM YYYY'),
1,
0
) Результат: 0, т.е. ложь.
if(
Text(Date(MakeDate(2018,9,21),'MMMM YYYY'))=Text(Date(MakeDate(2018,9,22),'MMMM YYYY')),
1,
0
) Результат: 1
Пример 1:
Num#(Date(MakeDate(2019,4,19),'MMYYYY')) //Вернет 042019
Пример 2:
Num#(Week(MakeDate(2019,4,19))&Year(MakeDate(2019,4,19))) //Вернет 162019
Иногда требуется в отчете вывести период, за который он был сформирован. Например, отчет за предыдущую неделю.
В скрипте определяем переменную Прошлая Неделя:
LET vReportWeekPeriod = Num#((Week(Today())-1)&Year(Today()));
В календаре есть поле НеделяГод, с помощью этого поля определяем минимальную и максимальную даты (функция Date используется для форматирования даты в нужном виде, т.е. ‘DD.MM.YY’):
='Отчет. Период '&
Date(min({<НеделяГод={'$(vReportWeekPeriod)'}>} Дата),'DD.MM.YY')
&'-'&
Date(max({<НеделяГод={'$(vReportWeekPeriod)'}>} Дата),'DD.MM.YY') Если нужно упорядочивать записи по неделям, то рекомендую использовать формат ГодНеделя, причем неделя должна быть записана как 201901 и 201911 (т.е. два знака). В скрипте этот прием использован:
[TEMP Кол-во отгрузок По неделям]:
LOAD
[Клиент ID],
Неделя,
[КлиентНеделяID],
count(DISTINCT [Документ ID]) As [Количество Отгрузок Неделя]
Group By [Клиент ID], Неделя, [КлиентНеделяID];
LOAD
Distinct
[КлиентНеделяID],
[Клиент ID],
Num#(Year(Дата)&if(Len(Week(Дата))=1,'0'&Week(Дата),Week(Дата))) As Неделя,
[Документ ID]
Resident [Факты];
NoConcatenate
[Номер отгрузки по неделям]:
LOAD
if([Клиент ID]=Previous([Клиент ID]),
RangeSum(Peek('Номер отгрузки по неделям'), 1),
1
) As [Номер отгрузки по неделям],
[КлиентНеделяID]
Resident [TEMP Кол-во отгрузок По неделям]
Order By [Клиент ID], Неделя
;
DROP Table [TEMP Кол-во отгрузок По неделям]; //Переменная Скрипта Qlik Sense - вчерашняя дата LET vTodayReport_Date = Date(Today()-1); //Переменная Скрипта Qlik Sense - дата "неделя назад" LET v7DaysAgoReport_Date = Date(Today()-7); //Переменная Скрипта Qlik Sense - дата "45 дней назад" LET v45DaysAgoReport_Date = Date(Today()-45); //Переменная Скрипта Qlik Sense - дата "месяц назад" LET v1MonthAgoReport_Date = AddMonths(Today(),-1); //Переменная Скрипта Qlik Sense - дата "3 месяца назад" LET v3MonthsAgoReport_Date = AddMonths(Today(),-3); //Переменная Скрипта Qlik Sense - дата "год назад" LET v1YearAgoReport_Date = AddYears(Today(),-1);
Формат длинных названий месяцев задается на вкладке Main с помощью системных переменных. Значения этих переменных задается по-умолчанию при создании приложения:
SET MonthNames='янв.;февр.;мар.;апр.;мая;июн.;июл.;авг.;сент.;окт.;нояб.;дек.'; SET LongMonthNames='январь;февраль;март;апрель;май;июнь;июль;август;сентябрь;октябрь;ноябрь;декабрь';
Рекомендую заменить их на следующие значения (точки будут портить вид при формате DD.MMM.YYYY):
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек'; SET LongMonthNames='января;февраля;марта;апреля;мая;июня;июля;августа;сентября;октября;ноября;декабря';
Формат даты с названием дня недели:
//Формат даты "11 июн 2019 (вт)" Date(Today(),'DD MMM YYYY (WWW)') //Формат даты "11 июн 2019 (вторник)" Date(Today(),'DD MMM YYYY (WWWW)') //Формат даты "11 июня 2019 (вторник)" Date(Today(),'DD MMMM YYYY (WWWW)')
LET пГодУстановка = 2017; // 2017 LET пФильтрДата = MakeDate(пГодУстановка); // 01.01.2017 LET пФильтрДатаПрошлыйГод = MakeDate(пГодУстановка-1); // 01.01.2016
todo
Данные хранятся в датах англоязычного календаря. Нужно произвести преобразование в русский формат даты:
Для преобразования используем следующий скрипт (с меппингом):
[MAP Месяц в число]:
Mapping
LOAD * Inline [
Месяц, Месяц номер
january, 1
february, 2
march, 3
april, 4
june, 6
july, 7
august, 8
september, 9
october, 10
november, 11
december, 12
];
Данные:
LOAD
Дата As [Дата исходная],
SubField(Дата,' ',1) As День,
lower(SubField(Дата,' ',2)) As Месяц,
ApplyMap('MAP Месяц в число',lower(SubField(Дата,' ',2)),'#Не определено#') As МесяцНомер,
SubField(Дата,' ',3) As Год,
Date(
MakeDate(
SubField(Дата,' ',3),
ApplyMap('MAP Месяц в число',lower(SubField(Дата,' ',2))),
SubField(Дата,' ',1)
)
,'DD.MM.YYYY'
) As Дата
FROM [lib://excel_date/TB01_20190404_054148.xlsx]
(ooxml, embedded labels, table is [Sheet1 (2)]); Получаем вот такой результат:
Введение Иногда при создании расширения Qlik Sense вам необходимо получить доступ к системным данным. Это…
// Команда, которая будет запускаться в Powershell на сервере LET vPowershellCommand = 'Get-ChildItem -Path ''lib://Data/QVDs''…
Как построить диаграмму Control Chart со скользящим средним в Qlik Sense? В этой статье будет…
Как сделать чередование цвета в строках прямой таблицы Qlik Sense? Если порядок строк неважен, то…
Обзор В этой серии руководств мы собираемся создать расширение Qlik Sense с использованием Nebula.js и…
Создание расширенного расширения визуализации с использованием Qlik Nebula.js и D3.js В моем последнем посте я рассказал…
This website uses cookies.