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

Примеры и гайды
,
1

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

Задача 1: сравнить числовой показатель за выбранный месяц текущего года и за аналогичный месяц прошлого года
Задача 2: сравнить тот же показатель, но за произвольный период прошлого и текущего годов.

В этой статье рассмотрим 2 способа расчета:

  1. Через редактируемую переменную

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

Пойдем по порядку

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

В этом случае формула может выглядеть следующим образом:

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

где [var_year] и [var_month] - редактируемые переменные,

[year], [month] - поля в модели с годом и месяцем.

[value] - значение показателя.

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

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

Решение:

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

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

  1. Создаем расчетный агрегат для вычисления показателя за прошлый год с формулой:

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

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

  1. Выводим оба агрегата в таблицу в область Агрегатов, а поле с категорией товаров - в область Групп.

Задача 2: теперь рассмотрим ту же задачу, но с выбором произвольного периода в фильтре.

Решение: в этом случае формула для прошлого периода будет иметь следующий вид:

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] - редактируемые переменные с датой начала и конца периода.

2. Расчет АППГ через расчет в модели.

В этом варианте расчет АППГ перекладывается с виджета на модель. В модель добавляем SQL-блок, в котором столбец со значением АППГ рассчитываем с помощью операции JOIN таблицы с данными саму на себя. Запрос может выглядеть примерно следующим образом:

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-го способа (через редактируемую переменную):

  1. Лаконичность расчета в случае, если достаточно рассчитывать АППГ относительно месяца.

  2. Нет необходимости в дополнительных расчетах на уровне модели.

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

  1. Простота реализации виджетов, в том числе для сценария с выбором произвольного периода.

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

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

Еще больше примеров расчетов АППГ и прошлых периодов можно посмотреть в подборке постов в нашем телеграм-сообществе:

  1. #Хак создания графика с динамикой по неделям и приростом неделя к неделе https://t.me/awcommunity/13737
Спойлер

  1. #Хак расчёта прироста показателя за неполный месяц к прошлому периоду в тренде Telegram: View @awcommunity
Спойлер

  1. #Хак показать текущий год в сравнении с прошлыми периодами Telegram: View @awcommunity
Спойлер