При создании отчетов на Системе Компоновки Данных (СКД) в 1С часто возникает задача вычисления показателей, зависящих от предыдущих значений, например, процентного изменения или разницы между текущим и прошлым периодом. Функция ВычислитьВыражение предоставляет мощный инструментарий для таких расчетов, но её использование в табличных отчетах, особенно при наличии пропусков в данных, может вызвать определенные трудности. Давайте подробно разберем, почему возникают эти сложности и как их преодолеть.
Основная сложность, с которой мы сталкиваемся при использовании функции ВычислитьВыражение с параметром "Предыдущая" в табличных отчетах, заключается в том, что функция оперирует не с визуальным представлением таблицы (колонки и строки, которые могут быть пустыми), а с фактическими записями в наборе данных, сформированном запросом. Если в каком-либо месяце для определенной номенклатуры не было движений (например, закупок), то соответствующая запись отсутствует в исходных данных. В такой ситуации ВычислитьВыражение("Поле", , ,"Предыдущая", "Предыдущая") будет искать предыдущую существующую запись в контексте текущей группировки.
Это может привести к нескольким нежелательным эффектам:
Для простых группировок (например, "Номенклатура - Месяц"), где каждый месяц для каждой номенклатуры представлен отдельной строкой, функция, как правило, работает корректно, поскольку "Предыдущая" однозначно указывает на предыдущую запись в рамках одной номенклатуры. Проблемы возникают именно в табличном режиме, когда месяцы идут как колонки.
Для начала рассмотрим базовый синтаксис ВычислитьВыражение, который работает в простых случаях или когда пропуски данных не критичны. Допустим, нам нужно получить количество прихода за предыдущий месяц для текущей номенклатуры.
В случае обычной (не табличной) группировки "Номенклатура - Месяц", если в отчете есть поле "КоличествоПриход", вы можете создать вычисляемое поле, например, "КоличествоПредыдущее", со следующей формулой:
ВычислитьВыражение("КоличествоПриход", , ,"Предыдущая", "Предыдущая")
Здесь:
Если мы работаем с агрегированными данными (например, сумма прихода за месяц), и у нас есть группировка по полю "ПериодМесяц" (колонка), то формулу можно уточнить:
ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая", "Предыдущая")
В этой формуле:
ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0): Мы получаем сумму количества прихода и заменяем NULL на 0, если данных нет."ПериодМесяц": Это поле группировки, по которому должно выполняться вычисление. В данном случае это колонка отчета."Группировка": Этот параметр указывает, что вычисление должно производиться в контексте текущей группировки. В табличном отчете это может означать текущую строку и текущую колонку.Важный аспект: Для корректной работы с ресурсами, основанными на вычисляемых полях, необходимо убедиться, что для ресурса в настройках СКД указано, по каким группировкам его "Рассчитывать". Например, для поля "КоличествоПредыдущее" ресурс должен рассчитываться по "ПериодМесяц", чтобы он применялся к каждой колонке.
Как мы уже выяснили, если в какой-то месяц нет данных, ВычислитьВыражение с "Предыдущая" может вернуть NULL или некорректное значение, если мы хотим найти ближайшее *непустое* значение. Один из самых эффективных способов решения этой проблемы – это последовательная проверка нескольких предыдущих периодов. Этот подход позволяет найти фактическое предыдущее значение, пропустив "пустые" месяцы.
Рассмотрим пример вычисления процента изменения цены. Нам нужно найти среднюю цену закупки в текущем месяце и сравнить ее с ценой закупки в ближайшем предыдущем месяце, где была закупка (то есть, количество прихода было больше нуля). Поскольку пропуски могут быть значительными, мы можем искать предыдущие значения вплоть до 12 месяцев назад.
Для реализации этой логики мы можем использовать конструкцию ВЫБОР КОГДА ... ТОГДА ... КОГДА ... КОНЕЦ в вычисляемом поле или непосредственно в ресурсе СКД. Представим, что у нас есть поля Об_КоличествоПриход и Об_СтоимостьПриход, и мы хотим рассчитать процент изменения средней цены (Об_СтоимостьПриход / Об_КоличествоПриход).
Вот пример такой формулы (обратите внимание на ее объемность, это необходимая жертва для достижения цели):
ВЫБОР
КОГДА ПериодМесяц = ДобавитьКДате(&Период.ДатаНачала, "Месяц", -12) ТОГДА 0 // Если это самый первый период в нашем окне просмотра (12 месяцев до начала периода отчета), то процент изменения 0
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") = 0 ТОГДА 0 // Если в текущем месяце нет прихода, то нет и изменения цены
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая", "Предыдущая") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая", "Предыдущая")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(2)", "Предыдущая(2)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(2)", "Предыдущая(2)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(3)", "Предыдущая(3)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(3)", "Предыдущая(3)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(4)", "Предыдущая(4)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(4)", "Предыдущая(4)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(5)", "Предыдущая(5)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(5)", "Предыдущая(5)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(6)", "Предыдущая(6)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(6)", "Предыдущая(6)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(7)", "Предыдущая(7)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(7)", "Предыдущая(7)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(8)", "Предыдущая(8)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(8)", "Предыдущая(8)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(9)", "Предыдущая(9)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(9)", "Предыдущая(9)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(10)", "Предыдущая(10)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(10)", "Предыдущая(10)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(11)", "Предыдущая(11)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(11)", "Предыдущая(11)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(12)", "Предыдущая(12)") <> 0 ТОГДА
(ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") - ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(12)", "Предыдущая(12)")) / ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") * 100
КОНЕЦ
Разберем логику этого сложного выражения:
КОГДА ПериодМесяц = ДобавитьКДате(&Период.ДатаНачала, "Месяц", -12) ТОГДА 0 – Это условие обрабатывает случай, когда мы находимся в самом первом месяце из 12-месячного окна просмотра (которое мы расширяем за счет ДобавитьКДате(&Период.ДатаНачала, "Месяц", -12)). В этом случае, очевидно, нет предыдущих данных для сравнения, поэтому процент изменения равен 0.КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Текущая", "Текущая") = 0 ТОГДА 0 – Если в текущем месяце нет прихода (количество равно 0), мы не можем рассчитать процент изменения цены, поэтому также возвращаем 0.КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка","Предыдущая(N)", "Предыдущая(N)") <> 0 ТОГДА ... – Это ключевая часть. Мы последовательно проверяем наличие прихода в предыдущем месяце (Предыдущая), затем в месяце, который был два месяца назад (Предыдущая(2)), и так далее, до Предыдущая(12). Как только мы находим месяц, в котором был приход (то есть, количество не равно 0), мы выполняем расчет процента изменения цены по формуле: (ТекущаяЦена - ПредыдущаяЦена) / ТекущаяЦена * 100. Важно, что в качестве "цены" мы используем среднюю цену, рассчитанную как Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход).Данное выражение гарантирует, что мы всегда найдем ближайшее предыдущее значение, при котором действительно были закупки, даже если между текущим месяцем и предыдущим были пропуски. Параметр "ПериодМесяц" в каждом вызове ВычислитьВыражение критически важен, так как он указывает, в каком контексте группировки (по месяцам) должна производиться навигация по "предыдущим" элементам.
Хотя ВычислитьВыражение достаточно гибка, в некоторых сложных случаях, когда логика становится чрезмерно громоздкой или не удается достичь желаемого поведения, более эффективным может быть предварительная подготовка данных на уровне запроса источника данных СКД.
Если пропуски в данных являются основной проблемой, мы можем "заполнить" отсутствующие периоды в запросе. Это достигается путем создания временной таблицы со всеми необходимыми периодами (например, всеми месяцами в выбранном диапазоне) и выполнения ЛЕВОГО СОЕДИНЕНИЯ (LEFT JOIN) с фактическими данными. Это позволит явно отображать нулевые значения вместо пропусков, делая набор данных "плотным".
Пример такого подхода в запросе 1С:
ВЫБРАТЬ
Периоды.Период КАК ПериодМесяц
ПОМЕСТИТЬ ВсеПериоды
ИЗ
(ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, МЕСЯЦ, Т.Номер - 1) КАК Период
ИЗ
(ВЫБРАТЬ РАЗЛИЧНЫЕ НОМЕР ИЗ ЧИСЛОВАЯПОСЛЕДОВАТЕЛЬНОСТЬ(&КоличествоМесяцев)) КАК Т
) КАК Периоды
;
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
ВсеПериоды.ПериодМесяц КАК ПериодМесяц,
ЕСТЬNULL(ЗакупкиОбороты.КоличествоПриход, 0) КАК КоличествоПриход,
ЕСТЬNULL(ЗакупкиОбороты.СтоимостьПриход, 0) КАК СтоимостьПриход
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВсеПериоды КАК ВсеПериоды
ПО ИСТИНА // Соединяем каждую номенклатуру со всеми периодами
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Закупки.Обороты(
&НачалоПериода,
&КонецПериода,
Месяц,
Номенклатура = Номенклатура.Ссылка) КАК ЗакупкиОбороты
ПО Номенклатура.Ссылка = ЗакупкиОбороты.Номенклатура
И ВсеПериоды.ПериодМесяц = НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ)
Этот запрос сначала генерирует список всех месяцев в заданном диапазоне, затем соединяет каждую номенклатуру со всеми этими месяцами. После этого производится левое соединение с фактическими данными о закупках. Таким образом, даже для месяцев без закупок будут созданы записи с нулевыми значениями количества и стоимости, что упростит дальнейшие вычисления в СКД.
В некоторых СУБД (например, PostgreSQL, MS SQL Server) существуют оконные функции, такие как LAG(), которые позволяют получить значение из предыдущей строки в рамках определенной партиции. Если ваша база данных поддерживает такие функции и вы имеете возможность использовать их напрямую в запросе (что не всегда просто в стандартном конструкторе запросов 1С), это может быть очень мощным решением. Однако в контексте стандартного запроса 1С и СКД без использования низкоуровневых запросов к СУБД этот подход менее применим.
После того как мы расширили диапазон данных для вычисления (например, добавили 12 месяцев до начала периода отчета, чтобы найти предыдущие значения), возникает вопрос: как скрыть эти "технические" данные, чтобы пользователю показывались только данные за выбранный им период? Для этого можно использовать условное оформление в СКД.
Добавим в запрос вычисляемое поле, которое будет служить флагом для отображения:
ВЫБОР
КОГДА НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ) < НАЧАЛОПЕРИОДА(&НачалоПериода, МЕСЯЦ) ТОГДА 0
ИНАЧЕ 1
КОНЕЦ КАК Выводить
В этом выражении:
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ)) с началом периода, выбранного пользователем в параметрах отчета (&НачалоПериода).Выводить принимает значение 0. В противном случае – 1.Далее, в настройках отчета (в разделе "Условное оформление") создаем новое правило:
Выводить Равно "0".Это позволит отрендерить отчет со всеми необходимыми данными для расчета, но визуально скроет те колонки/строки, которые выходят за рамки выбранного пользователем периода, делая отчет чистым и информативным.
ВычислитьВыражение с параметром "Предыдущая" сильно зависит от порядка сортировки данных в наборе. Изменение порядка сортировки может привести к тому, что "Предыдущая" будет указывать на совершенно другую запись. Убедитесь, что сортировка в вашем запросе и настройках СКД соответствует вашей логике.ВычислитьВыражение (третий параметр), чтобы гарантировать, что функция работает в нужном контексте (например, только для текущей номенклатуры, а не для всей таблицы).ЕСТЬNULL() при работе с данными, которые могут отсутствовать, чтобы избежать ошибок деления на ноль или некорректных расчетов.ВычислитьВыражение может вести себя непредсказуемо в очень сложных табличных структурах с глубокими группировками и многочисленными пропусками. В таких редких случаях может потребоваться полный пересмотр логики формирования данных в запросе или даже использование нестандартных подходов, таких как получение данных в массив/таблицу значений с последующей обработкой в коде на сервере. Функции ВычислитьВыражениеСГруппировкойМассив и ВычислитьВыражениеСГруппировкойТаблицаЗначений могут предоставить больше гибкости, но значительно усложняют отчет.Надеемся, эти подробные объяснения и примеры помогут вам успешно решить задачу по расчету процентных изменений и других показателей в табличных отчетах СКД с пропусками данных.