Конструктор отчетов Qlik Sense. AdHoc Reporting. Гибкий отчет с измерениями и показателями

Что такое конструктор отчетов Qlik Sense (Qlik AdHoc Report)?

В строгом смысле, Конструктор отчетов Qlik Sense – это отчет, который создается на лету и отображает информацию в виде таблицы, которая является результатом вопроса, который еще не был выведен в отдельный рабочий отчет. Существует ограничение на количество таких производственных отчетов и бизнес-вопросов, которые можно заранее предвидеть и разработать, чтобы пользователи могли обращаться к ним в любое время. В широком смысле, конструктор отчетов (adhoc report) – это просто способ ответить на непредвиденные вопросы.

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

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

Конструктор отчетов Qlik Sense (Qlik AdHoc Report) – это отчет, в котором Вы выбираете измерения (аналитики, например, товары, контрагенты, периоды) и показатели (например, продажи шт. и продажи руб.)

Qlik Sense Expert Russia AdHoc Report

Визуально конструктор отчетов будет выглядеть следующим образом:

Загрузка данных в модель (создание модели Qlik Sense для примера AdHoc Report)

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

Скачать файл Excel с данными для модели Qlik Sense:

Также нам понадобится специальная функция, которая была разработана мною. Грузить ее будем из текстового файла:

В этом файле лежит вот такая функция:

if(SubStringCount('|' & Concat({$3} distinct [$1], '|') & '|', '|$2|') and GetSelectedCount([$1],False(),'$3'),1,0)

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

Пишем скрипт загрузки данных. У нас будет 4 раздела загрузки данных:

Скрипт с Main (Переменные):

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 FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='ru-RU';
SET CreateSearchIndexOnReload=1;
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';
SET LongMonthNames='января;февраля;марта;апреля;мая;июня;июля;августа;сентября;октября;ноября;декабря';
SET DayNames='пн;вт;ср;чт;пт;сб;вс';
SET LongDayNames='понедельник;вторник;среда;четверг;пятница;суббота;воскресенье';
SET NumericalAbbreviation='3:тыс;6:млн;9:млрд;12:трлн;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Факты (загрузка данных из Excel):

Факты:
LOAD
    Дата,
    Подразделение,
    Контрагент,
    Менеджер,
    Номенклатура,
    Продажи,
    Себестоимость,
    [Валовая прибыль]
FROM [lib://DataSource/QlikSense_SampleData_Example_Data_SetAnalysis.xlsx]
(ooxml, embedded labels, table is Data);

Календарь (на основе минимальной и максимальной дат формируем календарь – массив дат с месяцами, годами, кварталами и т.п.):

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

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

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

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

// Формируем таблицу с календарем в моделе Qlik Sense
[Простой календарь QS]:  
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];

AdHoc Конструктор отчетов – загружаем переменную из файла (формула для переменной с 3 параметрами). Плюс определяем набор измерений и показателей, которые будут доступны в нашем гибком конструкторе отчетов:

//==========================================================
// Загрузка формулы CUSTOM VARIABLE
//==========================================================
AdHocFunc:
First 1
LOAD
    @1 AS ФормулаДляПеременной
FROM [lib://DataSource/Func.txt]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);

LET vAdHocFunc = Peek('ФормулаДляПеременной',0,'AdHocFunc');

Drop Table AdHocFunc;

//==========================================================
// Формирование Data Island таблиц Измерений и Показателей
//==========================================================
[Измерения AdHoc]:
Load * Inline [
    Измерения
    Дата
    Дата длинный месяц
    День недели МесяцГод
    Квартал
    КварталГод
    Год Месяц в числах
    Месяц Год
    Год
    Год Неделя
    Неделя
    День
    Подразделение
    Контрагент
    Менеджер
    Номенклатура
];

[Показатели AdHoc]:
Load * Inline [
	Показатели
    Продажи
    Себестоимость
    Валовая прибыль
];

Загружаем данные и смотрим модель данных:

В модели данных у нас есть таблица фактов, которая связана по общему полю Дата с таблицей Календарь. Также в модели данных есть две таблицы, которые не имеют связей с другими таблицами (их называют data island – острова данных):

Создание визуальной части приложения конструктора отчетов QlikSense

Переходим к основной части. Создаем лист Конструктор отчетов:

Слева располагаем фильтры с 2 таблицами (измерения и показатели – это как раз наши Data Island). Сверху накидываем фильтры для фильтрации модели данных (основные контекстные аналитики – Год, месяц, товары, клиенты и т.п.). На остальной части располагаем обычную прямую таблицу (как сделать Pivot и как с ним работать в режиме конструктора – объясню потом).

Схема расположения фильтров и отчета на листе QS (можете как-то по-другому расположить – это всего лишь рекомендация):

Настройка фильтров Измерения и Показатели

Для того, чтобы отчет работал, нам необходимо создать Альтернативное состояние “Конструктор”:

После этого в фильтре для “измерений” и “показателей” указываем Другие состояния “Конструктор”:

Настройка отчета AdHoc в Qlik Sense – Прямая таблица

После того, как вынесли элемент визуализации таблица на лист, создаем столбцы (выбираем измерения и создаем меры/показатели). После того, как Вы накидали все измерения и показатели из модели данных в таблицу, необходимо прописать условия отображения столбцов, отдельно для измерений и отдельно для показателей.

Вставляем условие в поле:

Формула для вычисления (с использованием специальной переменной с параметром):

$(vAdHocFunc(Измерения,Год,Конструктор))

Для Измерений условие будет выглядеть следующим образом (должно быть ОК):

Аналогично для показателей:

Формула:

$(vAdHocFunc(Показатели,Продажи,Конструктор))

Проверяем функцию (что отображается ОК):

Как работать с конструктором отчетов Qlik Sense?

Для того, чтобы отобразить нужные столбцы – выбираем измерения, показатели. Также можно отобразить данные по конкретному году, товару, клиенту (используйте верхние фильтры):

Для того, чтобы в отчете сохранить нужные настройки и всякий раз не выбирать измерения, показатели, можно создать множество закладок с выборками Измерение и Показатели. Для этого сверху кликните на значок “Закладка” и нажмите кнопку создать закладку:

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

Дополнительные настройки конструктора отчетов Qlik Sense

В настройках таблицы переходим на вкладку дополнительно и прописываем следующее:

1. Снимаем галочку

2. Ставим условие для расчета таблицы

if(GetSelectedCount([Измерения],False(),'Конструктор') and GetSelectedCount([Показатели],False(),'Конструктор'),1,0)

3. Прописываем сообщение для пользователей

Выберите, пожалуйста, измерения и показатели

Скачать приложение “Конструктор отчетов Qlik Sense” со всеми формулами

Ссылка на скачивание приложения Qlik Sense. Буду благодарен, если поделитесь ссылками в социальных сетях на статью 🙂