Quick guide: ISO 8601 dates in Tableau
How do you read 01/05/2019? Do you read it as the first of May 2019? Or the fifth of January 2019? If you’re an international organisation, you might have two different people reading the same date two different ways. Awkward!
What is an ISO date then? ISO is the International Organisation for Standardisation. They created the ISO calendar to stop confusion when talking about dates internationally. Instead ISO writes dates as YYYY-MM-DD (2019-05-01 is the first of May 2019).
ISO 8601 also labels weeks differently too. The first week in a year is where you’ll usually notice the biggest difference to the standard week numbering of the Gregorian calendar. ISO week 01 of the year starts when the week has a majority (4 or more) of its days in January. ISO weeks also always start on a Monday.
In 2016 for example the 1st, 2nd and 3rd of January were in week 53 of 2015 in the ISO calendar. Not week 1 of 2016 as would be in the Gregorian.
Tableau Desktop 2018.2 updated to include ISO 8601 dates and calendar. Why is that important? Well if you work in an industry or business that uses International Standard dates, this update will save you having to write some really tricky date calculations in Tableau.
So how can we use ISO formats for our dates in Tableau Desktop? To start you’re going to have to check the locale of your computer. Only European locales are supported. Then you’ve got a few options:
Data source date properties
Always want your dates in your data source to be in ISO format? Then you’ll want to change that at the data source level. Simply right-click your data source in the data pane and select date properties.
You’ll also want to make sure your week start day is set to Monday too.
Then you’ll want to change the date format option from automatic to custom and read on below.
Formatting date fields
You can edit the format of the date field in the data date properties box (as above). This will then be the default format of all dates in the data source. If you’d rather not have all dates formatted as ISO in your data source, then you can select the date fields individually from the data pane instead.
The [Y] tells Tableau you want to use the ISO calendar. If you wanted to use the Gregorian simply change it to [y]. Gregorian is also the default format in Tableau still.
Date calculations and syntax
Tableau has a lot of really useful date functions you can use in your calculations. Now you also have some new syntax you can use in these functions to specify the results are displayed as ISO dates.
Most of Tableau’s date functions have syntax for defining the date_part. Here’s an example of DATENAME in operation:
Here we’re asking Tableau to return to ISO week of our Order Date field. We’re specifying ‘iso-week’ in the calculation syntax.
As we’ve specified we want the ISO week returned, you can see the results (in the third column) are quite different from the Gregorian week number (in the second column).
Here’s a list of the Tableau date_part syntax, including the new ISO date parts.
Now with ISO dates in Tableau you can make sure your organisation is always on the same page.