Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
thisisnita
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

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

have a look at attached sample

Unbenannt.png

hope this helps!

View solution in original post

10 Replies
Anil_Babu_Samineni

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)
tripatirao
Creator II
Creator II

Dear Nita,

Set analysis Date Range

Please check this.You might get some information.

Regards

Tripati

silambarasan
Creator II
Creator II

Try this:


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


note : Suppress null

jyothish8807
Master II
Master II

Hi Nita,

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

Your expression looks good to me.

Regards

KC

Best Regards,
KC
Frank_Hartmann
Master II
Master II

have a look at attached sample

Unbenannt.png

hope this helps!

View solution in original post

silambarasan
Creator II
Creator II

can you share the expected result in table format

thisisnita
Creator
Creator
Author

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
Master II
Master II

Hi Nita,

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

Regards

KC

Best Regards,
KC
silambarasan
Creator II
Creator II

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

This is work for me

I think the below image is your expected result

Capture.PNG