5 Replies Latest reply on Feb 17, 2017 2:56 AM by Mahfooj Khan

# How to do a rolling calculation based on month selection in filter

Hi,

My data contains 2 items- Revenue and Costs from Oct 2015 to Dec 2016. The user input is the month and year which can be any value between Jan 2016 and Dec 2016.

The value to be shown is a ratio explained below:

Lets say the user selects year as 2016 and month as Dec 2017. Then the ratio for Dec = ( Sum of cost of Dec, Nov and Oct) / (Sum of revenue of Dec, Nov and Oct). This has to be done for all months from January till the selected month, in this case December.

I'm not able to figure this out at all. Workbook is attached. I'm using tableau 10.1.4

• ###### 1. Re: How to do a rolling calculation based on month selection in filter

Hi Naveen,

I've created a date parameter using your Date Num field and put the display format as month, year

Create separate calculated fields to calculate Last 3 Months Cost and Revenue like this

L3M Cost:

IF [KPI]='Cost' AND

DATETRUNC('month',[Date Num])>=DATETRUNC('month',DATEADD('month',-2,[Choose Date]))

THEN [Amount] END

L3M Revenue:

IF [KPI]='Revenue' AND

DATETRUNC('month',[Date Num])>=DATETRUNC('month',DATEADD('month',-2,[Choose Date]))

THEN [Amount] END

To validate the calculation drag the field on canvas and check.

Hope this help. Let us know If you've any query.

Mahfooj

3 of 3 people found this helpful
• ###### 2. Re: How to do a rolling calculation based on month selection in filter

Thanks for the help Mahfooj Khan Unfortunately I'm unable to get the output as you have obtained. It would be great if you could share the tableau workbook for this.

• ###### 3. Re: How to do a rolling calculation based on month selection in filter

Hi Naveen,

Workbook v10.1 attached for your reference. Let us know If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: How to do a rolling calculation based on month selection in filter

Thanks Mahfooj.

• ###### 5. Re: How to do a rolling calculation based on month selection in filter

You're welcome Naveen

Mahfooj