Qlikview & Qlik Sense обладают большим набором функций, назначение некоторых из них трудно понять по одной лишь документации. Потребуется не один пример, чтобы разобраться и понять, насколько интересными особенностями обладает, например функция AGGR().
Возьмем абстрактный пример — факт чего-либо по двум измерениям (Группа, Подгруппа).
Загружаем данные в приложение Qlikview и создаем простую таблицу с подсчетом суммы:
Мы получили данные о результатах работы групп по подгруппам с итогом.
Что еще мы можем получить из этого простого набора данных?
Отсортировав данные по сумме получаем минимальное и максимальное значение (13 237 и 96 040), итоги по подгруппам для каждой группы.
Включив опцию «Доля» для выражения суммы получим процент суммы для каждой строки в общей массе. Того же результата можно добиться используя выражение —
Sum([Сделки])/Sum(TOTAL [Сделки])
Следующий вопрос — чьи результаты меньше 50% и более 80% от максимального показателя? Т.е. кто насколько отстает от лидера?
Для ответа на этот вопрос нам потребуется определить долю суммы каждого сочетания Группа-Подгруппа относительно максимального результата (отношение к 96 040).
Решается этот вопрос довольно просто с применением функции Aggr().
Sum([# Сумма])/Max(TOTAL Aggr(Sum([# Сумма]), Группа, Подгруппа))Почему не стоит в данном примере использовать в знаменателе формулу max (TOTAL [# Сумма]), понятно по рисунку. Объяснение простое — max(total..) находит максимальное значение для выражения (82851), а нам требуется найти максимальное значения для каждой подгруппы внутри группы (96040=40520+55520).
Теперь следует разобраться, как работает эта функция.
Aggr() returns an array of values for the expression calculated over the stated dimension or dimensions. For example, the maximum value of sales, per customer, per region. The Aggr function is used for advanced aggregations, in which the Aggr function is enclosed in another aggregation function, using the array of results from the Aggr function as input to the aggregation in which it is nested.
https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm
Согласно описанию, функция AGGR() возвращает виртуальную таблицу из значений выражения, рассчитанного по измерениям, указанным в качестве параметров. Аналогично выражению GROUP BY оператора SELECT SQL. Но при этом связь между полученными значениями и соответствующими измерениями сохраняется.
Т.е. запрос:
aggr_SampleTable1:
LOAD Группа,
Подгруппа,
Sum([Сумма]) as [Agrr_Сумма]
Resident SampleTable1 Group by Группа, Подгруппа;Формирующий в приложении таблицу с итогами:
Дает тот же результат что и выражение Aggr(Sum([# Сумма]), Группа, Подгруппа). Но для выражения aggr() таблица создается виртуально, и пересчитывается каждый раз при изменении выборки.
Для просмотра результата достаточно создать объект «Список» с выражением:
=Aggr(Sum([# Сумма]), Группа, Подгруппа)который выведет подсчитанные значения сум, сгруппированные по полям Группа и Подгруппа. Выбрав одно или более значений в полученном списке будет установлен фильтр по соответствующим значениям измерений Группа и Подгруппа.
«Завернув» наше выражение в функцию class() можно получить еще одно измерение — градацию по сумме, кратную показателю, в нашем случае 25000.
=class(Aggr(sum([# Сумма]), Группа, Подгруппа),25000)
Возвращаемся к ответу на поставленный вопрос, как определить группу лидеров и отстающих. Самое простое решение для визуализации — выделение цветом, используя визуальные подсказки для верхней и нижней границы, как показано на Рис. 9.
Расчет среднего.
Такая, вроде бы простая задача как расчет среднего, тоже не обходится без использования функции AGGR().
На нашем примере в группе ЮГ присутствуют две записи по подгруппе Ж. AVG() высчитывает среднее по записям, а нас интересует среднее по Подгруппам внутри Групп.
Определение лидеров.
Следующий вопрос — кто является лидером внутри каждой группы? Требуется определить максимальное значение по подгруппе внутри каждой группы.
Для этого мы будем использовать функцию firstsortedvalue(), которая возвращает первое/последнее значение первого параметра функции по величине второго. Работу функции несложно понять на простом примере:
В нашем случае необходимо вернуть значение подгруппы, следовательно первым параметром будет ‘Подгруппа’, а вторым предварительная выборка Aggr(Sum([# Сумма]), Группа, Подгруппа).
Результат представлен на рисунке ниже:
Подсчет аутсайдеров
Требуется определить количество подгрупп, находящихся по результатам ниже 50% от максимального результата.
Ранее мы уже определяли лидеров и отстающих, и выделили их результаты красным цветом. Теперь нам предстоит вывести их количество. Т.е. подсчитать сколько значений на выходе выражения:
Aggr(Sum([# Сумма]), Группа, Подгруппа)менее чем:
0.5*max(TOTAL Aggr(Sum([# Сумма]), Группа, Подгруппа))Обратите внимание на TOTAL в выражении max. В данном случае оно обязательно, иначе возникнет ошибка.
Формула подсчета соответствий условию проста:
=sum(if(
Aggr(Sum([# Сумма]), Группа, Подгруппа)< 0.5*max(TOTAL Aggr(Sum([# Сумма]), Группа, Подгруппа))
,1,0))Для расчета количества лидеров (превысивших порог 80% от максимума) формула будет отличаться лишь условием и коэффициентом — “> 0.8*max(TOTAL…”
Все просто. Подсчитать, как видим, несложно.
Следующая задача — вывести в текстовом объекте названия Групп и подгрупп, попадающих в эти категории.
В предыдущей задаче мы перебирали виртуальную таблицу и складывали факты соответствий функцией SUM(). Теперь, в случае соответствий условию необходимо складывать текстовые строки Группа&’ — ‘&Подгруппа&CHR(13) функцией CONCAT() и выводить результат в текстовом объекте.
Подсчет:
=sum(if(
Aggr(Sum([# Сумма]), Группа, Подгруппа)< max(total Aggr(Sum([# Сумма]), Группа, Подгруппа))*0.5
,1,0))Вывод значений:
=concat(IF(
Aggr(Sum([# Сумма]),Группа, Подгруппа)< max(total Aggr(Sum([# Сумма]), Группа, Подгруппа))*0.5,
Группа&' - '&Подгруппа&CHR(13) ))
Идем далее,
Требуется вывести в текстовом объекте топ — 5 лучших подгрупп в разрез групп.
Что нам для это требуется сделать.
Необходимо подсчитать итоги по группам и подгруппам, это мы умеем — Aggr(Sum([# Сумма]),Группа, Подгруппа) , затем потребуется отсортировать полученные итоги и пронумеровать и в порядке убывания.
Для этого используем функцию RANK(), после чего нам потребуется полученный массив перебрать и выбрать записи с показателем RANK() менее или равным 5.
Конечная формула для текстового объекта, выводящая значения по условию не так уж и сложна:
=concat( IF(aggr( rank( total Aggr(Sum([# Сумма]),Группа, Подгруппа)), Группа, Подгруппа )<= 5,Группа&' - '&Подгруппа&CHR(13) ))Снова обращаю внимание на TOTAL, без него формула не сработает, потому что первой функции AGGR(rank… просто не из чего будет делать выборку.
RANK() как и AGGR() можно использовать и при создании списков, наравне с полями. Значения, возвращаемые этими функциями остаются связанными со значениями измерений, по которым они формировались. Т.е. если создать список с выражением:
=Aggr( Rank ( Total Aggr(Sum([# Сумма]) ,Группа, Подгруппа) ),Группа,Подгруппа)То при выборе, например, значения 10,
будет установлен фильтр на поля Группа и Подгруппа (указанным в параметрах функции AGGR(…,Группа, Подгруппа)), со значениями соответствующими 10 позиции по сумме в разрезе Групп и Подгрупп.
Есть еще один вариант решения, с переменными. Если в приложении часто используется одно и то же выражение мы можем вынести его в переменную и указывать ее там, где необходимо.
Если бы мы опирались на какое-то определенное значение, скажем 80000, то мы бы могли решить задачу простой формулой:
=sum(if(
Aggr(Sum([# Сумма]), Группа, Подгруппа)< 80000
,1,0))Тогда наша формула примет вид:
