Анамнез

За окном разливались яркие осенние краски, а я смотрел на график и морщился, силясь понять, что же в нем мне не нравится. Спустя некоторое время я заключил, что мне остро не хватает прогноза. Была середина месяца и кривая предсказуемо ныряла вниз, добавляя динамике излишней тревожности. Дело за малым - добавить кривую с прогнозом и наслаждаться полнотой картины. Но, как оказалось, заморская BI-платформа имела на этот счет другое мнение...

Дело в том, что в Tableau встроенный прогноз весьма специфичен. Он является своеобразным продолжением фактической кривой. Строится он либо начиная с периода, следующего за последним имеющимся на графике, либо прямо начиная с последнего, при этом заменяя факт. Мне же хотелось видеть прогноз рядом с фактом, чтобы понимать, какая перспектива ждет меня в конце недели, месяца, или квартала. Еще прогноз в Tableau очень трепетно относится к пропущенным значениям, объему выборки и не дружит с табличными вычислениями. Итак, клиническая картина стала ясна, пациенту было необходимо протезирование.

В целях сохранения врачебной тайны, заменим данные пациента на встроенный датасорс Sample - Superstore, который можно найти на стартовой странице при запуске Tableau. Для начала построим простенький дашборд на основе метрики Sales в разных разрезах.

Внизу мы видим линейный график, которому как раз и требуется терапевтическое вмешательство. Данные специально были ограничены 15 июля 2021 года для наглядности. Что ж, приступим.

Лечение

Как известно, для расчета простого линейного прогноза, мы должны поделить значение интересующей нас метрики на количество прошедших дней и умножить на общее количество дней в периоде. Общее количество дней мы могли бы посчитать, присоединив к нашему датасорсу внешний календарь и использовав LOD функции. Но, в этом случае, пропадет интерактивность. Все дело в порядке вычислений в Tableau. LOD функции срабатывают раньше, чем табличные фильтры, поэтому настроить взаимную фильтрацию между визуализациями не получится. Кажется, решить эту проблему можно, посчитав количество дней в разных периодах для каждой даты прямо во внешнем календаре.

Я работаю с Greenplum, который, как известно, построен на базе PostgreSQL, а в PostrgeSQL есть отличная функция generate_series, которая и поможет собрать календарь.

-- Сформируем сам календарь
with calendar as
(select 
calendar_date
from generate_series(
        '2021-07-15'::date - interval '2 year', 
        '2021-07-15'::date, 
        interval '1 day') as calendar_date)
/* Если данные у вас всегда свежие и обновляются регулярно,
то вместо конкретной даты можно указать current_date.
Так вы получите динамически меняющийся календарь, 
который не будет занимать лишнее место. */

/* Внутри основного запроса посчитаем кол-во дней в году,
квартале и месяце. */
select
calendar_date::date
,date_part(
    'day', 
    (date_trunc('year', calendar_date) + interval '1 year') 
    - date_trunc('year', calendar_date)) as days_of_year
,date_part(
    'day', 
    (date_trunc('quarter', calendar_date) + interval '3 month') 
    - date_trunc('quarter', calendar_date)) as days_of_quarter
,date_part(
    'day', 
    (date_trunc('month', calendar_date) + interval '1 month') 
    - date_trunc('month', calendar_date)) as days_of_month 
from calendar

Если вы работаете с другой СУБД, не беда. Похожий функцио��ал есть почти везде. На худой конец, можно слезно попросить собрать нужную вам табличку админа, либо изваять ее руками в Excel. Так же, можно воспользоваться эмулятором PostgreSQL, засунув туда вышеописанный код и скачать результат выполнения запроса в CSV.

Итак, календарь у нас есть. Добавим его в модель данных, сджойнив с основным датасорсом по полю Order Date. Теперь мы знаем, сколько дней длился каждый период, включающий в себя конкретную дату. Но как нам понять, какой период надо использовать, когда мы захотим изменить детализацию графика, например, с месяца на квартал? В этом нам поможет параметр.

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

Далее, на основе параметра, нам необходимо сформировать ось, которая ляжет в основу графика. Именно эта пара из параметра и кастомной оси позволит нам переключать детализацию, отображая прогноз. Создаем меру Date_axis и прописываем в ней следующую формулу:DATETRUNC([date_part], [Order Date]). В качестве первого аргумента указываем наш параметр, а второго - бизнес дату, по которой будем мониторить продажи.

Теперь посчитаем количество фактически прошедших дней и общее количество дней в периоде.

/* Fact_days_count */

DATEDIFF(
    'day',
    /* Берем минимальную дату в текущем периоде и отнимаем 1 день */
    DATETRUNC([date_part], MAX([Order Date])) - 1,
    /* Берем максимальную дату во всем датасорсе */
    MAX({MAX([Order Date])}))

Здесь есть некоторая специфика, на которой стоит остановиться подробнее. Во-первых, чтобы вычитание одного дня правильно работало с детализацией по неделям, нужно установить привычный для нас понедельник в качестве дня начала недели. Для этого идем по следующему пути: Data > Data source > Date properties > Week start и выбираем Monday. Во-вторых, при вычислении правой границы периода, нам нужно избавиться от контекста таблицы, или каких-либо фильтров. Это необходимо для корректного расчета прогноза в случае наличия пропущенных дат. Предположим, что мы строим прогноз для последней недели в датасете (с 12.07.2021 по 18.07.2021) и знаем, что датасет обрезан по 15.07.2021. Но продажи были только 12, 13 и 14 числа, а 15 - пусто. В таком случае, если не снять контекст таблицы, мы получим три дня вместо четырех и искусственно завысим прогноз.

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

