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