5 Replies Latest reply on Nov 8, 2017 12:09 PM by Tom W

# Multiple date formats in one chart?

Is it possible to force Tableau to use different date formats in the same chart?

Heres what I mean:
I have a dataset that has several thousand rows and each row has a date (1/1/15). I can organize into a bar chart and have tableau group them by year (2014, 2015, 2016, 2017). However, I would like for tableau to use a different grouping for the last bar and have it grab the last 12 months.

So the resulting bar chart would look something like

(2014 - Calendar year) (2015 - Calendar Year) (2016-Calendar Year) (2017-Last 12 months)

Thanks

• ###### 1. Re: Multiple date formats in one chart?

You'll need to create a calculation per year:

I.e. your 2014 calculation would be;

SUM(

IF YEAR([Sale Date]) = 2014 THEN [Sales] END

)

2015 and 2016 calcs would be effectively the same as this, just swap out the year.

Then your 2017 calculation would be more dynamic;

SUM(

IF [Sale Date] >= dateadd('year',-1,today()) THEN [Sales] END

)

Then use the 'Measure Values' and Measure Names to create a chart.

• ###### 2. Re: Multiple date formats in one chart?

Hi Matt,

There's a fairly manual way to solve this unique problem.

IF year([Date]) = 2014 then date('01/01/2014')

ELSEIF year([Date]) = 2015 then date('01/01/2015')

ELSEIF year([Date]) = 2016 then date('01/01/2016')

ELSEIF [Date] >= dateadd('day',TODAY(),-365) then date('01/01/2017')
END

The issue here is that 2016 will 'steal' November and December of 2016. I take it you want to double count these?

• ###### 3. Re: Multiple date formats in one chart?

The only issue here is that you'll lose your continuous date axis.

• ###### 4. Re: Multiple date formats in one chart?

Ok guys let me see if I got this straight.

Method 1 (Tom) is saying create a calculated field for each date? Then chart the fields?

Method 2 (Galen), your method is creating one nested IfElse statement?

Two thoughts:
Wouldn't Method 2 result in a loop?
Losing continuous date is a problem as I intend to have to replicate this often and present in various fashions.

Is the following possible? Let Tableau show me exclusively the last 2 months of 2016, then exclude that data and add to 2017? OR Have two data sources plotted on the same X-Axis.

IDK thanks for the help fellas.

• ###### 5. Re: Multiple date formats in one chart?

Galens IF then won't create a loop. Tableau will evaluate each row for that calculation. As soon as that calculation is true, it will return for that row. If you have a row with a date of December 25 2016, it will evaluate to 2016 in that formula. Not 2016 AND 2017, that isn't possible.

Losing continuous date is a problem as I intend to have to replicate this often and present in various fashions.

I don't know if this really prevents you from replication or presenting differently. You would need to create a new calc for each year when it rolls over.

Is the following possible? Let Tableau show me exclusively the last 2 months of 2016, then exclude that data and add to 2017? OR Have two data sources plotted on the same X-Axis.

You can't double count the data without creating calculations as per my previous method. So you can't represent 2 months of 2016 as 2016 and then also show it as 2017.