/* All_days_count */

/* Сперва сделаем проверку того, что текущий период - последний на графике */
IF WINDOW_MAX(MAX([Order Date]), 0, LAST()) = MAX([Order Date])
/* Затем проверим, попадает ли максимальная дата периода
в границы последнего периода во всем датасорсе. Ведь с учетом фильтрации,
последняя продажа могла быть 10.07, а мы знаем, что последняя неделя
в датасорсе началась 12.07, следовательно, при детализации по неделям
прогноз выводиться не должен, так как мы имеем дело с уже закрытым периодом,
где новых продаж не будет. */
AND DATEADD(
        [date_part],
        1,
        DATETRUNC(
            [date_part], 
            MAX([Order Date]))) > MAX({MAX([Order Date])})
/* Если эти условия соблюдены, то берем общее кол-во дней за соответствующий 
период, используя значения, рассчитанные в календаре. Для недель указываем 7, 
так как это константа и она не меняется. Для дней прогноз не нужен, 
потому что в нашей иерархии дат - это атомарная неделимая единица. */
THEN
    MAX(
        IF [date_part] = 'week'
            THEN 7
        ELSEIF [date_part] = 'month'
            THEN [days_of_month]
        ELSEIF [date_part] = 'quarter'
            THEN [days_of_quarter]
        ELSEIF [date_part] = 'year'
            THEN [days_of_year]
        ELSE 0
        END)
/* Если же начальные условия не соблюдаются, подставляем фактические значения. */
ELSE [Fact_days_count]
END

Что ж, дни посчитаны. Пора построить сам прогноз. Для удобства, создадим простую меру Sales_sum, которую будем использовать в дальнейшем.

/* Forecast */

IF [date_part] != 'day'
    THEN [Sales_sum] / [Fact_days_count] * [All_days_count]
    END

Тут все просто. Если установлена детализация по дням, то ничего не делаем, во всех остальных случаях выводим прогноз. Теперь перейдем к визуализации. Чтобы отобразить прогноз на графике, добавим на полку столбцов нашу меру Date_axis, а на полку строк - меры Forecast и Sales_sum.

Далее в настройках установим для пилюли Sales_sum параметр Dual Axis, чтобы графики объединились. Важно расположить прогноз и факт именно в таком порядке, так как это определяет очередность отображения на графике. В результате, кривая факта будет перекрывать кривую прогноза на всех прошлых периодах. Уберем с графика одну ось Y, а для второй установим в настройках параметр Synchronize Axis, чтобы оси не разбежались. Выведем легенду, отобразим параметр переключения детализации и фильтр дат. Посмотрим, что получилось.

Итак, детализация дат меняется, прогноз строится, не хватает только меток данных. Напишем три отдельных меры: для самого прогноза, для отрицательного и положительного отклонения от предыдущего периода.

/* Forecast_labels */

/* Метки будем отображать только для последнего периода на графике
и только если прогноз больше факта. */
IF WINDOW_MAX(MAX([Date_axis]), 0, LAST()) = MAX([Date_axis])
    AND ROUND([Sales_sum]) < ROUND([Forecast])
THEN [Forecast]
END

/* Forecast_diff - */

/* Пропишем те же условия, что и в предыдущей мере, но добавим к
ним проверку того, что разница с предыдущим периодом отрицательная. 
Для второй меры просто поменяем во второй строчке знак "<" на ">". */
IF WINDOW_MAX(MAX([Date_axis]), 0, LAST()) = MAX([Date_axis])
    AND [Forecast] - LOOKUP([Sales_sum], -1) < 0
    AND ROUND([Forecast]) > ROUND([Sales_sum])
THEN 
    '(' 
    + STR(
          ROUND(
            ([Forecast] - LOOKUP([Sales_sum], -1)) / 
            LOOKUP([Sales_sum], -1) * 100
            , 1)
         )
    + ')'
END

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

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

Реабилитация

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

Выберем Техас, категорию "Technology" и настроим детализацию дат по месяцам. Прогноз построен верно. Продажи в июле были, хоть последняя и была третьего числа.

Теперь посмотрим на продажи в категории "Furniture" в штате Иллинойс. Прогноза нет, так как последняя продажа была в июне, а этот период уже закрыт.

Переключим детализацию дат на недели и отфильтруем данные для сегмента "Consumer" и категории "Technology" в штате Калифорния. Последняя продажа была в среду 14.07 и прогноз на конец недели присутствует.

Взглянем на продажи в той же Калифорнии для сегмента "Corporate" и категории "Office Supplies". Прогноза на конец недели нет, что логично, ведь последняя продажа была в воскресенье на прошлой неделе.

Эпикриз

Подводя итог, можно смело заявить, что пациент жив и пребывает в добром здравии. Цель достигнута и мы имеем перед глазами график, где факт располагается рядом с прогнозом, где мы можем менять детализацию и быстро фильтровать данные, получая нужный срез. Разложив меры на составляющие, можно использовать их для создания карточек KPI, а староверы могут применять описанные расчеты для построения столбчатых диаграмм.

P.S. Подробнее ознакомиться с ~~историей болезни~~ отчетом можно по ссылке. Если кто-то владеет более эффективной техникой лечения подобных недугов, делитесь опытом. Работоспособность мер была оттестирована вдоль и поперек, но если вы вдруг обнаружите баг, или в вашем кейсе прогноз будет работать некорректно, приходите в комментарии, будем разбираться. Ну и, конечно же, будьте здоровы ;)

Линейный прогноз в Tableau. Терапевтический очерк