При разработке аналитических отчетов в Системе компоновки данных (СКД) разработчики часто сталкиваются с необходимостью расчета сложных показателей, таких как коэффициенты сезонности. Трудности начинаются тогда, когда нам требуется не просто рассчитать коэффициент для каждого месяца, но и получить его среднее значение по годам или другим аналитическим разрезам (контрагентам, номенклатуре, складам). Попытка сделать это «в лоб» через ресурсы СКД обычно приводит к системной ошибке: «Выражение не может быть вычислено».
В этой статье мы подробно разберем, почему возникает данная ошибка, проанализируем внутренние механизмы СКД и рассмотрим четыре практических способа реализации этой задачи.
Давайте выясним, почему стандартный подход вызывает сбой. Автор темы на форуме пытается рассчитать коэффициент сезонности по следующей формуле:
КоэфСез = Сумма на каждый месяц / (ВычислитьВыражение("Сумма(Сумма)", "Месяц", , "Первая", "Последняя") / КолвоМесяцев)
Для расчета среднего коэффициента по группировкам на вкладке «Ресурсы» разработчик пишет выражение:
ВычислитьВыражениеСГруппировкойМассив("Сумма(КоэфСез)", "ГодКоэфСез")
В этот момент СКД выдает ошибку компоновки. Дело в том, что функции ВычислитьВыражение (которая работает как оконная функция) и ВычислитьВыражениеСГруппировкойМассив (которая собирает массив для агрегации) работают на разных этапах компоновки данных. Платформа 1С имеет жесткое ограничение: запрещено использовать вложенные вычисления агрегатных и оконных функций друг в друге. СКД не может построить корректный двухуровневый план вычислений, когда одна динамическая функция зависит от результатов другой динамической функции.
К счастью, обойти это ограничение можно несколькими способами. Рассмотрим их подробно.
Если структура вашего отчета должна динамически меняться (например, пользователь может группировать данные по Контрагенту, Номенклатуре или Складу), лучшим решением будет переложить расчет базового (годового) уровня продаж на механизм связей наборов данных СКД. Это избавит нас от использования сложных формул ВычислитьВыражение.
Разберем этот метод по шагам:
Год, Контрагент и Номенклатура. В качестве приемника годовой суммы указываем поле из Набора 2.КоэфСез с простой формулой:
ЕжемесячнаяСумма / (ГодоваяСумма / 12)
КоэфСез является простым числом. Теперь на вкладке «Ресурсы» мы можем спокойно написать стандартное выражение:
Среднее(КоэфСез)
Если вы работаете на современной версии платформы 1С:Предприятие (8.3.20+), вам доступна официальная поддержка оконных функций непосредственно в языке запросов. Это позволяет рассчитать годовой итог еще на уровне СУБД, вообще не прибегая к механизмам СКД.
Рассмотрим пример построения такого запроса:
ВЫБРАТЬ
Продажи.ПериодГод КАК Год,
Продажи.ПериодМесяц КАК Месяц,
Продажи.Контрагент КАК Контрагент,
Продажи.Номенклатура КАК Номенклатура,
Продажи.СуммаОбороты КАК МесячнаяСумма,
// Вычисляем годовую сумму с помощью оконной функции
СУММА(Продажи.СуммаОбороты) ОВЕР (
ПАРТИЦИЯ ПО
Продажи.ПериодГод,
Продажи.Контрагент,
Продажи.Номенклатура
) КАК ГодоваяСумма
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК Продажи
При таком подходе СКД получает плоскую таблицу, где для каждого месяца уже прописана итоговая сумма за соответствующий год. Нам остается лишь добавить вычисляемое поле КоэффициентСезонности:
МесячнаяСумма / (ГодоваяСумма / 12)
В ресурсах для этого поля мы указываем простую агрегатную функцию Среднее(КоэффициентСезонности). Никаких ошибок вычисления не возникнет.
Если переписать запрос не представляется возможным, мы можем «разорвать» цепочку вложенных вычислений СКД, передав массив промежуточных значений во внешний программный код общего модуля.
Рассмотрим, как это реализовать:
РасчетСтатистикиПереопределяемый). Установим у него свойства «Клиент (управляемое приложение)» и «Сервер».
Функция РассчитатьСреднийКоэффициент(МассивЗначений) Экспорт
Если МассивЗначений.Количество() = 0 Тогда
Возврат 0;
КонецЕсли;
Всего = 0;
Для Каждого Значение Из МассивЗначений Цикл
Всего = Всего + ?(Значение = Неопределено, 0, Значение);
КонецЦикла;
Возврат Всего / МассивЗначений.Количество();
КонецФункции
РасчетСтатистикиПереопределяемый.РассчитатьСреднийКоэффициент(ВычислитьВыражениеСГруппировкойМассив("КоэфСез", "Год"))
Поскольку СКД передает в функцию уже готовый сформированный массив чисел (рассчитанных коэффициентов), мы обходим внутреннее ограничение платформы на вложенность агрегатов. Внешняя функция корректно возвращает среднее значение.
Компоновщик данных позволяет гибко настраивать формулу расчета одного и того же ресурса в зависимости от того, для какого уровня группировки он в данный момент рассчитывается. Для этого используется колонка «Рассчитывать по...» на вкладке «Ресурсы».
Проанализируем этот сценарий:
Сумма(Сумма) / (ВычислитьВыражение("Сумма(Сумма)", "Месяц", , "Первая", "Последняя") / 12)
Среднее(ВычисляемоеПолеМесячныйОбъем) / (Сумма(ВычисляемоеПолеГодовойОбъем) / 12)
Разделяя формулы расчета, мы указываем СКД четкие границы применения функций, предотвращая попытки рассчитать неподдерживаемые математические конструкции.
Завершая наш разбор, важно обратить внимание на содержательную сторону расчетов. При работе с реальными торговыми базами простое среднее арифметическое часто дает сильные погрешности из-за «выбросов» данных. Разовые крупные оптовые закупки или, наоборот, дефицит товара на складе могут исказить сезонные индексы.
Поэтому в профессиональных системах бизнес-аналитики рекомендуется: