При разработке отчетов в системе компоновки данных (СКД), программисты часто сталкиваются с ситуацией, когда стандартные агрегатные функции в ресурсах работают не так, как ожидается. Типичный пример — расчет конверсии. Если мы просто укажем Среднее(Конверсия) в общих итогах, система рассчитает среднее значение по всем детальным записям (строкам в базе данных). Однако для управленческого учета часто требуется иное: сначала посчитать конверсию по каждому сотруднику, а затем найти среднее арифметическое среди этих результатов. В этой статье мы разберем, как реализовать такой расчет по шагам.
Проанализируем ситуацию, описанную в теме. У нас есть три сотрудника с разными показателями продаж и поступлений. Конверсия по каждому из них рассчитывается как (Продано / Поступило) * 100. Если один сотрудник совершил 2 продажи из 2 поступлений (100%), второй — 1 из 2 (50%), а третий — 0 из 0 (0%), то среднее арифметическое их показателей должно быть 50% ((100 + 50 + 0) / 3).
Стандартный механизм СКД без дополнительных настроек попытается суммировать все продажи и разделить на все поступления по всем строкам базы данных. Если у первого сотрудника было 10 мелких чеков, а у второго — всего один, веса их показателей при обычном расчете будут разными, и итоговая цифра окажется далека от нужных нам 50%. Наша задача — заставить СКД сначала «схлопнуть» данные до уровня сотрудника, а затем выполнить расчет над этими агрегатами.
Рассмотрим первый способ, который позволяет гибко настраивать формулы расчета в зависимости от того, в какой строке отчета выводится результат. В СКД на вкладке «Ресурсы» мы можем добавить одно и то же поле несколько раз.
Конверсия. В колонке «Выражение» пропишем формулу для конкретного сотрудника:
Выбор Когда Сумма(КоличествоПоступило) = 0 Тогда 0
Иначе Сумма(КоличествоПродано) / Сумма(КоличествоПоступило) * 100
Конец
Ответственный (или Сотрудник).Конверсия в список ресурсов. На этот раз колонку «Рассчитывать по...» оставим пустой (это будет означать расчет для «Общего итога»).Сумма() здесь не сработает напрямую. Нам понадобится более продвинутый инструмент.Выясним причину, по которой это самый эффективный метод. Функция ВычислитьВыражениеСГруппировкойМассив позволяет получить массив значений, рассчитанных для каждой группировки, и выполнить над этим массивом любую агрегатную операцию.
Для решения нашей задачи в поле «Общий итог» ресурса Конверсия проанализируем и применим следующую конструкцию:
Среднее(ВычислитьВыражениеСГруппировкойМассив("Сумма(КоличествоПродано) / Выбор Когда Сумма(КоличествоПоступило) = 0 Тогда 1 Иначе Сумма(КоличествоПоступило) Конец * 100", "Ответственный"))
Разберем по шагам, что происходит в этой формуле:
"Ответственный"), по которой система должна собрать данные в массив.[100, 50, 0]) и вычисляет среднее арифметическое его элементов.Таким образом, мы получаем именно ту цифру, которую ожидает пользователь — 50. Это и есть решение проблемы «среднего от среднего».
Иногда в расчет не должны попадать сотрудники, у которых вообще не было активности (ни продаж, ни поступлений). Как упоминалось в сообщениях темы, для этого можно использовать вспомогательное поле Строки. Проанализируем ситуацию: если мы просто поделим на количество всех сотрудников, результат будет занижен из-за «нулевых» коллег.
В функции ВычислитьВыражениеСГруппировкойМассив предусмотрен третий параметр — Отбор. Мы можем модифицировать нашу формулу следующим образом:
Среднее(ВычислитьВыражениеСГруппировкойМассив(
"Сумма(КоличествоПродано) / Сумма(КоличествоПоступило) * 100",
"Ответственный",
"Сумма(КоличествоПоступило) > 0"
))
Здесь система сначала отфильтрует только тех ответственных, у которых сумма поступлений больше нуля, сформирует из их конверсий массив и только потом посчитает среднее. Это позволяет избежать искажения статистики «мертвыми душами».
Разберем подробнее, почему обычное поле Среднее в ресурсах ведет себя иначе. В СКД агрегатные функции по умолчанию работают в контексте записей набора данных. Если у одного сотрудника 100 записей в регистре, а у другого 1, то при использовании Среднее() первый сотрудник будет иметь в 100 раз больший «вес» в итоговой сумме.
Использование ВычислитьВыражениеСГруппировкойМассив фактически «схлопывает» (агрегирует) данные до одного значения на сотрудника перед выполнением финального расчета. Это критически важно для таких показателей, как:
Если по каким-то причинам использование массивов кажется сложным, можно рассмотреть вариант ручного деления суммы на количество. В ресурсе для общих итогов формула может выглядеть так:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Конверсия", "Ответственный")) / Количество(Различные Ответственный)
Однако этот метод менее надежен, так как он не учитывает фильтрацию (например, если нужно исключить сотрудников с нулевой активностью, как мы делали в способе 3). Поэтому рекомендуем придерживаться использования функции Среднее() совместно с генерацией массива по группировке.
Таким образом, правильная настройка ресурсов в СКД позволяет решать сложнейшие задачи по расчету итогов, не прибегая к изменению запроса или постобработке программным кодом. Достаточно понимать принцип работы функций вычисления выражений в контексте группировок.