Как в СКД рассчитать среднее количество продаж только по складам с движениями?

При формировании аналитических отчетов в схеме компоновки данных (СКД) часто возникает задача расчета средних показателей. Например, требуется вычислить средний объем продаж товара не по всей сети магазинов, а исключительно по тем складам или торговым точкам, где реально были зафиксированы продажи за указанный период.

Если применить стандартную формулу деления общей суммы на количество всех складов через Количество(Различные Склад), в знаменатель попадут даже те склады, по которым движения отсутствовали. Это приводит к занижению среднего значения. Разберем по шагам, почему возникают ошибки в стандартных выражениях и как правильно настроить формулы ресурсов на языке выражений СКД.

Проанализируем две типичные ошибки, с которыми сталкиваются разработчики:

1. Подсчет строк вместо уникальных складов: если использовать конструкцию вида Сумма(ВЫБОР КОГДА Количество > 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ), система просуммирует количество детальных записей выборки. Если по одному складу было 5 продаж одного товара, знаменатель вернет 5, а не 1 склад.
2. Неверная область вычисления: функция ВычислитьВыражение с контекстом "ОбщийИтог" или "Склад" вычисляет выражение в рамках фиксированной области, что не позволяет корректно получить сумму подитогов в группировках по товарам или номенклатурным группам.

Способ 1. Использование функции ВычислитьВыражениеСГруппировкойМассив

Рассмотрим основной и самый надежный способ. Чтобы посчитать количество уникальных складов с ненулевыми продажами для вышестоящей группировки (например, в строке товара или в общих итогах), необходимо вычислить массив значений по подчиненной группировке Склад и затем просуммировать полученные результаты.

Выражение для подсчета количества складов с продажами выглядит следующим образом:

Сумма(ВычислитьВыражениеСГруппировкойМассив("ВЫБОР КОГДА Сумма(Количество) > 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ", "Склад"))

Также можно использовать третий параметр функции — отбор группировок, получая массив складов, у которых сумма продаж строго больше нуля:

Количество(ВычислитьВыражениеСГруппировкойМассив("Склад", "Склад", "Сумма(Количество) > 0"))

Объединим это выражение с расчетом суммы количества и добавим обязательную проверку деления на ноль для итогового ресурса:

ВЫБОР 
    КОГДА Сумма(ВычислитьВыражениеСГруппировкойМассив("ВЫБОР КОГДА Сумма(Количество) > 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ", "Склад")) = 0 
    ТОГДА 0 
    ИНАЧЕ Сумма(Количество) / Сумма(ВычислитьВыражениеСГруппировкойМассив("ВЫБОР КОГДА Сумма(Количество) > 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ", "Склад")) 
КОНЕЦ

Способ 2. Использование свойства функции Количество(Различные ...)

Проанализируем альтернативный вариант, который подходит для наборов данных, где записи уже предварительно свернуты по складам, либо если продажи представлены положительными величинами. Функция Количество в языке выражений СКД игнорирует значения NULL.

Если в операторе ВЫБОР опустить ветку ИНАЧЕ, при невыполнении условия будет возвращаться NULL:

ВЫБОР 
    КОГДА Количество(Различные ВЫБОР КОГДА Количество > 0 ТОГДА Склад КОНЕЦ) = 0 
    ТОГДА 0 
    ИНАЧЕ Сумма(Количество) / Количество(Различные ВЫБОР КОГДА Количество > 0 ТОГДА Склад КОНЕЦ) 
КОНЕЦ

Обратите внимание: этот вариант сработает корректно только в том случае, если в детальных строках по складу нет отрицательных сторнировочных записей, компенсирующих продажи до нуля.

Настройка вкладки «Ресурсы» в СКД

Разберем, как правильно назначить формулы для разных уровней группировки отчета на вкладке Ресурсы:

1. Добавим поле СреднееКоличество и в колонке Рассчитывать по выберем группировку Склад. Для этого уровня среднее равно самому количеству: Сумма(Количество).
2. Добавим еще одну строку с тем же ресурсом СреднееКоличество, указав в колонке Рассчитывать по вышестоящие группировки (например, Товар, Номенклатура, Иерархия) и ОбщийИтог. В поле выражения укажем формулу с функцией ВычислитьВыражениеСГруппировкойМассив.

Такое разделение позволяет оптимизировать вычисления: на уровне детального склада СКД не тратит ресурсы на сбор массива, а на уровне итогов и товаров корректно вычисляет среднее по активным складам.