AGGR() - Qlikview-функция расширенного агрегирования, вопросы применения

Автор admin, 01 октября 2014, 02:20:10

« назад - далее »

admin

Нередко возникает вопрос о применении функции Qlikview AGGR(). В сети можно найти интересные примеры, но не понимая принципа работы AGGR() разобраться в них непросто.
Описание функции на сайте help.qlik.com

На простом примере начнем знакомство с этой функцией.

Возьмем абстрактный пример - факт чего-либо (# Сумма)  по двум измерениям (Группа, Подгруппа)
[smg id=35]
Загружаем данные в приложение Qlikview и создаем простую таблицу на три колонки:
[smg id=36]
Мы получили данные о результатах работы групп по подгруппам с итогом.
Что еще мы можем получить из этого простого набора данных?

Отсортировав данные по сумме получаем минимальное и максимальное значение (13 237 и 96 040).
Включив опцию «Доля» для выражения суммы получим процент суммы для каждой строки в общей масссе. Того же результата можно добиться используя выражение -
Sum ([# Сделки])/Sum (TOTAL [# Сделки])
[smg id=37]
Следующий вопрос — чьи результаты меньше 50%  и более 80% от максимального показателя? Т.е. кто насколько отстает от лидера?
Т.е. требуется определить долю суммы каждого сочетания Группа-Подгруппа относительно максимального результата (отношение к 96 040).
Решается этот вопрос довольно просто с применением функции Aggr().
Sum([# Сумма]) / Max(TOTAL Aggr(Sum([# Сумма]), Группа, Подгруппа))
Почему не стоит использовать в знаменателе формулу  max (TOTAL [# Сумма]) , показано на рисунке.
[smg id=38 type=preview]
Теперь следует разобраться, как работает эта функция. Согласно описанию, функция aggr() возвращает виртуальную таблицу из значений выражения рассчитанного по измерениям, указанным в качестве параметров. Аналогично выражению GROUP BY оператора  SELECT SQL.
Т.е. запрос:
LOAD sum([# Сумма]) Resident SampleTable1 Group By Группа, Подгруппа
Дает тот же результат что и выражение:
Aggr(Sum([# Сумма]), Группа, Подгруппа)
Возвращаясь к ответу на поставленный вопрос, как определить группу лидеров и отстающих. Самое простое решение — использование визуальных подсказок для верхней и нижней границы, как показано на рисунке.
[smg id=39 type=preview]
Для отладки при составлении выражений можно использовать списки для просмотра результатов расчетов функции AGGR(). Здесь нет ничего сложного - используем выражение вместо поля при создании списка:
[smg id=40 type=preview]

Это самый простой случай использования функции AGGR(). На практике ее можно использовать и при создании измерений и для более сложных вычислений.
Если есть интерес, готов рассмотреть другие ситуации.

Код источника данных для этого примера:
Извините, вам запрещён просмотр содержимого спойлеров.

admin

Расчет среднего значения

[smg id=41 type=preview]
Использование AVG() без предварительного вычисления итогов внутри группы функцией AGGR(), может привести к ошибочным результатам.

И еще один пример с расчетом среднего:
[smg id=42 type=preview]
Как видим, среднее подсчитано в трех вариантах, и каждый может быть справедлив, в зависимости от ситуации и задачи.

Формулы для выражений:
Sum ([# Сумма])
Avg ([# Сумма])
avg(Aggr(Sum([# Сумма]), Группа, Подгруппа))
avg(Aggr(Sum([# Сумма])/Count(Подгруппа), Группа, Подгруппа))

admin

Следующий вопрос — кто является лидером внутри каждой группы? Требуется определить максимальное значение по подгруппе внутри каждой группы.
Для этого мы будем использовать функцию firstsortedvalue(), которая возвращает первое/последнее  значение первого параметра функции по величине второго. Работу функции несложно понять на простом примере:
[smg id=43 type=preview]
В нашем случае необходимо вернуть значение подгруппы, следовательно первым параметром будет 'Подгруппа', а вторым предварительная выборка Aggr(Sum([# Сумма]), Группа, Подгруппа) .
Результат представлен на рисунке ниже:
[smg id=44 type=preview]

Описание функции  FirstSortedValue()
Формулы для выражений:
max(Aggr(Sum([# Сумма]), Группа, Подгруппа))
FirstSortedValue(Подгруппа,-Aggr(Sum([# Сумма]), Группа, Подгруппа))

admin

Подсчет лидеров и аутсайдеров

Требуется определить количество подгрупп, находящихся по результатам ниже 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..."

Кстати, все вышесказанное относится и к QLIK SENSE
[smg id=47 type=preview]


admin

Подсчитать, как видим, несложно. Следующая задача — вывести в текстовом объекте названия Групп и подгрупп, попадающих в эти категории.
В предыдущей задаче мы перебирали виртуальную таблицу и складывали факты соответствий функцией 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) ))

И результат:
[smg id=48]

admin

Идем далее,
Требуется вывести в текстовом объекте топ - 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,
[smg id=49]
будет установлен фильтр на поля Группа и Подгруппа (указанным в параметрах функции AGGR(...,Группа, Подгруппа)), со  значениями соответствующими 10 позиции по сумме в разрезе Групп и Подгрупп.
[smg id=50]

100tsky

добрый день!

плиз, я новичок, дайте понимание!
почему в примере справки по aggr() по ссылке в посте говорится, что Aggr(Max(UnitPrice), Customer) возвращает диапазон 16, 20, 15 и 25. Как получается 15?

загрузил временную таблицу из справки, почему в самом примере
Canutility CC - 19
а ниже в коде загрузки
Canutility CC 19

встает же не в то поле?

построил несколько сводных таблиц в excel, но как выйти на 15 так и не понял
плиз!!!

admin

Добрый день.

Действительно, пример по этой функции на официальном сайте некорректен. Спасибо за информацию.
Корректное описание источника данных по справке:

temp:
LOAD * INLINE [
    Customer, Product, UnitSales, UnitPrice
    Astrida, AA, 4, 16
    Astrida, AA, 10, 15
    Astrida, BB, 9, 9
    Betacab, BB, 5, 10
    Betacab, CC, 2, 20
    Betacab, DD, 25, 25
    Canutility, AA, 8, 15
    Canutility, CC, , 19
];

И как может получиться по выражению Aggr(Max(UnitPrice), Customer) массив из 4 чисел, когда Customer имеет только 3 уникальных значения - мне тоже непонятно.
Вы можете воспользоваться кодом из первого сообщения данной темы для проверки результатов описаных примеров.
Что касается 19 не в том поле, думаю что между СС и 19 подразумевается 2 пробела, один из которых разделить (delimiter is ' ').
Спасибо, я сообщу, куда следует   ;)

100tsky

большое спасибо за оперативность!

вот вы пишете, что 4 значения при 3-х кастомерах.
после прочтения именно справки вроде готов был к этому, так как в справке четко прописано, что считает по кастомеру, но с учетом элемента product, а их 4 ед.

Или, виртуальная таблица, о которой они пишут, именно, что и создается без поля product?


admin

С учетом продукта будет уже 7 строк, т.к. будет вычисляться максимум для комбинации партнер+продукт, т.е. максимум по каждому продукту у каждого партнера.
Самый простой способ "посмотреть" виртуальную таблицу - задать выражение AGGR() в поле поиска.
А самый простой способ понять действие aggr() - провести аналогию с SQL запросом.
Т.е. :
Aggr(Max(UnitPrice), Customer)
аналогично:

tmpAggr1:
load max(UnitPrice) as [maxUni]
Resident temp
Group by Customer;

evstafievav

Вечер добрый!
Может быть этот форум не то место для такого вопроса, но может быть вы в курсе как тоже самое проделать с помощью PowerPivot (надстройка в Excel 2013). Никак сообразить не могу, нужно для учебы в институте.
Если что заранее спасибо!

100tsky


evstafievav


Иван новый

Есть такая группировка
=concat( IF(aggr(rank (aggr(count(price),price),class_rooms,price),class_rooms,price)<=1,
class_rooms&class(price/1000,250)&chr(13)))
По факту считает моду цены в отдельной категории. Как сделать, чтобы счет количества велся не по значению цены, а по интервалу значений, т. е. в интервале 1000-1250 руб., количество значений 15?

Яндекс.Метрика