При разработке аналитических отчетов в системе компоновки данных (СКД) часто возникает задача не просто вывести текущие показатели, но и показать их изменение во времени. Одной из наиболее сложных задач является расчет динамики средних величин (например, средней цены), особенно когда требуется корректное отображение в группировках по иерархии номенклатуры и в общих итогах. В этой статье мы подробно разберем, как решить эту задачу, используя встроенные возможности языка выражений СКД.
Представим стандартную ситуацию: у нас есть набор данных, содержащий показатели СуммаВыручки и КоличествоПродаж. Нам необходимо рассчитать показатель СредняяЦена по формуле:
Сумма(СуммаВыручки) / Сумма(КоличествоПродаж)
Сложность начинается тогда, когда в отчете появляется группировка по периоду (например, по месяцам или кварталам), и нам нужно вывести поле ДинамикаЦены, которое равно разности средней цены текущего периода и предыдущего. Если просто вычитать значения в запросе, мы потеряем гибкость СКД при расчете итогов по иерархии папок номенклатуры, так как среднее от среднего — это математическая ошибка. Нам нужно, чтобы расчет производился после того, как СКД агрегирует данные по группировке.
Для решения подобных задач в СКД предусмотрена мощная функция ВычислитьВыражение(). Она позволяет вычислять значения в контексте других группировок, не выходя за рамки текущего расчета ресурсов. Проанализируем синтаксис, который нам понадобится:
ВычислитьВыражение(Выражение, Группировка, ОбластьВычисления, Начало, Конец)
Для расчета динамики мы будем использовать параметр "Предыдущая". Рассмотрим, как будет выглядеть выражение для ресурса ДинамикаЦены. Разберем по шагам создание формулы:
Сумма(СуммаВыручки) / Сумма(КоличествоПродаж)
ВычислитьВыражение("Сумма(СуммаВыручки) / Сумма(КоличествоПродаж)", "Период", , "Предыдущая", "Предыдущая")
При расчете динамики крайне важно обрабатывать ситуации, когда продаж в одном из периодов не было (деление на ноль) или когда мы находимся в самом первом периоде (предыдущего значения не существует). Проанализируем итоговую формулу для поля ДинамикаЦены в таб «Ресурсы»:
Выбор
Когда Сумма(КоличествоПродаж) = 0
Тогда 0
Иначе (Сумма(СуммаВыручки) / Сумма(КоличествоПродаж))
- ЕстьNull(ВычислитьВыражение("Сумма(СуммаВыручки) / Сумма(КоличествоПродаж)", "Период", , "Предыдущая", "Предыдущая"), 0)
Конец
Важный нюанс: В настройках ресурса в колонке «Рассчитывать по...» необходимо указать все группировки, кроме самой группировки Период. Это нужно для того, чтобы динамика считалась для номенклатуры, её иерархии и общих итогов, сравнивая значения внутри колонок-периодов.
Одной из ключевых особенностей СКД является расчет итогов сверху вниз или снизу вверх. Когда мы считаем динамику средней цены для папки номенклатуры, СКД сначала просуммирует всю выручку по папке, просуммирует всё количество, вычислит среднюю цену папки за текущий период, затем проделает то же самое для предыдущего периода и только потом найдет разницу. Это гарантирует математическую точность отчета.
Рассмотрим, как правильно настроить роли полей, если вы используете несколько периодов. Если отчет строится в виде таблицы, где Строки — это Номенклатура (с иерархией), а Колонки — Период, то формула ВычислитьВыражение будет работать «горизонтально», обращаясь к предыдущей колонке.
В некоторых случаях (например, если нужно посчитать не «динамику средней цены», а «среднюю динамику цен по товарам в папке»), нам потребуется другой подход. Выясним, как это работает через массивы:
Допустим, нам нужно в иерархии видеть среднее отклонение. Тогда выражение ресурса примет вид:
Среднее(ВычислитьВыражениеСГруппировкойМассив("ЦенаТекущая - ЦенаПредыдущая", "Номенклатура"))
Однако для классического анализа выручки и цен чаще всего используется именно первый метод (разность средних), так как он позволяет видеть реальное влияние изменения цены на общую выручку компании в разрезе товарных категорий.
Проанализируем ситуацию: в самой первой колонке отчета (например, за Январь) динамика будет равна самой цене за январь (так как предыдущее значение 0) или просто будет выглядеть некорректно. Чтобы скрыть эти данные, мы можем использовать системное поле НомерСтрокиВГруппировке или проверить индекс периода.
В выражении ресурса можно добавить проверку:
Выбор
Когда ВычислитьВыражение("Количество()", "Период", , "Предыдущая", "Предыдущая") = NULL
Тогда 0
Иначе [Наше_Выражение_Динамики]
Конец
Это позволит оставить первую колонку пустой, что сделает отчет более профессиональным и понятным пользователю.
Мы рассмотрели основной механизм работы с динамикой средних показателей в СКД. Использование ВычислитьВыражение избавляет от необходимости писать сложные вложенные запросы и позволяет использовать всю мощь встроенных агрегатных функций платформы 1С. Помните, что корректность итогов в иерархии напрямую зависит от того, как вы определили формулы в ресурсах: всегда старайтесь оперировать суммами (Сумма(Выручка)), а не заранее вычисленными средними значениями.