Update: This has been fixed in 2019.4!
As the data community grows and grows the kind of data the community is using and analysing grows as well. Unfortunately, Tableau currently discriminates against us history geeks, and so I’m here to tell you a trick to get around Tableau’s date limits.
If you’re a researcher, work in heritage, with antiques, or you’re just a history fanatic this might be very helpful for you!
<<Download my dummy data here>>
For the purpose of this tutorial I’m going to take you back to the Kingdom of Falolia. Every year, the Kingdom of Falolia is pillaged by its arch-rival, the Kingdom of Taroni. This annual onslaught saw swathes of villages (especially on the coast) attacked and ravaged by Taroni privateers and mercenaries causing intense damage between 1188 and 1220.
However, from 1208, after the inept rule of King Farsus III, his successor, King Radol decided to embark on a policy of fortifying the kingdom more thoroughly and launching defensive attacks on the Kingdom of Taroni.
Our task as amateur Tableau-historians is to analyse this data set to find out whether King Radol’s policies actually led to a decrease in villages pillaged by the Taronians.
However, when we try to use this data in Tableau we end up with two problems:
1 – we end up with unordered string data that makes it impossible to analyse any patterns.
2 – if we try to turn this into date data, Tableau rejects it and presents it as null.
In order to get round this, we need two calculated fields:
Our first is a STRING+DATE calculation.
Looking at this calculation a in a little bit more detail, step-by-step:
- DATE(DATEPARSE( “MM/yyyy”,
First of all, we’re telling Tableau that we want the end result to be turned into a date. DATE(converts the field into a date) and DATEPARSE “MM/yyyy” (tells Tableau what form the date takes within the field, month and year).
- IF CONTAINS([Year], ‘/11’) THEN REPLACE([Year], ‘/11’, ‘/16’)
Then we’re saying that if the [Year] field says ‘/11’ (the start of our year) then REPLACE that with ‘/16’ (a century that Tableau is OK with).
- ELSEIF CONTAINS([Year], ‘/12’) THEN REPLACE([Year], ‘/12’, ‘/17’)
Then since we’re dealing with two separate centuries, we need to convert our second century to the following century. Hence ‘/12’ is replaced by ‘/17’.
- END))
Now we just want to wrap up both our IF formula and our DATE function.
Our second is a DATE calculation.
Looking at this calculation in a bit more detail, essentially what we’re doing is:
- DATEADD
Essentially, this lets us add or subtract to/from date data.
- ‘year’
Indicates to Tableau that we want our calculation to take place at the year level.
- ‘-500’
This makes sense for my calculation, because I added 500 years in the string calculation, and in this date calculation I need to take those 500 years away again. However, if you added 600/700/800 years in your string calculation then you need to take 600/700/800 years away again.
- [Change to Modern Dates]
Here we are just specifying that the calculation should be modifying our STRING+DATE calculation.
And there you go; you should get something like this:
Looks like we can conclude that King Radol was actually a very good king!
