11 Replies Latest reply on Jan 20, 2017 7:49 AM by James Wilson

# Overlapping date ranges

Hello all. I know this topic has been covered, but I am still having trouble solving my problem so figured I would post the question. I frequently look at Excel data dumps of financial information (sales transaction detail, vendor purchases, etc) and use tableau to sort and manipulate the data. For my work it is important to look at the data on a trailing twelve month (TTM) basis, as well as fiscal year basis. I would like to have a table in tableau that shows a TTM period, along with a normal calendar year. As an example, I would like to see the TTM period ended November 30, 2016, side by side with calendar year 2015 data. Currently I can create a TTM period using a custom date range with start and end parameters and a calculated field. However, when I try to view this with a 2015 period the overlap cuts data out of one period or the other. As a result I have to use two tabs in tableau which makes things more difficult. Additionally I cannot produce a combined export directly out of tableau...I have to generate two reports then vlookup data from one to the other.

The end goal is to have a TTM and calendar year, with overlapping months, side by side in a table. Then I can pull in dimensions such as customer and vendor ID, and measures such as revenue or purchases. Is this possible?

• ###### 1. Re: Overlapping date ranges

James,

This is possible, but you'll have to create two calculations (one for TTY and one for FY) for each metric you'd like to compare. For example, here are the two equations I would create to compare Sales:

TTM

IF DATEDIFF('month',[Date], TODAY()) <= 12  THEN [Sales] END

FY for 2015

IF YEAR([Date]) = YEAR(TODAY())-1 THEN [Sales] END

Then you'll just need to use a discrete month for the Date field on the Columns shelf. For my example I also did a dual axis. It will look like this:

Let me know if you have any questions.

-Wesley

• ###### 2. Re: Overlapping date ranges

Wesley,

Thank you for the reply. I think I have everything set up properly except for the discrete month. BTW I am using a table not graph. FY15 shows the appropriate data. My TTM Nov 16 column is only showing YTD Nov 16, so 11 months. This must be something with the discrete date. Any ideas on how to fix that?

James

• ###### 3. Re: Overlapping date ranges

Yes, the discrete dat will be important for you to get the date range you're looking for. Is this closer to what you want?

• ###### 4. Re: Overlapping date ranges

Yes, that is much closer. When I consolidate the months into years though, I want to be able to see the 97237 in December in both the 2015 and 2016 columns though. Can that be done?

• ###### 5. Re: Overlapping date ranges

And instead of having Sales (FY15) Sales (TTM) I would want to have customer names or part names.

• ###### 6. Re: Overlapping date ranges

Yes, when you consolidate to years it will automatically sum that December amount into both years. Here's an example where I used Category in place of Customer Name:

-Wesley

• ###### 7. Re: Overlapping date ranges

Hmm. Would there be any way to not have to visualize the Sales FY15 and TTM column and have the \$37+215 roll up in one column? In that case you would just have rows of categories going down vertically and the two date columns of 2015 and TTM horizontally. For furniture your 2015 column would have \$198,901 and your TTM column has \$252,456 all in one row?

• ###### 8. Re: Overlapping date ranges

Yep, just move Measure Names to the Columns shelf and remove the Year(Order Date) dimension entirely. It will look like this:

1 of 1 people found this helpful
• ###### 9. Re: Overlapping date ranges

Awesome. This is perfect. Thank you for taking the time. Will be very helpful going forward.

On a side note, do you have any suggestions on how to learn more about tableau and its capabilities? I am just starting with it, but it is obviously powerful so I want to learn as much as possible.

• ###### 10. Re: Overlapping date ranges

James,

Of course Tableau's online training is the easiest answer, but I'd also recommend watching some of the videos from the recent Tableau conference or checkout some of the Tableau blogs out there. Here's a list of blogs I maintain on my site.

• ###### 11. Re: Overlapping date ranges

Sorry for the delay. Thank you for the help with my question and the recommendation on learning.