Friday, 16 December 2016

Automatically Parse Dates in any Tableau Version



Dates are just the worst data type you ever have to work with. There are about 1001 different ways to write a date, different databases store them differently. Each software has its little qwerks and that's even before you take into account regional differences. 

Using dates in Tableau has been no exception. Everyone at some point has imported some data with dates in and Tableau hasn't recognised it and refused to play along. So you change the data type to date, hoping it might work and it never does. 

So you have to instead use the date parse function, which is fine but does require a little bit of coding and a little bit of trial and error. Wouldn't it be so much better if there was an automatic way of doing it, what if Tableau just worked with dates like you think it should do.

Wel in 10.2 is does! Tableau have introduced an automatic date parse function. Now if you load in a data set and it doesn't at first recognise it as a function you can try to convert and 10.2 does a much better job at recognising strange formats the success rate is much much higher. 

But what if you don't have 10.2 yet, what if you are on 9.2 for example, what then? Are you stuck with having to write date parse functions yourself? Well of course not. 

We can a little trick to get all the goodness from 10.2 into any version of Tableau that has the date parse function. Watch how I do it below, or read on. 










First, load up your data with the datefield that you want to parse, in this case, its got a day portion, then the day number, the short month, and the full year. So we'd have to do a little bit of work to get that to parse. 










The Next step is to make a duplicate of the original field that we want to parse.





Now here is the interesting bit. See how tableau makes duplicates? that little = sign means it's created a calculated field, if we edit this field we can see this 


It's just a copy of the original. So.... now here comes the magic. 
Go back to your copy and change the data type to date 



Now if we drag that new field onto the sheet we see it now functions as a normal date, we can roll it up and down, filter on it etc etc. But here is the trick, what happens if we now look at the calculation?

Boom! There is the full calculation that Tableau did in order to parse that field into the date format. And as its just a calculation, you can copy and paste it into any Tableau version with the date parse function. 

You need never write a date parse calculation ever again. 



Boom




Matt Francis

Author & Editor

Tableau Zen Master, Social Ambassador, Wrangler of Data, Vizzer of Data

11 comments :

  1. Thanks, nice tip. Though you still will need to write or at least tweak a date parse for some awkward data cases, I've documented one here http://tableautziaimesa.blogspot.co.uk/2017/01/todays-quick-fix.html

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Tableau, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Tableau. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.

    For Free Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete
  3. The great service in this blog and the nice technology is visible in this blog. I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog
    Tableau Online Training|R Programming Online Training|SAS Online Training

    ReplyDelete
  4. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Tableau.
    MaxMunus Offer World Class Virtual Instructor led training on Tableau. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Nitesh Kumar
    MaxMunus
    E-mail: nitesh@maxmunus.com
    Skype id: nitesh_maxmunus
    Ph:(+91) 8553912023
    http://www.maxmunus.com/



    ReplyDelete
  5. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in ALTERYX, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on ALTERYX. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete
  6. Nice blog. Really helpful for learningTableau and keep update on some more tutorials….. I liked your blog.

    ReplyDelete
  7. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your mobile phone every day.

    Start following our signals NOW and gain up to 270% per day.

    ReplyDelete

 
biz.