3 Replies Latest reply on Jan 13, 2013 5:39 PM by Alex Kerin

Converting mmm-yy to grouped date format

Paul Carysforth Jan 13, 2013 12:42 PM

Wonder if anyone can help?

I have data in an excel sheet that relates to individual stores specifically revenue performance by month from Jan 11 to Dec 12. The column headers for monthly revenue are 'mmm-yy' so Jan-11 etc. The remaining data I have relates to the attributes of the individual stores.

My issue is that in Tableau the individual monthly sales are appearing as individual measures so 24 measures relating to each month. Is there a way of grouping these so I can take advantage of the standard date functionality in Tableau? For example I would like to be able to take advantage of the line graphing visualisations as well as rolling up the revenue for the year for example to compare 2012 to 2011 per store. Am i missing something?

I'm guessing its something basic I'm missing but any help would be appreciated.

Thanks

Paul

1. Re: Converting mmm-yy to grouped date format

Joshua Milligan Jan 13, 2013 1:06 PM (in response to Paul Carysforth)
I think you'll need to write a calculated field to convert the string "Jan-11" into a date.

It would look something like this:

Date(Replace([Date], "-", "-01-"))

That will convert Jan-11 to the date 01/01/2011.

I've attached a packaged workbook to demonstrate. In the future, please attach a workbook, or at least some data (dummy data) in excel format to questions. Thanks!

Just a note, the Replace function will only work with Excel if you are using an extract. Otherwise you'll need to use other string functions (such as Mid) to break apart and reconstruct the string into the date.

Joshua

Date Calculation.twbx.zip 39.9 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Converting mmm-yy to grouped date format

Paul Carysforth Jan 13, 2013 1:16 PM (in response to Joshua Milligan)

Hi Joshua,

Thanks for the quick reply and the guidance.

Will make sure I attach a doc next time.

Cheers
Paul
Like Show 0 Likes(0)

Actions
3. Re: Converting mmm-yy to grouped date format

Alex Kerin Jan 13, 2013 5:39 PM (in response to Paul Carysforth)

It also sounds like you need to shape your data so date is in one column.http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Converting mmm-yy to grouped date format

1. Re: Converting mmm-yy to grouped date format

2. Re: Converting mmm-yy to grouped date format

3. Re: Converting mmm-yy to grouped date format

Actions

More Like This