Как посчитать АППГ (аналогичный период прошлого года) в AW BI

Рассмотрим, какими способами в AW BI можно рассчитать показатель за аналогичный период предыдущего года (далее — АППГ). Задачи, с которыми вы можете столкнуться, могут звучать, например, так:
Задача 1: сравнить числовой показатель за выбранный месяц текущего года и тот же месяц прошлого года.
Задача 2: сравнить показатель за произвольные периоды текущего и прошлого годов.

В статье разберём два подхода к расчёту:

Через редактируемую переменную.
Через предварительный расчёт в модели.

Расчёт АППГ через редактируемую переменную

Формула для расчёта может выглядеть так:

SUM_IF([value], [month] = [var_month] AND [year] = [var_year] - 1)

где:

[var_year] и [var_month] — редактируемые переменные,
[year], [month] — поля в модели с годом и месяцем,
[value] — значение показателя.

Пример

Допустим, у вас есть данные о количестве продаж по категориям и дате заказа.

Задача 1: при выборе месяца в фильтре нужно отобразить:

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

Решение:

создайте редактируемую переменную var_month в виджете. Значения — из поля модели с названиями месяцев. Если такого поля нет, создайте иерархию из даты (это можно сделать в настройках поля модели).
создайте расчётный агрегат для текущего года:

SUM_IF([value], [month] = [var_month] AND [year] = YEAR(NOW()))

3.создайте агрегат для прошлого

SUM_IF([value], [month] = [var_month] AND [год] = YEAR(NOW())-1

Здесь [var_month] — редактируемая переменная, созданная на шаге 1.

4.перетащите оба агрегата в область Агрегатов, а поле с категорией товаров — в область Групп.

Задача 2: рассчитаем этот же показатель за произвольный период.

Используем переменные [date_from] и [date_to] (начало и конец периода), и преобразуем их к аналогичным датам прошлого года:

SUM_IF([value],
  DATETIME([date]) >= DATE(CONCAT(STR(YEAR([date_from])-1), '-', STR(MONTH([date_from])), '-', STR(DAY([date_from]))))
  AND DATETIME([date]) <= DATE(CONCAT(STR(YEAR([date_to])-1), '-', STR(MONTH([date_to])), '-', STR(DAY([date_to]))))
)

где:

[date] — поле с датой в модели,
[date_from], [date_to] — редактируемые переменные для фильтра

Расчёт АППГ в модели

В этом варианте расчёт выполняется на уровне модели, а не в виджете.

Для этого:

Добавьте SQL-блок в модель.
Выполните JOIN таблицы самой на себя.

Пример SQL-запроса:

SELECT COALESCE(current_year.year, last_year.year + 1) AS year,
       COALESCE(current_year.month, last_year.month) AS month,
       COALESCE(current_year.day, last_year.day) AS day,
       COALESCE(current_year.data, TO_DATE(CONCAT(YEAR(last_year.data)+1, '-', MONTH(last_year.data), '-', DAY(fact_last_year.data)), 'yyyy-M-d')) AS data,
       COALESCE(current_year.category, last_year.category) AS category,
       current_year.value,
       last_year.value AS value_appg
FROM table current_year
FULL JOIN table last_year
  ON current_year.year = last_year.year + 1
  AND current_year.month = last_year.month
  AND current_year.day = last_year.day
  AND current_year.category = last_year.category

В результате вы получите поле value_appg — значение показателя за АППГ.

Теперь можно создать такой же виджет, как в предыдущем примере, перетащив поле category в область Групп, а поля value и value_appg — в область Агрегатов, указав для них функцию агрегации SUM.

Решение задачи 1 и задачи 2 теперь достигается за счёт добавления системных фильтров на дашборде.

Больше не требуется создавать редактируемые переменные и учитывать их при расчётах агрегатов.

Для задачи 1 — фильтр по году и месяцу.

Для задачи 2 — фильтр «Двойной календарь» для произвольного периода.

Преимущества первого способа (через редактируемую переменную):

Удобен для простых сценариев (например, сравнение по месяцам).
Не требует изменений в модели.

Преимущества второго способа (через модель):

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

В нашей бесплатной free-версии можно посмотреть примеры, приведенные в этой статье.