Contents
Функция диаграммы “AGGR()”
Функция Aggr() возвращает временную (виртуальную) таблицу с группировкой по одному или более измерениям. Функция Aggr() похожа на group by в SQL. Результирующая виртуальная таблица может быть использована внешней агрегирующей функцией в выражении (expression) или в вычисляемом измерении (calculated dimension).
Функция Aggr() возвращает диапазон значений заданной функции, сгруппированных по одному или более измерениям. Например, сумма продаж по каждому клиенту и по региону. Функция Aggr используется для расширенных агрегирований, в которых функция Aggr заключена в другую функцию агрегирования, а результаты функции aggr() используются для дальнейших вычислений в функции, в которую она вложена.
MindMap с примерами использования функции Aggr() в QlikView:
Синтаксис
Функция Aggr() имеет следующий синтаксис:
1 |
Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, dim{, dimension}) |
Возвращаемый тип данных: dual
Пример записи функции Aggr():
1 |
max( Aggr( sum(Sales)/count(Order), Country, Salesperson ) ) |
Аргументы
Аргумент | Описание |
expr | Выражение обычно состоит из функции агрегирования. По умолчанию функция агрегирования агрегирует множество возможных записей, определенных выборкой. |
dim | Измерение, для которого определен диапазон значений в выражении. Это одиночное поле, оно не может быть выражением. |
dimension | Дополнительно. Одно или несколько измерений, по которым выражение можно в дальнейшем расширять. |
SetExpression | По умолчанию функция агрегирования агрегирует множество возможных записей, определенных выборкой. Альтернативный набор записей может быть определен набором выражений анализа. |
DISTINCT | Если перед аргументом выражения стоит префикс distinct, или его вообще нет, то каждая комбинация значений измерений будет создавать только одно возвращаемое значение. Это обычный способ создания агрегирований — каждая комбинация значений измерений будет воспроизводить одну линию в диаграмме. |
NODISTINCT | Если перед аргументом выражения стоит префикс nodistinct, то каждая комбинация значений измерений может создавать несколько возвращаемых значений в зависимости от базовой структуры данных. Если измерение только одно, функция aggr вернет массив с тем же количеством элементов, что и строк в исходных данных. |
Базовые функции агрегирования, такие как Sum, Min и Avg, возвращают отдельное числовое значение, тогда как функцию Aggr() можно сравнить с созданием временной прямой таблицы, которая используется при создании диаграмм. Например, поиск максимального значения по клиенту. Далее мы можем найти минимальное значение в результирующей временной таблице. Функция Aggr() используется для вложения исходного агрегирования и помещения его в базовую функцию агрегирования Sum, Max или Count. Например: Min(Aggr(Max(Value),Customer)).
Ограничения
Каждое измерение, прописанное в функции Aggr(), должно являться отдельным полем модели данных QlikVIew и не может быть вычисляемым измерением (calculated dimension).
Простой пример использования функции Aggr() в QlikView
В скрипте загрузки данных пишем код:
1 2 3 4 5 6 7 8 9 10 11 12 |
ProductData: 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 Canutility, AA, 8, 15 Canutility, CC, , 19 ]; |
Результат загрузки данных:
Создадим следующую визуализацию:
Создаем диаграмму вида «Straight Table»:
Max кол-во проданного:
1 |
Max(Aggr(Sum(UnitSales), Customer, Product)) |
Лучший продукт:
1 |
firstsortedvalue(Product,-Aggr(Sum(UnitSales), Customer, Product)) |
Min кол-во проданного:
1 |
Min(Aggr(Sum(UnitSales), Customer, Product)) |
Худший продукт:
1 |
firstsortedvalue(Product,Aggr(Sum(UnitSales), Customer, Product)) |
Топ 3 продаж с помощью Aggr() в ListBox
Рассмотрим пример создания отображения топ 3 продаж в лист-боксе. Будем использовать данные из предыдущего примера. Вот такую картинку мы получим:
Добавим дополнительные таблицы, для понимания внутренних вычислений в Aggr:
Прописываем в лист-боксе следующую совокупность формул:
1 2 |
='Топ 3 продаж:' & Chr(13) & Concat( DISTINCT If( Aggr( Rank( TOTAL Sum(UnitSales) ), Customer, Product )<=3, Customer & ' - ' & Product & Chr(13) )) |
*Chr(13) — это символ переноса строки.
Top-20 и Bottom-20 продаж с использованием Aggr функции
Для того, чтобы в таблице типа «Pivot Table» или «Straight Table» отобразить лучшие 20 и худшие 20 продаж, необходимо создать вычисляемое измерение.
Формула для Топ-20 продаж (с использованием функции AGGR):
1 |
=If(Aggr(Rank(Sum(Sales)) <= 20,Salesman),Salesman) |
Формула для Худших-20 продаж (с использованием функции AGGR):
1 |
=If(Aggr(Rank(-Sum(Sales)) <= 20,Salesman),Salesman) |
Преимущества использования Aggr
Мы можем использовать эту функцию в вычисляемых измерениях. Она будет создавать стороннее измерение, которое основано на выборках (установленных фильтрах).
Пример:
1 |
Aggr(if(Sum(Value)<=60,'0-60',if(Sum(Value)<=80,'61-80','>81')),Category) |
Функция предоставляет огромные возможности для визуализации в диаграммах. Мы можем получить любой уровень агрегации, минимум, максимум, среднее значение.
Пример:
1 |
Max(Aggr(Sum(Sales),Dimension)), Max(Aggr(Sum(Sales),Dimension),2) |
Недостатки использования Aggr
Когда таблицы модели данных приложения QlikView содержат большое количество строк (например, миллионы уникальных записей), агрегация в диаграммах очень сильно сказывается на производительности. Вычисляемые измерения также влияют на производительность. Поэтому в таких ситуациях (при больших массивах данных) пытайтесь избегать aggr функции. Вместо нее можно заранее рассчитывать агрегаты при загрузке данных.
Когда нами используется сложная логика или if() условия внутри агрегации, то это будет оказывать значительное влияние на производительность. Поэтому внимательно отслеживайте использование сложных условий и время перестроения диаграммы.
Пример:
1 |
Aggr(if(Sum(Sale)<=0,'<=0',if(Sum(Sale)<=....ect), Dimension) |
Leave a Reply