Вложенные агрегирования

Всегда один уровень агрегирования в функции

В типичном приложении может содержаться:

• один миллион записей данных;
• сто строк в сводной таблице;
• один KPI в датчике или текстовом поле.

Хотя эти три числа различаются по величине, они могут представлять все данные. Эти числа — просто различные уровни агрегирования.

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

Следующие выражения будут работать:

• Sum(Sales)
• Sum(Sales)/Count(Order Number)

Следующее выражение не будет работать, поскольку оно является вложенным агрегированием:

• Count(Sum(Sales))

Решение этой проблемы находится в форме функции Aggr(). Вопреки своему имени, это не функция агрегирования. Это функция «многие ко многим», скорее похожая матрицу в математике. Она преобразует таблицу с N записями в таблицу с M записями. Она возвращает массив значений. Она может также рассматриваться как виртуальная прямая таблица с одной мерой и одним или несколькими измерениями.

Функция Aggr() возвращает массив значений выражения, вычисленный по указанному измерению или измерениям. Например, максимальное значение продаж по каждому клиенту, по региону. В расширенных агрегированиях функция Aggr() заключается в другую функцию агрегирования с помощью массива результатов, полученных из функции Aggr() в качестве ввода в агрегирование, в которое она вложена.

Когда функция используется, оператор Aggr() создает виртуальную таблицу с одним выражением, сгруппированным по одному или нескольким измерениям. Затем результат этой виртуальной таблицы может быть снова агрегирован внешней функцией агрегирования.

Вычисление самой большой средней стоимости заказа

Давайте использовать простой оператор Aggr() в выражении диаграммы.

Нам нужно посмотреть общие показатели на региональном уровне, а также показать два более сложных выражения:

• Самая большая средняя стоимость заказа по менеджеру в каждом регионе.
• Менеджер, получивший ту самую большую среднюю стоимость заказа.

Можно легко вычислить среднюю стоимость заказа для каждого региона с помощью стандартного выражения Sum(Sales)/Count([Order Number]).

В приложении на листе Nested Aggregations находится таблица с названием Aggr() function.

Выполните следующие действия.

1. Выберите доступную таблицу Aggr() function.
   Откроется панель свойств.
2. Щелкните команду Добавить столбец и выберите параметр Мера.
3. Щелкните символ .
   Откроется редактор выражения.
4. Введите следующее: Sum(Sales)/Count([Order Number])
5. Щелкните Применить.

Нам нужно получить самую большую среднюю стоимость заказа для каждого региона. Необходимо использовать Aggr(), чтобы указать Qlik Sense, что нужно вычислить среднюю стоимость заказа для каждого региона по менеджеру и затем отобразить самую большую из них. Для получения средней стоимости заказа для каждого региона по менеджеру нужно включить эти измерения в оператор Aggr():

Aggr(Sum(Sales)/Count([Order Number]), Region, Manager)

С помощью этого выражения Qlik Sense создаст виртуальную таблицу, выглядящую следующим образом:

Когда Qlik Sense вычисляет отдельные средние стоимости заказов для каждого региона по менеджеру, необходимо найти самое большое из этих значений. Для этого функцию Aggr() нужно заключить в Max():

Max(Aggr(Sum(Sales)/Count([Order Number]), Manager, Region))

Выполните следующие действия.

1. Щелкните команду Добавить столбец и выберите параметр Мера.
2. Щелкните символ .
   Откроется редактор выражения.
3. Введите следующее: Max(Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
4. Щелкните Применить.

Можно просмотреть саму большую среднюю стоимость заказа для всех менеджеров на уровне региона. Это первое из наших двух сложных выражений. Следующая задача — показать имя менеджера, получившего самую большую среднюю стоимость заказа, рядом с самими значениями.

Для этого будет использоваться та же функция Aggr(), что и прежде, но на этот раз вместе с функцией FirstSortedValue(). Функция FirstSortedValue() указывает Qlik Sense вывести имя менеджера для определенного измерения, указанного во второй части функции:

FirstSortedValue(Manager,-Aggr(Sum(Sales)/Count(Order Number), Manager, Region))

Выполните следующие действия.

1. Щелкните команду Добавить столбец и выберите параметр Мера.
2. Щелкните символ .
   Откроется редактор выражения.
3. Введите следующее: FirstSortedValue(Manager,-Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
4. Щелкните Применить.