Чарты с использованием функций AGO() и AT_DATE()

В данной статье мы рассмотрим как создать чарты с использованием функций AGO() и AT_DATE() для визуализации выгруженных Экстрактором 1С данных.

Подробнее об Экстракторе 1С

В данной статье мы рассмотрим как правильно использовать функции AGO() и AT_DATE() для корректного отображения данных в рамках чарта-таблицы чарта-комбинированной диаграммы, а также некоторые интересные дополнительные настройки и функции, которые помогли получить такие результаты.

Мы подготовили для Вас дашборд с использованием описанных ниже функции, ознакомиться с ним можно здесь: https://datalens.yandex/k512t46vlof84

Примечание:

Подробнее о базовых функциях Yandex DataLens (в том числе, AGO() и AT_DATE()) Вы можете почитать нажав кнопку «Справочник» в правом верхнем углу окна редактирования формул. Здесь же мы рассмотрим то, как их использовать для корректного отображения данных в чартах.

Чарт-таблица

Рассматривать применение функции будем на базе следующего чарта-таблицы:

Используемые в чарте измерения

В данном чарте-таблице визуализированы два нестандартных показателя: «Выручка (Сумма) год назад» и «Выручка (Сумма) год назад, приведенная ко дню недели». Они рассчитаны с помощью следующих измерений:

  • Дата события год назад;

  • Дата события год назад, приведенная ко дню недели.

Давайте рассмотрим функции, использованные в них подробнее.

Формула для измерения «Дата события год назад»

Данная формула задается на этапе настройки датасета с помощью функции DATETRUNC и выглядит следующим образом:

DATETRUNC (DATEADD([Период]), ‘year‘,-1, ‘day‘) , где мы берем [Период] добавляем к нему с помощью DATEADD год ‘year‘ и его количество, в данном случае, ‘-1' , а затем обрезаем по дню — ‘day‘ .

Формула для измерения «Дата события год назад, приведенная ко дню недели»

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

DATEADD([Дата события год назад],"day",[Смещение дней недели год назад])

Здесь также используется функция DATEADD , с помощью нее мы к дате события год назад [Дата события год назад] добавляем дни "day" , и функцию [Смещение дней недели год назад], которая проверяет день недели и прибавляет необходимое количество дней для приведения к конкретному дню недели.

Функция [Смещение дней недели год назад] разработана специально для данного чарта и выглядит следующим образом:

IF DAYOFWEEK([Дата события])-DAYOFWEEK([Дата события год назад])<0 THEN
7 + DAYOFWEEK([Дата события])-DAYOFWEEK([Дата события год назад])
ELSE
DAYOFWEEK([Дата события])-DAYOFWEEK([Дата события год назад])
END

Использование функций AGO() и AT_DATE()

Функция AGO() здесь используется для показателя «Выручка (Сумма) год назад»:

IFNULL(
AGO(
SUM([Выручка (Сумма)]),
[Дата события],
'year'
BEFORE FILTER BY [Год],
IGNORE DIMENSIONS [Дата события год назад],
[Дата события год назад, приведенная ко дню недели],
[Дата],
[Дата (год назад)],
[Дата события (год назад), приведенная ко дню недели]
),
0
)

Подробнее рассмотрим формулу:

  • столбец, который необходимо посчитать (расчет производится с использованием агрегатной функции): SUM([Выручка (Сумма)]) ;

  • столбец, по которому будет производится поиск события: [Дата события] ;

  • единица времени, со сдвигом на которую будет производиться поиск: 'year' .

Чтобы функция AGO() работала корректно, в данном случае, мы используем также следующие параметры функции:

  • параметр для исключения всех фильтров, которые не должны влиять на результат (в нашем случае — это фильтр по году): BEFORE FILTER BY;

  • так как, в нашем примере добавлены дополнительные столбцы с датами, но эти даты не должны влиять на результат расчетов, произведенных с помощью функции AGO(), необходимо исключить данные показатели для этой функции с помощью параметра IGNORE DIMENSIONS .

Таким образом, данный показатель должен выводить Выручку (Сумму) на дату, которая была ровно год назад. Например, если «Дата события» 03.01.2023, то показатель «Выручка (Сумма) год назад» должен отображать значение, которое было ровно год назад - 03.01.2022.

Функция AT_DATE() используется для показателя «Выручка (Сумма) год назад, приведенная ко дню недели» аналогичным образом:

IFNULL(AT_DATE(
SUM([Выручка (Сумма)]),
[Дата события],
[Дата события год назад, приведенная ко дню недели]
BEFORE FILTER BY [Год],
IGNORE DIMENSIONS [Дата события год назад],
[Дата события год назад, приведенная ко дню недели],
[Дата],
[Дата (год назад)],
[Дата события (год назад), приведенная ко дню недели]
),
0
)

Подробнее рассмотрим формулу:

  • столбец, который необходимо посчитать (расчет производится с использованием агрегатной функции): SUM([Выручка (Сумма)]) ;

  • столбцы, по которым будет производится поиск события: [Дата события] и [Дата события год назад, приведенная ко дню недели];

Чтобы функция AT_DATE() работала корректно, в данном случае, мы используем также следующие параметры функции:

  • параметр для исключения всех фильтров, которые не должны влиять на результат (в нашем случае — это фильтр по году): BEFORE FILTER BY;

  • так как, в нашем примере добавлены дополнительные столбцы с датами, но эти даты не должны влиять на результат расчетов, произведенных с помощью функции AT_DATE(), необходимо исключить данные столбцы для этой функции с помощью параметра IGNORE DIMENSIONS.

Таким образом, данный показатель должен выводить Выручку (Сумму) на дату, которая была ровно год назад, но приведенную к тому же дню недели, на который приходится исходная дата события. Например, если «Дата события» 03.01.2023 (это вторник), то показатель «Выручка (Сумма) год назад, приведенная ко дню недели» должен отображать значение которые было год назад и тоже во вторник - 04.01.2022.

Чарт-комбинированная диаграмма

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

Однако, в данном случае, стоит скорректировать формулы для показателей «Выручка (Сумма) год назад», «Выручка (Сумма) год назад, приведенная ко дню недели»: в них мы добавили к параметру BEFORE FILTER BY также ограничение по месяцу. Это необходимо для корректной визуализации данных. Измененные формулы выглядят следующим образом:

SUM(IFNULL(
AGO(
SUM([Выручка (Сумма)]),
[Дата события],
'year'
BEFORE FILTER BY [Год], [Месяц]
IGNORE DIMENSIONS [Дата события год назад],
[Дата события год назад, приведенная ко дню недели],
[Дата],
[Дата (год назад)],
[Дата события (год назад), приведенная ко дню недели]
),
0
)
IFNULL(AT_DATE(
SUM([Выручка (Сумма)]),
[Дата события],
[Дата события год назад, приведенная ко дню недели]
BEFORE FILTER BY [Год], [Месяц]
IGNORE DIMENSIONS [Дата события год назад],
[Дата события год назад, приведенная ко дню недели],
[Дата],
[Дата (год назад)],
[Дата события (год назад), приведенная ко дню недели]
),
0
)

В противном случае, если [Месяц] не добавить к параметру BEFORE FILTER BY, тогда функции AGO() и AT_DATE() будут некорректно отрабатывать на концах графика. Потому что обе функции являются оконными, и без изменения параметра BEFORE FILTER BY вычисляемое значение выходит за «рамки» окна и интерпретирует его как нулевое значение.