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

← На главную

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

В этой статье разберем, как реализовать подобный расчет средствами языка выражений СКД без усложнения исходного запроса и с поддержкой динамической периодичности отчета.

1. Постановка задачи и базовый запрос отчета

Рассмотрим ситуацию, когда запрос отчета получает данные из виртуальной таблицы оборотов регистра накопления. Для обеспечения универсальности отчет использует параметр &Периодичность, который позволяет пользователю выбирать группировку: «День», «Неделя», «Месяц», «Квартал» или «Год».

Пример исходного запроса отчета:


ВЫБОР
    КОГДА &Периодичность = "День"
        ТОГДА ПродажиОбороты.ПериодДень
    КОГДА &Периодичность = "Неделя"
        ТОГДА ПродажиОбороты.ПериодНеделя
    КОГДА &Периодичность = "Месяц"
        ТОГДА ПродажиОбороты.ПериодМесяц
    КОГДА &Периодичность = "Квартал"
        ТОГДА ПродажиОбороты.ПериодКвартал
    КОГДА &Периодичность = "Год"
        ТОГДА ПродажиОбороты.ПериодГод
КОНЕЦ КАК Период,
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Магазин КАК Магазин,
ПродажиОбороты.КоличествоОборот КАК КоличествоОборот,
ПродажиОбороты.СтоимостьОборот КАК СтоимостьОборот
ИЗ
    РегистрНакопления.Продажи.Обороты(, , Авто, ) КАК ПродажиОбороты

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

2. Почему расчет процента сложен в запросе?

Попытка посчитать динамику прямо в SQL-запросе сталкивается с рядом сложностей. Поскольку периодичность отчета динамическая (выбирается пользователем), для получения данных предыдущего периода пришлось бы делать левое соединение таблицы с самой собой по сложным условиям смещения даты через функции работы с датами. Это громоздко и сильно снижает производительность.

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

3. Пошаговая настройка расчета в СКД

Разберем по шагам алгоритм настройки отчета с использованием языка выражений СКД.

Шаг 1. Создание вычисляемого поля

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

Шаг 2. Настройка выражения ресурса

Так как расчет должен выполняться для итоговых значений группировок и колонок кросс-таблицы, выражение необходимо задавать на вкладке «Ресурсы».

Если применить простейшее выражение для расчета доли:


СтоимостьОборот / ВычислитьВыражение("СтоимостьОборот", "Период", "Группировка", "Предыдущая", "Предыдущая") * 100

Мы получим отношение текущего периода к предыдущему (например, 120%), но не сам прирост. Кроме того, при отсутствии продаж в предыдущем периоде произойдет ошибка деления на ноль, а для первого периода отчета результат будет не определен.

Для корректной обработки всех случаев проанализируем и применим защищенную формулу расчета процента прироста:


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

4. Подробный разбор функции ВычислитьВыражение

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

Синтаксис функции:

ВычислитьВыражение(Выражение, Группировка, Область, Начало, Конец, Сортировка)

"Сумма(СтоимостьОборот)" — агрегатное выражение, которое рассчитывается в контексте указанной смещенной группировки.
"Период" — имя группировки, вдоль которой происходит перемещение (в нашем случае это колонка периода).
"Группировка" — область расчета. Указывает, что поиск ведется среди соседних элементов текущей группировки.
"Предыдущая" (4-й параметр - Начало) — указывает, что отсчет начинается с предыдущей записи.
"Предыдущая" (5-й параметр - Конец) — указывает, что выборка ограничивается ровно одной предыдущей записью.

5. Настройка визуального оформления отчета

Чтобы отчет выглядел наглядно и информативно, разберем базовые настройки оформления вычисляемого ресурса:

Формат вывода чисел: На вкладке «Параметры комплекта» или в настройках оформления поля зададим строку формата: ЧДЦ=2; ЧН=0.00. Чтобы добавить знак процента, можно указать формат ЧДЦ=2; ЧФ='L%'.
Условное оформление: Перейдем на вкладку «Настройки» -> «Условное оформление».
- Добавим условие: Если ПроцентИзмененияСуммы < 0, то цвет текста — Красный.
- Добавим условие: Если ПроцентИзмененияСуммы > 0, то цвет текста — Зеленый.

Использование встроенной функции ВычислитьВыражение позволяет эффективно решать задачи расчета динамики показателей нарастающим итогом или относительно прошлых периодов, сохраняя при этом гибкость запросов и высокую скорость работы отчетов в 1С.