Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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,

1 Solution

Accepted Solutions
Master II

have a look at attached sample

hope this helps!

10 Replies

What values you are expected UI?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II

Dear Nita,

Set analysis Date Range

Please check this.You might get some information.

Regards

Tripati

Creator II

Try this:

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

note : Suppress null

Master II

Hi Nita,

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

Your expression looks good to me.

Regards

KC

Best Regards,
KC
Master II

have a look at attached sample

hope this helps!

Creator II

can you share the expected result in table format

Anonymous
Not applicable
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,

Master II

Hi Nita,

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

Regards

KC

Best Regards,
KC
Creator II

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

This is work for me

I think the below image is your expected result

Community Browser