Как в СКД рассчитать процент изменения ресурса относительно предыдущего периода в кросс-таблице?

← На главную

При построении аналитических отчетов в схеме компоновки данных (СКД) часто возникает задача вывести динамику показателей в кросс-таблице. Например, когда по строкам сгруппирована номенклатура, по колонкам выводятся периоды (даты), а на пересечении необходимо отобразить не только значение ресурса (цену или сумму), но и процент его изменения по сравнению с предыдущей колонкой.

Разберем, как реализовать такой расчет стандартными средствами механизма компоновки данных с помощью специализированной функции ВычислитьВыражение, а также рассмотрим альтернативные подходы и возможные проблемы при расчетах.

Использование функции ВычислитьВыражение на вкладке «Ресурсы»

Функция ВычислитьВыражение позволяет обращаться к значениям ресурсов в соседних ячейках и группировках отчета. Для вычисления динамики важно правильно передать параметры смещения записи: «Текущая» и «Предыдущая».

Рассмотрим синтаксис и формулу, которая рассчитывает процент прироста цены к предыдущему периоду:


(ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Текущая", "Текущая") 
- ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Предыдущая", "Предыдущая")) 
/ ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Предыдущая", "Предыдущая") * 100

Разберем по шагам параметры вызова функции:

Выражение: "Максимум(Цена)" — агрегатная функция и ресурс, значение которого мы получаем.
Группировка: "ТекущаяДата" — имя поля группировки колонок, по которой происходит смещение.
Область: оставлена пустой (по умолчанию).
Начало и Конец диапазона: указание "Предыдущая", "Предыдущая" возвращает значение ресурса из соседней левой колонки таблицы.

Безопасный расчет с защитой от деления на ноль и значений NULL

В реальных отчетах исходная формула может вызвать ошибку или вернуть пустые значения в двух случаях: для самого первого периода (где предыдущей колонки просто нет) и при нулевой цене в прошлом периоде. Проанализируем безопасный вариант формулы с использованием конструкции ВЫБОР и функции ЕстьNULL:


ВЫБОР
    КОГДА ЕстьNULL(ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Предыдущая", "Предыдущая"), 0) = 0 ТОГДА 0
    ИНАЧЕ (ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Текущая", "Текущая") 
          - ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Предыдущая", "Предыдущая")) 
          / ВычислитьВыражение("Максимум(Цена)", "ТекущаяДата", , "Предыдущая", "Предыдущая") * 100
КОНЕЦ

Настройку рекомендуется выполнять следующим образом:

На вкладке «Вычисляемые поля» добавим новое поле, например, ПроцентИзменения, оставив выражение пустым или указав 0.
Перейдем на вкладку «Ресурсы», добавим поле ПроцентИзменения и поместим в колонку выражения подготовленную расчетную формулу.
В колонке «Рассчитывать по...» укажем только группировку колонок (ТекущаяДата), чтобы избежать некорректного расчета в общих итогах таблицы.

Особенности и ограничения механизма

При использовании вычислений по смежным ячейкам в СКД важно учитывать следующие нюансы:

Зависимость от сортировки: Параметры "Предыдущая" и "Следующая" ориентируются строго на порядок колонок, заданный в настройках отчета. Изменение пользовательской сортировки повлияет на результат расчета.
Пропущенные периоды: СКД берет предыдущую существующую колонку в выборке. Если по товару не было движений в каком-то месяце, расчет выполнится относительно последней доступной даты, а не строго предыдущего календарного месяца. Для строгой календарной сетки необходимо использовать автодополнение периодов.
Ограничения отборов: На поля, рассчитанные через ВычислитьВыражение с контекстом соседних группировок, нельзя накладывать стандартные групповые отборы СКД.

Альтернативный вариант: расчет на уровне запроса

Если требуется избежать сложностей с вычислениями в СКД или перенести логику на уровень СУБД, можно получить значение предыдущего периода прямо в тексте запроса с помощью оконных функций:


ВЫБРАТЬ
    ДатыСтоимости.ТекущаяДата КАК ТекущаяДата,
    СтоимостьНоменклатуры.Номенклатура КАК Номенклатура,
    СтоимостьНоменклатуры.Цена КАК Цена,
    LAG(СтоимостьНоменклатуры.Цена, 1) OVER (
        PARTITION BY СтоимостьНоменклатуры.Номенклатура 
        ORDER BY ДатыСтоимости.ТекущаяДата
    ) КАК ПредыдущаяЦена
ИЗ
    ДатыСтоимости КАК ДатыСтоимости
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Цены КАК СтоимостьНоменклатуры
        ПО ДатыСтоимости.Номенклатура = СтоимостьНоменклатуры.Номенклатура
        И ДатыСтоимости.Период = СтоимостьНоменклатуры.Период

При таком подходе поле ПредыдущаяЦена рассчитывается до попадания в компоновщик, что позволяет считать процент простой формулой в ресурсах или в вычисляемых полях без вызова контекстных функций компоновки.