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

18 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
  8. Linux Online training in India – Webtrackker Technology is providing the linux online training with 100% placement support. If you are looking for the BEST LINUX & UNIX Training Institute In india or linux online training from india, live project based LINUX & UNIX online training then you can contact to us.

    Python online training in India, RPA Online training in India, Salesforce online training in india, AWS online training in india, Cloud Computing Online Training in India, SAS Online Training in india, Hadoop online training in INDIA, Oracle DBA online training in India, SAP online Training In india, Linux Online training in India








    ReplyDelete
  9. • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online Training Bangalore

    ReplyDelete
  10. This post is much helpful for us. this is really very massive value to all the readers and it will be the only reason for the post to get popular with great authority. If you want to learn Details About Date Fields in Tableau. Here we have the information How to use Date Fields in Tableau

    ReplyDelete
  11. The information which you have provided is very good. It is very useful who is looking for salesforce Online Training

    ReplyDelete

 
biz.