6 Replies Latest reply on Nov 26, 2013 8:40 AM by Larry Hill

# Calculating a true rolling-12 aggregate

Hi,

I am creating a bar chart that uses a rolling-12 sum for display. I would like to only restrict data for the last 12 months in this chart. However, as soon as I put the date range filter on the extract limiting the data to last 12 months, my rolling-12 numbers no longer "actually" have 12 data points being used in the sum-calculation. However, if the date filter is set to last 24 months, I see a correct calculation on the chart, but then I also have 24 months of bar charts in the display.

Is there a workaround to put a filter that only restricts the view of the data on the charts? Currently, I need to manually "Hide data" for the first 12 months of the chart.

Here is a picture of what I have today :

Here is a picture of what I need the view to look like (without the numbers changing) :

Any help would be highly valuable! Thanks!

Tandra

• ###### 1. Re: Calculating a true rolling-12 aggregate

Tandra,

Please look at the attached workbook.  Sheet 1 allows you to specify an end date, while Sheet 2 will always use the last date in the data set.  For more information on how this works, check out

http://community.tableau.com/message/139603#139603

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 2. Re: Calculating a true rolling-12 aggregate

Solution:

1. Using the last() function, which is relative to its position in the view, you can inject parameters that allow you to dial in the # of months back at each end (min & or max).

2. A calculated field such as - last()>=0 and last()<=11 – can be used as a filter (it returns a True or False value) to restrict the data in the view without actually restricting the data used to create the view – thereby allowing a true “rolling-N” value.
• ###### 3. Re: Calculating a true rolling-12 aggregate

Hi Tandra,

Could you please provide sample worksheet? I would like to explore it.

I would also like to ask few questions :

1. I am confuse where exactly last()>=0 and last()<=11 need to be added?

2. Does it also affect on axis where month is setup?

Thank you.

• ###### 4. Re: Calculating a true rolling-12 aggregate

Tandra,

I am trying to do something similar. Do you have a packaged workbook you can attach so I see how you did it and reverse engineer it?

Thanks,

Larry

• ###### 5. Re: Calculating a true rolling-12 aggregate

Larry:

My suggestion would be to open a new thread/question on the forum, post a packaged workbook (.twbx) file that demonstrates the issue you are having, along with a detailed description/mockup of what you would like to display in Tableau.  Since this question has been "answered", you may get more help by opening a new thread.  A packaged workbook will go a long way toward helping us help you, just as having a packaged workbook here to examine would help you!

• ###### 6. Re: Calculating a true rolling-12 aggregate

Ok..will do