Настройка отклонений через чекбокс и функции LAG

Настройка отклонений через чекбокс и функции LAG.
В данном материале рассматривается настройка отклонений, сравнение выбранного месяца с прошлым месяцем в процентном соотношении.

image
Рисунок 1. Текущий месяц, процент по отношению к прошлому месяцу.

Для данной реализации потребуется:

  1. Датасет с основными данными

  2. Отдельный датасет для чекбокса, в котором одно поле это текущий месяц (например 2 и 3 строка это февраль), во втором поле текущий и предыдущий (2 строка это февраль, а 3 строка это предыдущий месяц январь)


    Рисунок 2. Пример датасет для чекбокса.

  3. Чекбокс с включенной настройкой «Все кнопки активны»

  4. Фильтр для отправки месяца

  5. Виджет который будет выводить данные на дашборд, в данном случае «Ключевой показатель»

Настройка чекбокса:
Для настройки чекбокса, нам потребуется к нему подключить специфичный датасет с двумя измерениями «текущего» (месяц – это текущий) и «прошлого месяца» (прошлый месяц - это прошлый месяц и текущий)


Рисунок 3. Измерения чекбокса.

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

image image
Рисунок 4. Настройка чекбокса.

Переменные, которые слушает и отправляет чекбокс настраиваются следующим образом:
слушает переменную от фильтра который отправляет текущий месяц (в данном случае month), чекбокс в свою очередь отправляет переменную «Прошлый месяц» (в данном случае month_1) в КПИ с отклонением.




Рисунок 5. Переменные чекбокса.

Настройка КПИ с отклонением:
Для настройки отклонения на потребуется функция LAG – это оконная функция, которая позволяет получить доступ к данным из предыдущей строки в том же результирующем наборе. Функция LAG() используется для сравнения значений текущей строки с предыдущей строкой в пределах указанного окна строк. Функция OVER (ORDER BY) - позволяет изменить порядок обработки данных при применении оконной функции.
В данном случае будет рассчитываться показатель среднего чека текущего месяца по отношению среднего чека прошлого месяца в процентном соотношении, потребуется датасет с полями: дата (месяц), выручка, чек.
Для расчета потребуется создать в источнике для Ключевого показателя измерение по месяцам, метрику расчета отклонений и сортировку


Рисунок 6. Источник Ключевого показателя.

Метрика отклонения рассчитывается следующим образом


Рисунок 7. Расчет отклонения.

• Где формула (sum(“Выручка”)/count(DISTINCT(“Чек_код”)) - LAG(sum(“Выручка”)/
count(DISTINCT(“Чек_код”))) OVER (ORDER BY “Номер_месяц”)) нужна для расчета разницы между текущим и прошлым месяцем.
• Формула /LAG(sum(“Выручка”)/count(DISTINCT(“Чек_код”))) OVER (ORDER BY “Номер_месяц”) нужна для процентного соотношения.
• DISTINCT – необходим чтобы исключить повторения чеков (так как например по одному чеку может быть несколько товаров).
• OVER() — ключевое слово, которое определяет, как оконная функция будет применяться к набору данных.
• ORDER BY - определяет, как данные будут упорядочены при применении оконной функции. Этот параметр является ключевым, так как именно в нем определяется порядок строк и, соответственно, отношения между текущей и предыдущей строкой. То есть очень важно правильно подобрать поля для ORDER BY.


Рисунок 8. Расчет отклонения.

К Ключевому показателю подключаем переменную, которую отправляет чекбокс (month_1)


Рисунок 9. Подключение переменной к КПИ с отклонением.

Данная настройка отклонений подойдет для любых случаев, когда требуется сравнение текущего с предыдущим (годы, дни, номера и тд).

Создано в соавторстве с

4 Likes