4 Replies Latest reply on May 31, 2016 12:01 PM by vinutha vasanthu

# How to calculate the Rolling Sum for 3 months

My requirement is I need to calculate the Rolling Previous Month from the current month

I could figure out on how on the months we need to fetch based on the filter which is working as expected.

Now i hit the dead end....

• I Need 30,60,90 Sum per LOB. instead of 30,60,90 to be a drop down.
• Also for 60 days its is showing as 2 months(for 11/17/2015 the Rolling Months is October and November)
• I need sum of Oct and Nov to be displayed as one in 60 days.Added to this i need 30,60,90 as bars per LOB on selecting the Date.
• Is there a way to use Month(dimension) as a selection criteria instead of parameter(DateParam).
• Also i am not able to see the  data for current month i.e 30 days

This is the calculation i used.

CASE [TimeFrame]

when 'DateMonth' then if [Months between today and Date]=0 then 'show' end

when 'Last2Months' then if [Months between today and Date]>=0 and [Months between today and Date]<=1 then 'show' end

when 'Last3Months' then if [Months between today and Date]>=0 and [Months between today and Date]<=2 then 'show' end

else 'hide'

end

• ###### 1. Re: How to calculate the Rolling Sum for 3 months

Hi Vinutha,

I've attached a sample. The Rolling Sum is what I expect you are after.

Best,

-V

• ###### 2. Re: How to calculate the Rolling Sum for 3 months

Unfortunately i wasn't able to open as the version available at my end is 9.1.

Thank you so much Vasil I could do the rolling sum as follows

1.Created a date parameter

2.create a calculated field as follows

datediff('month',(datetrunc('month',[Month])),(datetrunc('month',[Date])))

3.Created the filter as follows and used as filter.

IF  [Months between today and Date]=0 then '30Days'

ELSEIF  [Months between today and Date]>=0 and [Months between today and Date]<=1 then '60Days'

ELSEIF [Months between today and Date]>=0 and [Months between today and Date]<=2 then '90Days' end

The above approach worked for me

I would love to see your approach but ...

Thanks,

Vinutha

• ###### 3. Re: How to calculate the Rolling Sum for 3 months

Hi Vinutha,

My approach was similar, only I used a running sum table calculation. I've attached a 9.1 version workbook for your convenience.

Best,

-V

• ###### 4. Re: How to calculate the Rolling Sum for 3 months

Hi Vasil,

My approach was much similar and thanks for re sharing the workbook.I forgot to include the workbook in the earlier response.