Настройка отклонений через чекбокс и функции LAG.
В данном материале рассматривается настройка отклонений, сравнение выбранного месяца с прошлым месяцем в процентном соотношении.
Рисунок 1. Текущий месяц, процент по отношению к прошлому месяцу.
Для данной реализации потребуется:
-
Датасет с основными данными
-
Отдельный датасет для чекбокса, в котором одно поле это текущий месяц (например 2 и 3 строка это февраль), во втором поле текущий и предыдущий (2 строка это февраль, а 3 строка это предыдущий месяц январь)
Рисунок 2. Пример датасет для чекбокса. -
Чекбокс с включенной настройкой «Все кнопки активны»
-
Фильтр для отправки месяца
-
Виджет который будет выводить данные на дашборд, в данном случае «Ключевой показатель»
Настройка чекбокса:
Для настройки чекбокса, нам потребуется к нему подключить специфичный датасет с двумя измерениями «текущего» (месяц – это текущий) и «прошлого месяца» (прошлый месяц - это прошлый месяц и текущий)
Рисунок 3. Измерения чекбокса.
В настройках чекбокса необходимо выбрать в разделе Измерение «Прошлый месяц», для того чтобы при фильтрации он выводил текущий и предыдущий месяц, также в разделе Настройка виджета надо активировать свич «Все кнопки активны». Данный виджет нужен только для отправки текущего и прошлого месяца, он не нужен пользователю, так что его необходимо скрыть для пользователя.
Рисунок 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. Подключение переменной к КПИ с отклонением.
Данная настройка отклонений подойдет для любых случаев, когда требуется сравнение текущего с предыдущим (годы, дни, номера и тд).
Создано в соавторстве с