Issue with RangeSum

[Anonymous]

Hi All,

I am facing an issue with rangesum when we have null values in data.

Issue:

Month	R1	R2
Jan-16	10
Feb-16	10
Mar-16	10	30
Apr-16	-	20
May-16	10	20
Jun-16	10	20
Jul-16	10	30
Aug-16	10	30
Sep-16	-	20
Oct-6	-	10
Nov-16	10	10
Dec-16	-	10

R2 is total of 3 month sum of R1.

So R2 in Mar-16=R1 Jan-16+R1Feb-16+R1Mar-16

                        =10+10+10

                        =30

Which we achieved through applying Rangesum(Above) function. Unfortumately the null functions(- is null value) are not handled properly and we are receing incorrect R2 results.

I am receiving below Data

YearMonth	R1	R2
Jan-16	10
Feb-16	10
Mar-16	10	30
May-16	10	30
Jun-16	10	20
Jul-16	10	30
Aug-16	10	30
Nov-16	10	30

My expression is something like this:

Sum(aggr(rangesum(above( sum({<Yearmonth=>}[R1])),0,3)),$(vCurrentDimForecast), YearMonth))

Please let me know if you need more clarity.

Regards,

Nita Prasad

[silambarasan]

Dim : Date

Exp:  If(RowNo()>=3,Rangesum(above(R1,0,3)))