RangeSum of Previous Three Months

[asmithids]

Hello,

I am trying to create a previous three month aggregation of Charges per row.  The issue is when I filter on the current year of 2016, January is not aggregating the previous months of November 2015,December 2015 and January 2016. I am using the RangeSum function with the Above function with the {1} to ignore the filter selection but, the beginning periods are not aggregating correctly.  The goal is to have a previous three month aggregation per row on the straight table regardless of which year I filter on except for the first year where data for the previous months do not exist.

Attached is the app with the current version of the previous three month expression. 

Any help would be very much appreciated. 

Thank you!

[girirajsinh · Accepted solution]

Hi Alec,

I feel that you should use date related function instead of inter-record functions for table. In this case Above().

But anyway, I guess changing your expression as below would get you the desired result. I hope that would help you.

If(Sum(GrossCharges)=0,0, RangeSum( Above( Total Sum({1} GrossCharges ), 0, 3)))

Instead of

If(Sum(GrossCharges)=0,0, RangeSum( Above( Sum({1} GrossCharges ), 0, 3)))

Now result is like,

Cheers,

Giri

[sunny_talwar]

You can also look at this

RangeSum(Above(TOTAL Sum({<Year, ServiceMonth2, Month>} GrossCharges), 0, 3)) * Avg(1)

Note:

1) I am avoiding to use the if statement and multiply by Avg(1) which essentially does the same thing

2) I am not ignoring selections in all field using 1, because you may want charges to change based on your other dimension. For example if you have multiple employees, you may want the chart to show employee wise 3 month accumulation. If you use {1}, your expression won't filter based on any selection including employee. Instead of using {1}, I suggest ignoring only those selections such as Year and ServiceMonth2 where you plan to make a selection.

[asmithids]

Hi Giri,

That worked!  I appreciate the help. 

Take care,

[asmithids]

Hi Sunny,

I like your approach.  I see where I can use it in other situations. 

Thanks again! 

[mayuraam]

Hello Sunny Talwar,

The solution did work. But could you pls explain what exactly Rangesum*avg(1) does exactly or i want to understand its working. I tried to understand its working but was unsuccessful. 

Thank you in advance... -Mayura