Как в СКД рассчитать итог по группировке для пересекающихся периодов (количество дней на складе)?

← На главную

При разработке отчетов по оборачиваемости в системе компоновки данных (СКД) программисты часто сталкиваются с проблемой корректного расчета агрегатов для временных показателей. Типичный пример — расчет поля «Количество дней на складе». Если просто использовать функцию МАКСИМУМ или СУММА в ресурсах, результат по группе товаров будет искажен, так как периоды нахождения разных товаров на складе могут пересекаться или, наоборот, иметь разрывы.

Рассмотрим ситуацию: Товар №1 был на складе 30 дней (с 1 по 30 апреля), а Товар №2 — 15 дней (с 29 апреля по 13 мая). Для группы, в которую входят оба товара, общее количество дней наличия на складе составляет 43 дня, так как 29 и 30 апреля они присутствовали одновременно. Стандартные функции выдадут либо 30 (максимум), либо 45 (сумма), что методологически неверно для расчета среднего срока хранения.

В данной статье мы разберем несколько способов решения этой задачи, от изменения структуры запроса до использования продвинутых функций языка выражений СКД.

Способ 1. Использование дополнительного набора данных с разверткой по дням

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

Алгоритм реализации:

Создадим в схеме компоновки данных второй набор данных (запрос). Этот запрос должен выбирать все уникальные даты из календаря или таблицы остатков, когда товар был в наличии.
В запросе мы получаем записи вида: Номенклатура, Склад, ДатаДень.
Свяжем основной набор данных с этим новым набором по полям Номенклатура и Склад (или другим аналитикам).
В поле ресурсов для показателя КоличествоДнейНаСкладе пропишем формулу:

Количество(Различные ДатаДень)

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

Способ 2. Использование функции ВычислитьВыражение в вычисляемых полях

Если по каким-то причинам мы не можем добавить второй набор данных, проанализируем возможность использования функций СКД для анализа предыдущих записей. Этот метод сложнее, но он позволяет вычислить «дельту» — только те дни, которые текущий интервал добавляет к уже учтенному периоду группировки.

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


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

Важно помнить, что данный метод требует строгой сортировки записей по ДатаС внутри группировки, иначе функция ВычислитьВыражение с направлением «Предыдущая» вернет некорректный результат. Посмотрим на нюанс: если текущий интервал полностью поглощается предыдущими, формула должна возвращать 0.

Способ 3. Разделение формул ресурсов для разных уровней группировок

Иногда расчет для детальных записей и для итогов должен идти по разным алгоритмам. Выясним, как настроить расчет ресурсов так, чтобы на уровне номенклатуры мы видели ее честный срок, а на уровне группы — сумму или максимум по заранее подготовленным в запросе данным.

В настройках СКД на закладке Ресурсы можно добавить одно и то же поле несколько раз, указывая в колонке «Рассчитывать по...» разные группировки:

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


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

Этот метод предполагает, что значение КоличествоДнейДляГруппы уже рассчитано в основном запросе с помощью вложенных запросов или временных таблиц, где номенклатура была исключена из разреза, и периоды были объединены на уровне СУБД.

Решение проблемы с условным форматированием шапки

Помимо математических расчетов, автор темы поднял важный вопрос оформления: как раскрасить итоги, но оставить шапку колонки стандартного цвета? В СКД по умолчанию условное форматирование применяется ко всей колонке, включая заголовок.

Чтобы исключить шапку, выполним следующие шаги:

Перейдем в настройки условного форматирования.
Найдем параметр «Область использования» (или «Область»).
Вместо значения «Везде» выберем конкретные области: «В группировке» и «В детальных записях».

В этом случае оформление применится только к ячейкам с данными, а заголовок (шапка) останется нетронутым, так как он относится к области «Заголовок», которую мы не выбрали.

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