DATEPARSE-ing Tips for Successful Speed Dating with Tableau 8.1

Data

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!

More About the Author

Carly Capitula

Principal / Enablement Practice Lead
A Quick Recap of the 2017 DRIVE/ Conference Last week, some of the InterWorks team dropped by Seattle, WA for DRIVE/ 2017 – a conference for professionals in fundraising and ...
Tableau Viz Showcase by Industry: K-12 Education “Data-driving decision making” is a phrase heard in several industries, perhaps none more so than in education. In such a scrutinized ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK