The Data School - Date based Functions in Tableau

Tableau offers a variety of functions based on dates. Understanding the differences is important for working with dates.

DATEPART and DATETRUNC

When you want to aggregate the data to the level of a year, month, week, etc. there are these two standard options, extracting the timeframe with the ‘DATEPART’ or the ‘DATETRUNC’ function.

DATEPART: If we take the monthly DATEPART of the Order Date, we end up with twelve values, one for each month. The year does not matter, the values over the different years are aggregated and then displayed as a single value per month.

DATETRUNC: Here the longer timeframes, like the different years are always included. If we take the monthly DATETRUNC, we don’t end up with twelve values, but with twelve times as many years as we have.

This difference between DATEPART and DATETRUNC on a monthly level is displayed in Fig.1.

Figure 1: Profit displayed over time. In the first visualization, we see the total profit over the years for each month, while in the second viz. we see the profit of each individual month.

Both fields can be changed between discrete and continuous, but the default setting in Tableau is that DATEPART is set to be discrete, while DATETRUNC is set to be continuous.

DAY/WEEK/MONTH/QUARTER/YEAR and DATENAME

These functions work similarly to each other. They transform your Date into another field type. With the DAY, WEEK, etc. functions a certain value behind the date is extracted and returned as an integer.

DAY(#05-27-2022#) = 27

DATENAME instead extracts the name as a string instead and also needs a specifier to understand what to extract.

DATENAME('weekday',#05-27-2022#) = Friday

This can result in numbers that are saved as a string, since day, week, year, etc. have no logical name attached to them. With ‘month’, ‘weekday’ etc. you’ll get a value of ‘May’, ‘Friday’, etc.

DATEPARSE

DATEPARSE is the function to transform a string into a date. Here you need to specify which format the string is in and then you can transform it into a date. This function is helpful when Tableau is not able to pick up the date format automatically, or if you try to change it manually and end up with Null values.

DATEPARSE('yyyy-MM-dd','2022-05-27') = 5/27/2022 12:00:00 AM

The Documentation for what each letter means can be found at Tableau Help, but luckily this is not often needed, since this can get quite annoying.

DATE

The DATE function is similar to DATEPARSE, it should be used when you want to convert a number into a Date

DATE(44706) = 2022-05-27 (Days since the 01.01.1900)

Or it can transform a string into a date type, but you cannot specify further information of the string, unlike with DATEPARSE.

There are a few other date-based functions (DATEADD, DATEDIFF, NOW and TODAY are the most important ones) but they have a straightforward explanation within Tableau Desktop and therefore are not explained further on this blogpost.