topic Re: Issue with RangeSum in QlikView

Issue with RangeSum

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Issue with RangeSum

Anil_Babu_Samineni — Wed, 19 Oct 2016 13:21:27 GMT

What values you are expected UI?

Re: Issue with RangeSum

tripatirao — Wed, 19 Oct 2016 13:37:18 GMT

Dear Nita,

Set analysis Date Range

Please check this.You might get some information.

Regards

Tripati

Re: Issue with RangeSum

silambarasan — Wed, 19 Oct 2016 13:59:57 GMT

Try this:

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

note : Suppress null

Re: Issue with RangeSum

jyothish8807 — Wed, 19 Oct 2016 14:20:53 GMT

Hi Nita,

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

Your expression looks good to me.

Regards

Re: Issue with RangeSum

Frank_Hartmann — Wed, 19 Oct 2016 14:24:13 GMT

have a look at attached sample

hope this helps!

Re: Issue with RangeSum

silambarasan — Wed, 19 Oct 2016 14:25:34 GMT

can you share the expected result in table format

Re: Issue with RangeSum

Anonymous — Wed, 19 Oct 2016 14:27:51 GMT

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

Re: Issue with RangeSum

jyothish8807 — Wed, 19 Oct 2016 14:31:00 GMT

Hi Nita,

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

Regards

Re: Issue with RangeSum

silambarasan — Wed, 19 Oct 2016 14:49:48 GMT

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

This is work for me

I think the below image is your expected result

Re: Issue with RangeSum

silambarasan — Thu, 20 Oct 2016 13:31:43 GMT

Dim : Date

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