Рассмотрим, какими способами в AW BI можно рассчитать показатель за аналогичный период предыдущего года (далее — АППГ). Задачи, с которыми вы можете столкнуться, могут звучать, например, так:
Задача 1: сравнить числовой показатель за выбранный месяц текущего года и тот же месяц прошлого года.
Задача 2: сравнить показатель за произвольные периоды текущего и прошлого годов.
В статье разберём два подхода к расчёту:
Задача 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-запроса:
Для этого:
- Добавьте 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 теперь достигается за счёт добавления системных фильтров на дашборде.
Больше не требуется создавать редактируемые переменные и учитывать их при расчётах агрегатов.
Для задачи 1 — фильтр по году и месяцу.
Для задачи 2 — фильтр «Двойной календарь» для произвольного периода.
Преимущества первого способа (через редактируемую переменную):
Преимущества второго способа (через модель):
- Удобен для простых сценариев (например, сравнение по месяцам).
- Не требует изменений в модели.
Преимущества второго способа (через модель):
- Проще в использовании на дашборде, особенно при работе с произвольными периодами.
- Переносит нагрузку на уровень модели, ускоряя работу виджетов.
В нашей бесплатной free-версии можно посмотреть примеры, приведенные в этой статье.