DATEPARSE-ing Tips for Successful Speed Dating with Tableau 8.1

Data

DATEPARSE-ing Tips for Successful Speed Dating with Tableau 8.1

by Carly Capitula
//

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

Global Enablement Practice Lead
Wie steigert man die Nutzerakzeptanz von Tableau Server oder Tableau Online mit Hilfe von Trainer-geführten Workshops Die Tableau-Plattform ist eine Suite innovativer und intuitiver Tools. Als ich 2009 anfing mit Tableau Desktop zu arbeiten, war ich ...
Increasing Tableau Server (or Tableau Online) User Adoption with Instructor-Led Workshops The Tableau platform is a suite of innovative and intuitive tools. When I first started using Tableau Desktop in 2009, I managed to ...

See more from this author →

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

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!