6 Replies Latest reply on Jan 12, 2017 7:10 PM by Rahul Nambiar

# How to show rolling 6 months graph in Tableau

Hi,

I have a requirement wherein I have to show a line graph with Months on column and and Sum of sales on rows.

Now the tricky part for me is I want a rolling 6 months calculation. in which if month corresponding to todays date is january then it should show points on the x axis from Jan to the preceding 6 months. i.e Jan 17, Dec 16, Nov 16, Oct 16, Sep 16, Aug 16. and similarly if the date comes to Feb 17 the its Preceding 6 months till Sep 16 and so on.

I tried to get a solution using tableau calculations like Last()-n and so on but I am getting confused with every attempt.

Any help in this respect would be greatly appreciated.

Rahul.

• ###### 1. Re: How to show rolling 6 months graph in Tableau

Use the Calculated Month Field

if DATEDIFF('month',[Order Date],TODAY())<=5 and DATEDIFF('month',[Order Date],TODAY())>=0  then [Order Date] END

instead of your actual Date field. Replace [Order Date] in the formula with your actual Date column

• ###### 2. Re: How to show rolling 6 months graph in Tableau

Hi Rahul,

Do you have Reporting Month (Month - Year) as drop down selection? If yes, is it parameter or filter?

Thanks,

Nikunj

• ###### 3. Re: How to show rolling 6 months graph in Tableau

Hi ,

Can you use the filters Property in Tableau to achieve this .

2 of 2 people found this helpful
• ###### 4. Re: How to show rolling 6 months graph in Tableau

I use this for my Rolling 6 Month logic:

DATETRUNC('month', DATEADD('month',-6, TODAY())) <= [Order Date] AND [Order Date] <= TODAY()

I add to my filter shelf & return only True.

1 of 1 people found this helpful
• ###### 5. Re: How to show rolling 6 months graph in Tableau

Hi Rahul,

I'm interpreting your question that you want to show multiple months and not a single moving average number. If you want a single number then one of the other solutions posted here will work.

We can get a rolling or moving average from a view with Month as a dimension and a measure (like SUM(Sales) here) in a few clicks.

1) I right-clicked on SUM(Sales) and chose Quick Table Calculation->Moving Average.

2) I right-clicked again on the SUM(Sales) pill and choose Edit Table Calculation to bring up the Table Calculation window.

3) I edited it to include the previous 5 values:

Here's the view:

If you want to filter for certain months then you need to make sure the filter is applied *after* the table calculation is computed, the easiest way to do that is to use a calculated field that is a table calc such as LOOKUP(DATETRUNC('month',MIN([Order Date])),0) and then put that on the Filters Shelf.

v10.0 workbook is attached, let me know if you have any questions!

Jonathan

2 of 2 people found this helpful
• ###### 6. Re: How to show rolling 6 months graph in Tableau

Hey Nikunj

Right now in this worksheet I have not kept any filter or parameter Drop down.

Thanks,

Rahul.