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.

Please find my workbook attached.

Thanks

Vinutha