Мы часто сталкиваемся с необходимостью анализировать изменения цен на товары в отчетах, построенных на Системе Компоновки Данных (СКД). Особенно сложная ситуация возникает, когда приобретение номенклатуры происходит нерегулярно, и в некоторые периоды (например, месяцы) отсутствуют данные о ценах. В таких случаях стандартные механизмы СКД для получения "предыдущего" значения могут работать некорректно, так как они оперируют по отношению к соседним строкам в текущей группировке, игнорируя реальное наличие данных.
В данной статье мы подробно разберем, как реализовать вычисление изменения цены в процентах в отчете СКД, учитывая следующие сценарии:
Мы рассмотрим как простые подходы с использованием ВычислитьВыражение, так и более сложные, требующие доработки запроса или использования продвинутых возможностей платформы 1С.
В отчете СКД мы имеем табличную группировку, где колонки представляют собой "Год" и "Месяцы", строки — "Номенклатура", а поля — "Цена приобретения". Основная сложность заключается в том, что цена приобретения может отсутствовать в некоторых месяцах для определенной номенклатуры. Наша задача — вывести рядом с ценой изменение этой цены в процентах, адаптируя расчет под различные условия.
Этот сценарий наиболее простой, так как он предполагает сравнение только в тех случаях, когда данные о приобретении есть в обоих соседних месяцах. Если в предыдущем месяце приобретения не было, мы можем не выводить процент изменения или отображать 0.
Для реализации этого подхода в СКД мы используем вычисляемое поле с функцией ВычислитьВыражение(). Эта функция позволяет обратиться к значениям других ячеек отчета относительно текущей.
Шаги реализации:
Номенклатура, ПериодМесяц (или просто Период, из которого можно получить месяц), Об_СтоимостьПриход и Об_КоличествоПриход. Средняя цена приобретения будет вычисляться как Об_СтоимостьПриход / Об_КоличествоПриход.ЦенаПриобретения. Его выражение будет Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход).ПроцентИзменения_МесяцКМесяцу. Выражение для него будет выглядеть следующим образом:
ВЫБОР
КОГДА ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка", "Предыдущая", "Предыдущая") = 0
ТОГДА 0
КОГДА ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0) = 0
ТОГДА 0
ИНАЧЕ
(ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)
- ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка", "Предыдущая", "Предыдущая"))
/ ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка", "Предыдущая", "Предыдущая") * 100
КОНЕЦ
Разберем это выражение:
КОГДА ... ТОГДА 0 проверяют, было ли приобретение в текущем или предыдущем месяце. Если количества приобретения (Об_КоличествоПриход) нет, это означает отсутствие данных, и мы возвращаем 0.ИНАЧЕ. Здесь мы:
ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0).ВычислитьВыражение("ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0)", "ПериодМесяц", "Группировка", "Предыдущая", "Предыдущая"). Здесь "ПериодМесяц" — это имя поля группировки по месяцу, "Группировка" — уровень группировки (текущая строка), а "Предыдущая", "Предыдущая" — указывает на взятие значения из предыдущей строки по этой группировке.(ТекущаяЦена - ПредыдущаяЦена) / ПредыдущаяЦена * 100 для расчета процентного изменения. Важно учитывать, что деление на ноль недопустимо, поэтому мы используем ЕСТЬNULL.Важно: Этот метод работает корректно, только если поле ПериодМесяц является непосредственной группировкой в отчете, и нам нужно сравнение именно с соседней ячейкой по этой группировке.
Это более сложная задача, так как нам нужно "пропустить" месяцы без приобретений и найти последнюю доступную цену. Форумчанин предложил достаточно громоздкое решение с каскадом ВЫБОР КОГДА ... Предыдущая(N). Давайте проанализируем его и предложим более элегантные и производительные варианты.
Форумчанин привел пример, как можно попытаться решить эту задачу, перебирая несколько предыдущих позиций в группировке:
ВЫБОР
КОГДА ПериодМесяц = ДобавитьКДате(&Период.ДатаНачала, "Месяц", -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
// ... и так далее до Предыдущая(12)
КОНЕЦ
Проблемы этого подхода:
ВычислитьВыражение могут негативно сказаться на скорости формирования отчета, особенно при больших объемах данных и сложных группировках.Предыдущая(N) оперирует порядком строк в текущей группировке, а не реальным наличием данных. Если между двумя покупками есть месяцы без покупок, но они все равно попадают в группировку, то Предыдущая(N) будет смотреть на эти "пустые" месяцы.Для сокрытия неактуальных данных, пользователь форума предложил использовать условное оформление и дополнительное поле в запросе:
ВЫБОР КОГДА НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ) < НАЧАЛОПЕРИОДА(&НачалоПериода, МЕСЯЦ) ТОГДА 0 ИНАЧЕ 1 КОНЕЦ КАК Выводить
А затем настроить условное оформление: Минимальная ширина = 0, Максимальная ширина = 0, если поле Выводить равно "0". Этот метод позволяет скрыть колонки, относящиеся к "подтягиваемой" истории, которая нужна только для расчета, но не для отображения.
Для более гибкого и эффективного решения этой задачи мы рекомендуем использовать подходы, которые обрабатывают данные до того, как они попадут в основной механизм СКД для группировки.
Если ваша платформа 1С версии 8.3.12 или новее, вы можете использовать оконные функции в языке запросов. Это наиболее элегантное и производительное решение для таких задач.
Суть метода: Оконные функции, такие как ЛАГ (LAG), позволяют получить значение из строки, находящейся на определенном смещении относительно текущей строки внутри заданного "окна" (партиции) данных. Мы можем партиционировать данные по номенклатуре и упорядочить по периоду, игнорируя строки, где нет приобретения.
Шаги реализации:
Об_КоличествоПриход > 0). Это упростит работу оконной функции.ЛАГ.Пример запроса с оконной функцией:
ВЫБРАТЬ
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ) КАК ПериодМесяц,
СУММА(ЗакупкиОбороты.Об_СтоимостьПриход) / СУММА(ЗакупкиОбороты.Об_КоличествоПриход) КАК ЦенаПриобретенияТекущая,
ЛАГ(СУММА(ЗакупкиОбороты.Об_СтоимостьПриход) / СУММА(ЗакупкиОбороты.Об_КоличествоПриход), 1, 0) ПОВЕРХ (РАЗДЕЛИТЬ ПО ЗакупкиОбороты.Номенклатура УПОРЯДОЧИТЬ ПО НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ)) КАК ЦенаПриобретенияПредыдущая
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ЗакупкиОбороты
ГДЕ
ЗакупкиОбороты.Об_КоличествоПриход > 0
СГРУППИРОВАТЬ ПО
ЗакупкиОбороты.Номенклатура,
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ)
Разберем элементы запроса:
ЛАГ(Выражение, Смещение, ЗначениеПоУмолчанию): Эта функция возвращает значение Выражения из строки, предшествующей текущей на Смещение позиций. ЗначениеПоУмолчанию используется, если нет предыдущей строки.ПОВЕРХ (РАЗДЕЛИТЬ ПО ЗакупкиОбороты.Номенклатура УПОРЯДОЧИТЬ ПО НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ)): Это "окно", в котором работает функция ЛАГ.
РАЗДЕЛИТЬ ПО ЗакупкиОбороты.Номенклатура: Делит данные на группы (партиции) по каждой номенклатуре. ЛАГ будет искать предыдущее значение только внутри одной номенклатуры.УПОРЯДОЧИТЬ ПО НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ): Определяет порядок строк внутри каждой партиции, по которому будет работать смещение.ГДЕ ЗакупкиОбороты.Об_КоличествоПриход > 0: Критически важно! Мы фильтруем данные, чтобы оконная функция работала только по тем месяцам, где были фактические приобретения. Таким образом, ЛАГ(..., 1, ...) всегда будет ссылаться на последнюю реальную покупку.После получения полей ЦенаПриобретенияТекущая и ЦенаПриобретенияПредыдущая в СКД, мы можем легко создать вычисляемое поле ПроцентИзмененияСПропусками:
ВЫБОР
КОГДА ЦенаПриобретенияПредыдущая = 0
ТОГДА 0
ИНАЧЕ
(ЦенаПриобретенияТекущая - ЦенаПриобретенияПредыдущая) / ЦенаПриобретенияПредыдущая * 100
КОНЕЦ
Этот подход значительно чище и производительнее, чем каскад ВЫБОР КОГДА.
Если оконные функции недоступны, мы можем получить аналогичный результат с помощью более сложных запросов с использованием временных таблиц или вложенных запросов. Этот метод будет более тяжелым для выполнения, но универсален.
Суть метода: Для каждой записи о приобретении мы должны найти дату и цену непосредственно предыдущего приобретения для той же номенклатуры. Это можно сделать, объединяя основной запрос с подзапросом, который находит максимальную дату приобретения, меньшую, чем текущая дата приобретения.
Шаги реализации:
Пример запроса с временной таблицей:
// Шаг 1: Получаем цены приобретения по месяцам для каждой номенклатуры
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ) КАК ПериодМесяц,
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
СУММА(ЗакупкиОбороты.Об_СтоимостьПриход) / СУММА(ЗакупкиОбороты.Об_КоличествоПриход) КАК Цена
ПОМЕСТИТЬ ЦеныПриобретения
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ЗакупкиОбороты
ГДЕ
ЗакупкиОбороты.Об_КоличествоПриход > 0
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ),
ЗакупкиОбороты.Номенклатура
;
// Шаг 2: Для каждой записи находим предыдущую цену
ВЫБРАТЬ
ТекущиеЦены.ПериодМесяц,
ТекущиеЦены.Номенклатура,
ТекущиеЦены.Цена КАК ЦенаТекущая,
(ВЫБРАТЬ ПЕРВЫЕ 1 ПредыдущиеЦены.Цена
ИЗ ЦеныПриобретения КАК ПредыдущиеЦены
ГДЕ ПредыдущиеЦены.Номенклатура = ТекущиеЦены.Номенклатура
И ПредыдущиеЦены.ПериодМесяц < ТекущиеЦены.ПериодМесяц
УПОРЯДОЧИТЬ ПО ПредыдущиеЦены.ПериодМесяц УБЫВ ) КАК ЦенаПредыдущая
ИЗ
ЦеныПриобретения КАК ТекущиеЦены
В этом запросе мы сначала формируем таблицу ЦеныПриобретения со всеми ценами по месяцам. Затем в основном запросе для каждой текущей цены мы ищем максимальную цену из ЦеныПриобретения с меньшим ПериодМесяц для той же номенклатуры. После получения ЦенаТекущая и ЦенаПредыдущая, расчет процента выполняется аналогично предыдущему пункту.
Для этих расчетов нам также потребуется дополнительная подготовка данных, чтобы определить цену первого и последнего приобретения в рамках каждого года для каждой номенклатуры.
СКД предоставляет агрегатные функции, такие как ПервыеЗначения() и ПоследниеЗначения(). Они могут быть полезны, но требуют особого внимания к упорядочиванию данных.
Шаги реализации:
Год (извлеченное из Периода) и ПериодМесяц, а также ЦенаПриобретения (как Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход)).ЦенаПервогоПриобретенияВГоду: ПервыеЗначения(ПериодМесяц, ЦенаПриобретения). Важно, чтобы группировка по "Году" была активна, а внутри года данные были упорядочены по ПериодМесяц.ЦенаПоследнегоПриобретенияВГоду: ПоследниеЗначения(ПериодМесяц, ЦенаПриобретения). Аналогично, данные должны быть упорядочены по ПериодМесяц.ПроцентИзмененияКПервомуВГоду:
ВЫБОР
КОГДА ЦенаПервогоПриобретенияВГоду = 0 ИЛИ ЕСТЬNULL(Сумма(Об_КоличествоПриход), 0) = 0
ТОГДА 0
ИНАЧЕ
(ЕСТЬNULL(Сумма(Об_СтоимостьПриход) / Сумма(Об_КоличествоПриход), 0) - ЦенаПервогоПриобретенияВГоду)
/ ЦенаПервогоПриобретенияВГоду * 100
КОНЕЦ
ПроцентИзмененияКПоследнемуВГоду: Аналогично, используя ЦенаПоследнегоПриобретенияВГоду.Нюансы: Функции ПервыеЗначения и ПоследниеЗначения работают в пределах текущей группировки и зависят от сортировки. Если в месяце нет данных, то его цена не будет учтена в этих функциях. Если нам нужно получить именно первое/последнее фактическое приобретение, то данные в запросе должны быть подготовлены так, чтобы "пустые" месяцы игнорировались, или эти функции применялись к уже агрегированным ценам по месяцам.
Этот подход более надежен, так как позволяет точно определить первое и последнее приобретение в году, независимо от группировок в СКД.
Шаги реализации:
ПериодМесяц, в которых было приобретение, и соответствующие им цены.Пример запроса с подзапросами:
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ) КАК ПериодМесяц,
ГОД(ЗакупкиОбороты.Период) КАК Год,
ЗакупкиОбороты.Номенклатура КАК Номенклатура,
СУММА(ЗакупкиОбороты.Об_СтоимостьПриход) / СУММА(ЗакупкиОбороты.Об_КоличествоПриход) КАК ЦенаТекущая,
(ВЫБРАТЬ ПЕРВЫЕ 1 ВТ_ПерваяЦена.Цена
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ) КАК ПериодМесяц,
ЗакупкиОборотыВТ.Номенклатура КАК Номенклатура,
СУММА(ЗакупкиОборотыВТ.Об_СтоимостьПриход) / СУММА(ЗакупкиОборотыВТ.Об_КоличествоПриход) КАК Цена
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ЗакупкиОборотыВТ
ГДЕ
ГОД(ЗакупкиОборотыВТ.Период) = ГОД(ЗакупкиОбороты.Период)
И ЗакупкиОборотыВТ.Номенклатура = ЗакупкиОбороты.Номенклатура
И ЗакупкиОборотыВТ.Об_КоличествоПриход > 0
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ),
ЗакупкиОборотыВТ.Номенклатура
УПОРЯДОЧИТЬ ПО НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ)
) КАК ВТ_ПерваяЦена
) КАК ЦенаПервогоПриобретенияВГоду,
(ВЫБРАТЬ ПЕРВЫЕ 1 ВТ_ПоследняяЦена.Цена
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ) КАК ПериодМесяц,
ЗакупкиОборотыВТ.Номенклатура КАК Номенклатура,
СУММА(ЗакупкиОборотыВТ.Об_СтоимостьПриход) / СУММА(ЗакупкиОборотыВТ.Об_КоличествоПриход) КАК Цена
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ЗакупкиОборотыВТ
ГДЕ
ГОД(ЗакупкиОборотыВТ.Период) = ГОД(ЗакупкиОбороты.Период)
И ЗакупкиОборотыВТ.Номенклатура = ЗакупкиОбороты.Номенклатура
И ЗакупкиОборотыВТ.Об_КоличествоПриход > 0
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ),
ЗакупкиОборотыВТ.Номенклатура
УПОРЯДОЧИТЬ ПО НАЧАЛОПЕРИОДА(ЗакупкиОборотыВТ.Период, МЕСЯЦ) УБЫВ
) КАК ВТ_ПоследняяЦена
) КАК ЦенаПоследнегоПриобретенияВГоду
ИЗ
РегистрНакопления.Закупки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ЗакупкиОбороты
ГДЕ
ЗакупкиОбороты.Об_КоличествоПриход > 0
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(ЗакупкиОбороты.Период, МЕСЯЦ),
ГОД(ЗакупкиОбороты.Период),
ЗакупкиОбороты.Номенклатура
После того как мы получили все необходимые цены (текущую, первую в году, последнюю в году) на уровне запроса, вычисление процентных изменений в СКД становится тривиальной задачей:
// Для первого приобретения в году
ВЫБОР
КОГДА ЦенаПервогоПриобретенияВГоду = 0
ТОГДА 0
ИНАЧЕ
(ЦенаТекущая - ЦенаПервогоПриобретенияВГоду) / ЦенаПервогоПриобретенияВГоду * 100
КОНЕЦ
// Для последнего приобретения в году
ВЫБОР
КОГДА ЦенаПоследнегоПриобретенияВГоду = 0
ТОГДА 0
ИНАЧЕ
(ЦенаТекущая - ЦенаПоследнегоПриобретенияВГоду) / ЦенаПоследнегоПриобретенияВГоду * 100
КОНЕЦ
Мы рассмотрели различные подходы к решению задачи. Выбирая тот или иной метод, рекомендуем учитывать следующие моменты:
ЕСТЬNULL или проверки КОГДА ... ТОГДА 0), чтобы избежать ошибок в отчете.Решение задачи по выводу изменения цены в процентах с учетом пропусков требует внимательного подхода к структуре запроса и понимания работы механизмов СКД. Надеемся, что предложенные методы помогут вам создать эффективные и информативные отчеты.