9 Replies Latest reply on Jun 28, 2016 1:20 PM by Ron Schultz

Dynamic rolling 3 mo calculation

Hello Friends,

I hope this message finds you well.

I'm a very new to Tableau so I hope what I'm asking for is a simple request!

On the attached, I would like to create a current rolling 3 month calculation based off my "Month" date field. With that rolling 3 month field, I would then like to use this field to compare "Order Value" with the next previous rolling 3 months. So, I would like to compare the current rolling 3 months against the previous rolling 3 months. Is that possible?

For example. in my data set, I have data from April 2015 - March 2016. So my current rolling 3 months would = Jan, Feb. March of 2016.. And the previous rolling 3 months would be Oct, Nov, Dec of 2015. I would like this to be dynamic so when I add another month of data to my Excel sheet, the calculations automatically update.

Here is a quick mock-up:

Thank you everyone for your time!

Cheers,

Mikey

• 1. Re: Dynamic rolling 3 mo calculation

I tried to achieve this with 8.3.

Create two calculated fields.

[Current 3 months]

window_sum(sum([Order Value (USD)]),-2,0)

[Previous 3 months]

window_sum(sum([Order Value (USD)]),-5,-3)

With this window formula, the 3 months period changes automatically.

Thanks,

Shin

8.3 attached.

1 of 1 people found this helpful
• 2. Re: Dynamic rolling 3 mo calculation

Hi Shin,

Thank you so much for your prompt response....much appreciated!

Is it possible to adjust this formula so that if there is a month where there is no value for "Order Value" the formula does not look at the next month?

To clarify, for this example, I only want the "Current 3 Months" filter to looks at Jan - March 2016 regardless if there is no "Order Value" for say Jan 2016. Is that possible?

Cheers,

Mikey

• 3. Re: Dynamic rolling 3 mo calculation

With checking/un-checking "show missing Value", you can show/hide January.

Look at the difference of below two tables, and you can understand what is different.

Thanks,

Shin

1 of 1 people found this helpful
• 4. Re: Dynamic rolling 3 mo calculation

Thank you so much, Shin!

I have one quick question. Ultimately, I do not want to show each month in my viz, I just want to show the values for [Current 3 Month] and [Previous 3 Month]. I tried to just remove the "Month" dimension, however this makes the values for [Current 3 Month] and [Previous 3 Month] incorrect.

here is what I'm ultimately after:

THANK YOU SO MUCH FOR YOUR TIME!

Regards,

Andrew

• 5. Re: Dynamic rolling 3 mo calculation

Unfortunately, because of the feature of this calculation function, that becomes completely different request.

Shin

• 6. Re: Dynamic rolling 3 mo calculation

For this request, you need to create parameter.

It's troublesome, but you need to clear and add date parameter each time the source data is updated.

At this point there is not way to update parameter automatically.

[Current 3 months sum]

sum( if [Date] >= DATEADD('month',-2,[param Date]) and [Date] <= [param Date] then [Value] end )

[previous 3 months sum]

sum( if [Date] >= DATEADD('month',-5,[param Date]) and [Date] <= DATEADD('month',-3,[param Date])  then [Value] end )

Thanks,

Shin

• 7. Re: Dynamic rolling 3 mo calculation

THANK YOU SO MUCH, SHIN!

• 8. Re: Dynamic rolling 3 mo calculation

You are very welcome.

Shin

• 9. Re: Dynamic rolling 3 mo calculation

Hi Shin,

I have a similar question to what you answered for Mikey.  My question is how do you get the first two months of the displayed data to calculate the 3 month rolling sum.  In the attached file I have data back to January 2015, but I am displaying May 2015 to May 2016.  The first 2 months displayed only show on and two month rolling totals.  Can this be managed correctly in the Tableau report or do I need to create the summarized 3 month rolling data in the database?

Ron