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