forums

Use these forums to ask questions and discuss Tableau.

How to convert weird "date" text string to Datetime format?

Posted May 1st, 2008 by Lindmannnn
in Dates & Times

A colleague provided me with some already partially summarized data where the "date" field, representing a fiscal quarter, is formatted as a string in this format: "YYYYqN", where N is the quarter number (1-4). So, quarter 2 in 2007 would be "2007q2". The obective in tableau is to treat this field as a date field in some way so we can use one of the line views. How can we do this?

Add new comment

Comments

On May 1st, 2008 Marc Rueter said:

You can definitely do this directly in Tableau. It can be done via a single calculation, but it is a little easier to follow if you build a couple of fields. From the analysis menu, choose 'Create Calculated Field' to create the following 3 fields (note that you will need to replace [fiscal period] with your current date field:
Year
// this creates a date field in the format mm/dd/yyyy
date("1/1/"+str(left([fiscal period],4)))

Quarter
// this results in a single digit integer
int(right([fiscal period],1))

Date
// this combines the fields above into a single field
dateadd('quarter',[Quarter]-1,[Year])

You will then use the Date field in your analysis - you can hide the Year and Quarter fields if you desire.
reply
On May 1st, 2008 Lindmannnn said:

Groovy. Thank you a lot. I will try it out and get back if this helps with the analysis needed. Funny quirk is that the fiscal periods, which are supposed to be quarters, do not begin every 3 months. some have 12 weeks, some 13 or 11, etc. But is should do the trick. Again, thanks.

reply