Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Issue with RangeSum

Hi All,

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

Issue:

MonthR1R2
Jan-1610
Feb-1610
Mar-161030
Apr-16-20
May-161020
Jun-161020
Jul-161030
Aug-161030
Sep-16-20
Oct-6-10
Nov-161010
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

YearMonthR1R2
Jan-1610
Feb-1610
Mar-161030
May-161030
Jun-161020
Jul-161030
Aug-161030
Nov-161030

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Re: Issue with RangeSum

have a look at attached sample

Unbenannt.png

hope this helps!

View solution in original post

10 Replies
Highlighted

Re: Issue with RangeSum

What values you are expected UI?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

Re: Issue with RangeSum

Dear Nita,

Set analysis Date Range

Please check this.You might get some information.

Regards

Tripati

Highlighted
Creator II
Creator II

Re: Issue with RangeSum

Try this:


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


note : Suppress null

Highlighted
Master II
Master II

Re: Issue with RangeSum

Hi Nita,

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

Your expression looks good to me.

Regards

KC

Best Regards,
KC
Highlighted
Master II
Master II

Re: Issue with RangeSum

have a look at attached sample

Unbenannt.png

hope this helps!

View solution in original post

Highlighted
Creator II
Creator II

Re: Issue with RangeSum

can you share the expected result in table format

Highlighted
Creator
Creator

Re: Issue with RangeSum

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

Highlighted
Master II
Master II

Re: Issue with RangeSum

Hi Nita,

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

Regards

KC

Best Regards,
KC
Highlighted
Creator II
Creator II

Re: Issue with RangeSum

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

This is work for me

I think the below image is your expected result

Capture.PNG