Скрестите пальцы, у нас релиз…

10 января, 2020

Атрибуты, помогающие пользователям увидеть разницу

Ошибки визуализации, вводящие в заблуждение

7 марта, 2020

Функции для работы с датами в Tableau

Описание работы, сценарии использования и примеры вычислений

Функции для работы с датами в Tableau позволяют настраивать фильтры и сравнивать показатели в текущем и предыдущем периодах. Выбор функций зависит от структуры источников данных и поставленных задач по визуализации. В данной статье я рассмотрю функции дат, наиболее частые сценарии их использования и примеры вычислений с датами.

Мы рассмотрим следующие функции для работы с датами в Tableau:

DATENAME
DATEPARSE
DATE
DATETRUNC
DATEPART
DAY / WEEK / MONTH / QUARTER / YEAR
DATEADD
MAX / MIN
DATEDIFF
MAKEDATE
ISDATE
NOW / TODAY

Некоторые функции требуют указания уровня детализации времени (аргумент date_part): год, квартал, месяц, день, день недели и т.д. Возможные значения аргумента date_part:

‘year’ (4-значный год)
‘quarter’ (от 1 до 4)
‘month’ (от 1 до 12 или строковые значений 'January', 'February' и т.д.)
‘dayofyear’ (порядковый номер дня года: от 1 до 365 или 366)
‘day’ (от 1 до 31)
‘weekday’ (от 1 до 7 или строковые значений 'Sunday', 'Monday' и т.д.)
‘week’ (от 1 до 52)
‘hour’ (от 0 до 23)
‘minute’ (от 0 до 59)
‘second’ (от 0 до 60)

В некоторых функциях (DATEDIFF, DATENAME, DATEPART и DATETRUNC) возможно указание дня начала недели (параметр start_of_week). Если параметр задан, то его значение перепишет значение по умолчанию, указанное на уровне источника данных в настройках Date Properties. Возможные значения: 'monday', 'tuesday' и т.д. Указание данного параметра будет полезным, если: 1) необходимо вернуть детализацию по неделям, отличную от настроек по умолчанию на уровне источника данных; 2) есть непостоянство указания начала недели в настройках устройства, сервера и т.д., и датасет требует частой подмены при обновлении.

