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

[Anil_Babu_Samineni]

What values you are expected UI?

[tripatirao]

Dear Nita,

Set analysis Date Range

Please check this.You might get some information.

Regards

Tripati

[silambarasan]

Try this:

rangesum(above(field, 0, rowno()))

note : Suppress null

[jyothish8807]

Hi Nita,

from presentation tab, simply uncheck " Suppress Zero Value" option.

Your expression looks good to me.

Regards

KC

[Frank_Hartmann · Accepted solution]

have a look at attached sample

hope this helps!

[silambarasan]

can you share the expected result in table format

[Anonymous]

Hi Jyothish,

I had unchecked " Suppress Zero Value" option, but it did not work.

Hi Silambarasan,

Unfortunately, your suggestion is also not working.

It is able to detect null values. Once Null value is there, that month is not getting counted, it goes to previous month and provides the result which is inaccurate.

Thanks, if there is any more suggestion for further debug.

Regards,

Nita Prasad

[jyothish8807]

Hi Nita,

Try to fix your sort order in the backed and give it a try.

Regards

KC

[silambarasan]

aggr(RangeSum(Above(sum(R1),0,3)),Date)

This is work for me

I think the below image is your expected result