Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Не получили письмо с кодом активации?
Декабрь 13, 2018, 06:07:30 pm

Автор Тема: AGGR() - Qlikview-функция расширенного агрегирования, вопросы применения  (Прочитано 13859 раз)

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Нередко возникает вопрос о применении функции Qlikview AGGR(). В сети можно найти интересные примеры, но не понимая принципа работы AGGR() разобраться в них непросто.
Описание функции на сайте help.qlik.com

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

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

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

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

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

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Расчет среднего значения

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

И еще один пример с расчетом среднего:
(Извините, но вы не имеете доступа к галерее)
Как видим, среднее подсчитано в трех вариантах, и каждый может быть справедлив, в зависимости от ситуации и задачи.

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

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Следующий вопрос — кто является лидером внутри каждой группы? Требуется определить максимальное значение по подгруппе внутри каждой группы.
Для этого мы будем использовать функцию firstsortedvalue(), которая возвращает первое/последнее  значение первого параметра функции по величине второго. Работу функции несложно понять на простом примере:
(Извините, но вы не имеете доступа к галерее)
В нашем случае необходимо вернуть значение подгруппы, следовательно первым параметром будет 'Подгруппа', а вторым предварительная выборка Aggr(Sum([# Сумма]), Группа, Подгруппа) .
Результат представлен на рисунке ниже:
(Извините, но вы не имеете доступа к галерее)

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

Неофициальный форум пользователей QlikView & Qlik Sense


Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Подсчет лидеров и аутсайдеров

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


Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Подсчитать, как видим, несложно. Следующая задача — вывести в текстовом объекте названия Групп и подгрупп, попадающих в эти категории.
В предыдущей задаче мы перебирали виртуальную таблицу и складывали факты соответствий функцией 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) ))
И результат:
(Извините, но вы не имеете доступа к галерее)

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Идем далее,
Требуется вывести в текстовом объекте топ - 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 позиции по сумме в разрезе Групп и Подгрупп.
(Извините, но вы не имеете доступа к галерее)

Оффлайн 100tsky

  • Новичок
  • *
  • Сообщений: 14
  • Рейтинг: +3/-0
    • Просмотр профиля
добрый день!

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

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

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

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

Неофициальный форум пользователей QlikView & Qlik Sense


Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
Добрый день.

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

  • Новичок
  • *
  • Сообщений: 14
  • Рейтинг: +3/-0
    • Просмотр профиля
большое спасибо за оперативность!

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

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


Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 1237
  • Страна: ru
  • Рейтинг: +133/-0
    • Просмотр профиля
С учетом продукта будет уже 7 строк, т.к. будет вычисляться максимум для комбинации партнер+продукт, т.е. максимум по каждому продукту у каждого партнера.
Самый простой способ "посмотреть" виртуальную таблицу - задать выражение AGGR() в поле поиска.
А самый простой способ понять действие aggr() - провести аналогию с SQL запросом.
Т.е. :
Aggr(Max(UnitPrice), Customer)
аналогично:
tmpAggr1:
load max(UnitPrice) as [maxUni]
Resident temp
Group by Customer;

Оффлайн evstafievav

  • Стажер
  • *
  • Сообщений: 2
  • Рейтинг: +0/-0
    • Просмотр профиля
Вечер добрый!
Может быть этот форум не то место для такого вопроса, но может быть вы в курсе как тоже самое проделать с помощью PowerPivot (надстройка в Excel 2013). Никак сообразить не могу, нужно для учебы в институте.
Если что заранее спасибо!

Оффлайн 100tsky

  • Новичок
  • *
  • Сообщений: 14
  • Рейтинг: +3/-0
    • Просмотр профиля
в PowerPivot можно попробовать RANKX

Оффлайн evstafievav

  • Стажер
  • *
  • Сообщений: 2
  • Рейтинг: +0/-0
    • Просмотр профиля
в PowerPivot можно попробовать RANKX

Спасибо большое! Все получилось  8)

Оффлайн Иван новый

  • Новичок
  • *
  • Сообщений: 3
  • Страна: ru
  • Рейтинг: +1/-0
    • Просмотр профиля
Есть такая группировка
=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?

Неофициальный форум пользователей QlikView & Qlik Sense