DATEPARSE-ing Tips for Successful Speed Dating with Tableau 8.1
Thanks to another new and improved feature in the Tableau 8.1 release, we no longer have to spend time writing out long tedious calculations to reformat date fields! If you haven’t met the new DATEPARSE function, then let me introduce you.
First, let me point out that DATEPARSE is not automatically in the list of functions in Tableau. You will see the option if you are connected to MySQL, Oracle, PostgreSQL or a Tableau Data Extract. However, If you are connected to a datasource and do not see the function listed (i.e. Excel or a text file), then you will need to simply extract your data. Once you have your connection set, it’s time to jump into the dating pool!
Here’s a sample of how a date may be stored in your datasource:
In previous versions of Tableau, you would have to write out a calculated field with RIGHT/LEFT, counting the spaces to include for the appropriate date parts, etc.
In 8.1 we can use the new DATEPARSE function to write a simple formula as follows:
And when we bring this new field into our view we see:
Wait, that’s not correct! Using “yyyymm” in the formula yields the correct year but you only get January—we’re missing the rest of the data! If we switch the case of “yyyymm” to all caps and try “YYYYMM”:
Still not right since our formula now yields the previous year and December? Ok, deep breath… so we see that like most “dates,” we’re finding this function to be a little sensitive…case sensitive to be exact.
Rewrite the formula that reflects a change in the case for the date parts:
And we will get the correct dates:
So why the discrepancy with letter case in our example of year/month? Well depending on our datasource, Tableau can parse a date field down to seconds. Because there are several date parts to account for, capital “M” should be used to designate “months” and lowercase “m” for “minutes.”
I’ve put together the following cheat sheet for that will lead to a successful DATEPARSE:
DATEPARSE is a fantastic function and will save you time—just be sure to pay attention to the case sensitivity in the formula and you’ll be all set! The “Date Field Symbol Table” is another great resource for ensuring you are using the right letter/case when writing your DATEPARSE formula.
There may be date configurations that have not been included in this list. If you have a different format that you typically use, please provide it in the comments below!