My 5th and final tip of the week looks at the difference between Dateparts and Datetruncs in Tableau.

The first thing to note here is that when you drag a date field on to the view, Tableau is essentially automatically calculating one of two things: datepart or a datetrunc.

So, how does Tableau decide which to use?

When you right-click and drag a date field on to the view, a box will pop up for you to choose what field you’d like to drop into the view. There are two main sections to this list (highlighted in yellow below) and one main differences between them… one section has blue symbols and the other has green.

There are many good blogs to explain the difference between these (including these by Tom Brown, Alex Jones, Anna Noble and Amanda Patist).

I won’t go into it in depth in this post but essentially blue = discrete and green = continuous.

The Tip

In short, this is the difference between Datepart and Datetrunc.

Datepart = discrete

Datetrunc = continuous

So when you select the blue discreet option ‘MONTH(Order Date)’ in the first highlighted group in the example above, Tableau will do the calculation DATEPART(‘month’, [Order Date]).

When you select the green continuous option ‘MONTH(Order Date)’ in the second highlighted group, Tableau will do the calculation DATETRUNC(‘month’, [Order Date]).

Datepart returns an integer denoting the section of the date you have chosen

• Month: numbers 1 – 12
• Year: 2014, 2015, 2016 etc.

Datetrunc returns a date field, with the first date of the chosen section

• Month: 01/mm/yyyy, that is the first of every month and year combination in the date
• Year: 01/01/yyyy, that is the 1st of January of every year in your data