5 Replies Latest reply on Nov 22, 2016 11:00 AM by Matthew Risley

# Rolling 6 Months

Hey Tableau Family! I need your help!

Attached is a workbook with everything you need. If you need more or a better explanation of the requirement, please ask

I am trying to calculate a score that is %Good Guys - %Bad Guys. However, my new requirement is to have a 6 month rolling calculation for it.

For example, A user wants to see the score for October. So the score should work as follows: Add up all the Good guys, Bad guys, and Number of Records for the months of October, September, August, July, June, and May and calculate the score.

I am currently solving this with a Window_SUM calculation (Window Sum Rolling Score in the workbook):

WINDOW_SUM(SUM([Good Guy]),-5,0)/WINDOW_SUM(sum([Number of Records])-SUM([Unsure of Guy]),-5,0) - WINDOW_SUM(SUM([Bad Guy]),-5,0)/WINDOW_SUM(sum([Number of Records])-SUM([Unsure of Guy]),-5,0)

This calculation works great if you have the data in a table format. But if I want to see October 2016 vs October 2015 it will not work because it's using Window_SUM.

• ###### 1. Re: Rolling 6 Months

Hi Matthew,

Upfront thanks a TON!:)

• ###### 2. Re: Rolling 6 Months

Norbert,

I'm sorry. My internet is buggy. I've seemed to attach two or none at all.

Thanks for the help!

• ###### 3. Re: Rolling 6 Months

Hi Matthew,

Find my approach as reference below and stored in attached workbook version 9.3

If your dataset is "up to date" then replace #1-9-2016# by today()

Be sure "showing missing values" is "checked".

• ###### 4. Re: Rolling 6 Months

Norbert,

Thanks for helping out.

Unfortunately, I am not understanding the solution you gave. I thought about using DateDiff before but could not get it to work.

I am unable to create the numbers in the picture you gave with the numbers in the attached workbook. When i Add "Department store" and "Relationship" survey to filters, I do not get the correct numbers.

With the same filters applied, October 2016 should have a difference (%Good Guy - %Bad Guy) of -2.9% and for September 2016 the difference should be -3.3%.

Is there something to your solution that I am missing? Thanks!

• ###### 5. Re: Rolling 6 Months

EDIT: With Norbert's input on "Show Missing Values" I've gotten the correct numbers in table form. But I now would like to move away from using Window_SUM and create the numbers without a table calculation so i can do YoY comparisons. Perhaps doing something with DateDiff like Norbert has pointed out?

I've update the question at the top of the thread.