В некоторых примерах, приведенных ниже, используется написание даты между символом решетки (#): указание типа Date.

Мы рассмотрим работу функций дат на примере датасета Sample - Superstore. Книга “Month over month performance by subcategory” доступна для скачивания в Tableau Public.

DATENAME

Функция DATENAME(date_part, date, [start_of_week]) возвращает строковое значение даты date, на указанном уровне детализации времени: название месяца, строковое значение года / числа месяца / номера недели (и т.д., в зависимости от значения аргумента date_part). Например, формула DATENAME('month', #2004-04-15#) вернет значение 'April'.

Что на входе в аргументеdate: дата типа Date.

Что на выходе: строковое значение уровня детализации времени (тип String).

Возможные сценарии применения:

- Создание фильтра по дате на уровне названия месяца.
- Создание параметра с названиями месяцев / лет на основе списка значений результата DATENAME().

В примере Month over month performance by subcategory было создано логическое поле Selected Month Filter, в формуле которого сравниваются месяц и год Order Date со значениями строковых параметров Month и Year (yyyy). Данное поле будет использоваться в качестве фильтра дат заказов в период заданного месяца и года.

DATEPARSE

Функция DATEPARSE(format, string) позволяет вернуть дату типа Date из строкового поля (String) с указанием, какие части поля представляют какие уровни детализации времени. По сути, вы создаете карту с помощью символов, которую Tableau может использовать для перевода строки в формат даты. Например, DATEPARSE("dd.MMMM.yyyy", "15.April.2012") вернет дату 15.04.2012 типа Date. Перечень возможных символов для обозначения формата даты указаны в справке Tableau.

Что на входе в аргументе string: строковое поле типа String.

Что на выходе: дата типа Date.

Возможные сценарии применения:

- Комбинирование строковых полей "Число", "Месяц", "Год" в единое поле типа Date.

- Перевод строкового поля с нестандартным форматом даты в поле типа Date.

В нашей книге-примере создано поле Selected Month (Dateparse), преобразующее строковое значение даты в поле типа Date в заданном формате.

DATE

Функция DATE(string) выполняет ту же задачу, что и DATEPARSE (преобразует значение в поле типа Date), но отличается по принципу работы. DATE может преобразовывать не только строчные значения, но и численные значения и даты. При этом Tableau самостоятельно интерпретирует символы даты как уровни детализации времени. Второе отличие DATE от DATEPARSE: DATE возвращает именно тип Date, а DATEPARSE - тип Date & Time.

Что на входе в аргументе string: строковое/целочисленное поле или поле типа Date & Time.

Что на выходе: дата типа Date.

Возможные сценарии применения:

- Случаи, когда источником данных ограничены возможности функции DATEPARSE (например, в случае экстрактов из Access).

- Требуется преобразование из числового формата.

В нашей книге-примере создано поле Selected Month (Date), преобразующее строковое значение даты в дату типа Date без задания формата.

DATETRUNC

Функция DATETRUNC(date_part, date, [start_of_week]) возращает дату и время начала периода, к которому относится дата, указанная в качестве значения аргумента date, на уровне детализации времени date_part. Например, DATETRUNC('quarter', #2004-08-15#) вернет дату и время начала третьего квартала, то есть 2004-07-01 12:00:00 AM. .

Что на входе в аргументе date: дата типа Date.

Что на выходе: дата типа Date.

Возможные сценарии применения:

- Настройка фильтра для закрытого периода.

- Использование параметра для выбора уровня детализации времени.

Рассмотрим на примере второй вариант фильтра дат заказов в период заданного месяца и года - Selected Month - с использованием DATETRUNC. Значение TRUE будет возвращаться в случае совпадения первой даты месяца заказа с месяцем, заданным параметрами месяца и года.

DATEPART

Функция DATEPART(date_part, date, [start_of_week]) возращает целочисленное значение периода, к которому относится дата, указанная в качестве значения аргумента date, на уровне детализации времени date_part. Например, DATEPART('year', #2004-04-15#) вернет целое число 2004 . Когда в качестве date_part указано значение 'weekday', параметр start_of_week игнорируется: Tableau использует фиксированный порядок для нумерации дней недели.

! Важно отметить: когда в качестве date_part задано значение weekday, параметр start_of_week не учитывается, так как Tableau полагается на заданный порядок дней недели. Если необходимо задать иной первый день недели (например, вместо Sunday установить Monday), то следует перейти в настройки Date Properties в меню источника данных.

Что на входе в аргументе date: дата типа Date.

Что на выходе: целое число типа Number (whole).

Возможный сценарий применения:

- Расчет показателей на уровнях периода с помощью LOD.

Отличие DATEPART от DATETRUNC: DATEPART возращает "синее" дискретное (discrete) поле даты, а DATETRUNC - "зеленое" непрерывное (continuous) поле даты.

В примере создано поле Avg. Sales by Weekday с помощью выражения FIXED и функции DATEPART(): средняя сумма заказа по дням недели.

DAY / WEEK / MONTH / QUARTER / YEAR

Функции DAY(date), WEEK(date), MONTH(date), QUARTER(date), YEAR(date), по аналогии с DATEPART(), возращают порядковое целочисленное значение периода, к которому относится дата, указанная в качестве значения аргумента date, на уровне детализации времени функции. Эти функции - короткие версии DATEPART() для разных уровней детализации времени, не требующие отдельного задания аргумента date_part.

Что на входе в аргументе date: дата типа Date.

Что на выходе: целое число типа Number (whole).

Возможные сценарии применения:

- Те же, что и DATEPART.

В примере создано поле Avg. Sales by Month с помощью выражения FIXED и функции MONTH(): средняя сумма заказа по порядковым числам месяцев.

DATEADD

Функция DATEADD(date_part, interval, date) возвращает дату, отличающуюся от даты, заданной аргументом date, на количество лет / месяцев / недель / дней (и т.д., в зависимости от значения аргумента date_part), равное значению аргумента interval. При этом новая дата рассчитывается прибавлением interval к date. Например, DATEADD('month', 3, #2004-04-15#) вернет дату, отличающуюся от 15 апреля 2004 г. на 3 месяца вперед, то есть 15 июля 2004 г (2004-04-15 плюс 3 месяца). Если необходимо вернуть дату из прошлого периода, то в качестве аргумента interval следует указать отрицательное значение.

Что на входе в аргументе date: дата типа Date.

Что на выходе: дата типа Date.

Возможный сценарий применения:

- Сравнение показателей в выбранном месяце с показателями предыдущего месяца.

В нашем примере функция DATEADD возвращает месяц, предшествовавший месяцу, заданному параметрами - Prior Month.

MAX / MIN

Функции MAX(date) и MIN(date) возвращают самую позднюю и самую раннюю даты соответственно. Например, MAX(#2004-01-01# ,#2004-03-01#) вернет дату 2004-03-01 типа Date

Что на входе в аргументе date: поле типа Date.

Что на выходе: дата типа Date.

Возможный сценарий применения:

- Необходимо вернуть максимальную дату в датасете, например, для фильтрации последнего месяца.

В примере поле MAX Order Date возвращает самую позднюю дату в источнике данных.

DATEADD

Что на входе в аргументе date: дата типа Date.

Что на выходе: дата типа Date.

Возможный сценарий применения:

- Сравнение показателей в выбранном месяце с показателями предыдущего месяца.

В нашем примере функция DATEADD возвращает месяц, предшествовавший месяцу, заданному параметрами - Prior Month.

DATEDIFF

Функция DATEDIFF(date_part, date1, date2, [start_of_week]) возвращает разницу уровней детализации времени (date_part) между датой date1 и датой date2. Тип обеих дат - Date. DATEDIFF фактически возвращает целочисленную разницу первых дат (как бы результат DATETRUNC) периодов на заданном уровне date_part, к которым относятся даты date1 и date2. Для упрощения понимания работы функции лучше указывать в качестве аргумента date1 более раннюю дату, чем date2, чтобы целочисленная разница была положительной. Например, DATEDIFF('week', #2020-02-09#, #2020-02-12#, 'monday') вернет целочисленное значение 1, так как при начале недели в понедельник 2020-02-09 (date1) относится к неделе 6 (начинающейся с 3 февраля 2020), а 2020-02-12 (date2) относится к неделе 7 (начинающейся с 10 февраля 2020). А DATEDIFF('week', #2020-02-09#, #2020-02-12#, 'sunday') вернет целочисленное значение 0, так как при начале недели в воскресенье 2020-02-09 (date1) и 2020-02-12 (date2) относятся к неделе 7 (начинающейся с 9 февраля 2020).

Что на входе в аргументах date1 и date2: даты типа Date.

Что на выходе: целочисленное значение типа Integer.

Возможный сценарий применения:

- Настройка фильтра для отображения N-ого количества предыдущих периодов date_part.

В нашем примере поле Prior 3 Month Filter будет иметь значение TRUE для дат заказов, разница между которыми и максимальной датой в датасете не превышает 3 месяца.

MAKEDATE

Функция MAKEDATE(year, month, day) возвращает дату типа Date на основе целочисленных полей года, месяца и числа месяца. Например, MAKEDATE(2004, 4, 15) вернет дату 2004-04-15.

Что на входе в аргументах year, month и day: целочисленные значения типа Number (whole).

Что на выходе: дата типа Date.

Возможный сценарий применения:

- При построении временных рядов, когда разные уровни детализации времени содержатся в разных полях.

В примере поле Shipping Date собрано из трех целочисленных полей.

ISDATE

Функция ISDATE(string) возвращает TRUE, если строковое значение типа string соответствует требованиям Tableau для распознавания даты. Например, ISDATE("April 15, 2004") вернет TRUE.

Что на входе в аргументе string: строковое значение типа String.

Что на выходе: логическое значение TRUE или FALSE.

Возможный сценарий применения:

- При различных форматах указания даты внутри одного поля.

В примере поле Date: T|F проверяет соответствие формату указания дат в строковом поле Selected Month (String).

NOW / TODAY

Функция TODAY() возвращает сегодняшнюю дату, а функция NOW() - настоящие дату и время. Значения, возвращаемые функцией NOW(), зависят от способа соединения с источником данных:

- При неопубликованном и опубликованном соединении Live: время сервера источника данных.

- При неопубликованном соединении Extract: время локальной системы.

- При опубликованном соединении Extract: локальное время Tableau Server Data Engine.