Сравнительный анализ данных по периодам — одна из самых частых задач при построении управленческой отчетности. Типичный пример: необходимо вывести таблицу продаж, где рядом стоят показатели января 2018 и января 2019 года, а следом — процент их разницы. Сложность заключается в том, что стандартный механизм вывода периодов в Системе компоновки данных (СКД) обычно выводит периоды линейно (хронологически), что затрудняет расчет отклонений между "соседними" годами внутри одного месяца.
В этой статье мы разберем несколько подходов к решению этой задачи: от изменения структуры настроек до использования мощных встроенных функций языка выражений СКД.
Часто программисты пытаются решить задачу на уровне запроса, объединяя данные разных лет в разные колонки. Однако СКД позволяет сделать это гораздо элегантнее на уровне настроек отчета. Если нам нужно сравнить январь к январю, нам необходимо изменить логику группировки.
Рассмотрим по шагам, как настроить структуру колонок:
ВЫБРАТЬ
МЕСЯЦ(Продажи.Период) КАК НомерМесяца,
ГОД(Продажи.Период) КАК Год,
Продажи.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК Продажи
Период в колонки, создадим вложенную структуру:
НомерМесяца.Год.При такой настройке СКД сначала сгруппирует данные по январю, а внутри него создаст две колонки (например, 2018 и 2019). Теперь нам остается только добавить ресурс, который вычислит разницу.
Если нам нужно программно обратиться к значению предыдущей колонки (предыдущего года в рамках того же месяца), мы воспользуемся функцией ВычислитьВыражение(). Это наиболее гибкий способ, позволяющий вычислять отклонения в ресурсах без изменения структуры запроса.
Проанализируем ситуацию: когда СКД строит кросс-таблицу, она последовательно обходит группировки. Мы можем попросить систему взять значение текущей суммы и значение суммы из "соседней" ячейки. Для этого в поле "Выражение" на закладке "Ресурсы" для поля ПроцентОтклонения пропишем следующую конструкцию:
(Сумма(Сумма) - ВычислитьВыражение("Сумма(Сумма)", "Год", "Группировка", "Предыдущая"))
/ ВычислитьВыражение("Сумма(Сумма)", "Год", "Группировка", "Предыдущая") * 100
Разберем параметры функции:
"Сумма(Сумма)" — агрегатная функция, которую мы хотим рассчитать."Год" — имя группировки, в рамках которой производится расчет."Группировка" — область анализа."Предыдущая" — указывает системе, что нужно взять данные из предыдущей колонки (предыдущего года).Важно помнить о проверке деления на ноль. В реальном отчете выражение должно выглядеть более безопасно:
ВЫБОР
КОГДА ЕстьNull(ВычислитьВыражение("Сумма(Сумма)", "Год", "Группировка", "Предыдущая"), 0) = 0
ТОГДА 0
ИНАЧЕ (Сумма(Сумма) - ВычислитьВыражение("Сумма(Сумма)", "Год", "Группировка", "Предыдущая"))
/ ВычислитьВыражение("Сумма(Сумма)", "Год", "Группировка", "Предыдущая") * 100
КОНЕЦ
Выясним причину, по которой иногда предыдущие методы не подходят: например, если данных за прошлый год в базе нет совсем, и колонка просто не создается, что мешает расчету. В этом случае эффективным решением будет создание двух наборов данных в СКД.
КлючСвязи, равное номеру месяца.ДОБАВИТЬКДАТЕ(&НачалоПериода, "ГОД", -1). Поле КлючСвязи здесь также будет равно номеру месяца.КлючСвязи (номеру месяца).Теперь в одном наборе данных (результирующем) у нас в каждой строке (для каждого месяца) есть две разные колонки: СуммаТекущая и СуммаПрошлая. Расчет процента в "Вычисляемых полях" становится тривиальным:
(СуммаТекущая - СуммаПрошлая) / СуммаПрошлая * 100
Этот метод гарантирует, что даже если в текущем году продаж в январе не было, а в прошлом были, мы все равно увидим корректную строку в отчете.
Чтобы отчет выглядел профессионально, воспользуемся советами из обсуждения на форуме. Для вывода названий месяцев вместо чисел (1, 2, 3...) можно использовать поле выражения в вычисляемых полях или прямо в запросе:
ВЫБОР
КОГДА МЕСЯЦ(Период) = 1 ТОГДА "Январь"
КОГДА МЕСЯЦ(Период) = 2 ТОГДА "Февраль"
...
КОНЕЦ КАК ИмяМесяца
Обратите внимание: если вы используете текстовые названия месяцев, сортировка может нарушиться (Апрель станет первым по алфавиту). Чтобы этого избежать, на закладке "Наборы данных" для поля ИмяМесяца укажите в колонке "Выражение упорядочивания" числовое поле НомерМесяца.
Рассмотренные подходы позволяют гибко настраивать аналитические отчетов типа "Year-over-Year". Использование функции ВычислитьВыражение является наиболее современным и рекомендуемым методом для платформы 1С 8.3, так как оно минимизирует объем данных, передаваемых из БД, и перекладывает расчеты на мощный движок компоновки данных.