topic Re: RangeSum() function results in 0 when all the arguments are Null in it in QlikView

RangeSum() function results in 0 when all the arguments are Null in it

pranaview — Sat, 16 Nov 2024 18:59:29 GMT

Hi All,

So, I am using RangeSum() function to get total time taken by each transaction of a group in a process. Now since finally I have to sum up the time taken for each transaction , i am using RangeSum(). So, each argument inside this function is basically Time taken for every transaction. It works absolutely fine until I come across a scenario where there's no dates logged in for any transaction which means the process has not come to that group yet and in such case instead of getting Null or (-), i am getting 0 which i know is a property of RangeSum(), as it treats any non-numeric value as 0. But I am also using RangeAvg() to get the avg time per transaction and i am getting Null in the same scenario.

RangeSum(Null(),Null()) = 0

RangeAvg(Null(),Null()) = -

Since it's about Lead time, Showing 0 gives a wrong impression. I have to show (-) in such cases so please let me know if you guys have any solution to this problem.

Any suggestion or help will be appreciated as this needs to be sorted before presenting to the users.

Thanks!

Pranav

Re: RangeSum() function results in 0 when all the arguments are Null in it

jochem_zw — Thu, 05 Mar 2020 15:41:42 GMT

No idea why this happens but maybe you could use the if statement?
=if(RangeSum(Null(),Null())=0,null())

Re: RangeSum() function results in 0 when all the arguments are Null in it

rwunderlich — Fri, 06 Mar 2020 08:59:33 GMT

You could test if you have any input and return null() if not.

if(count(Dates)>0, RangeSum(...), null())

-Rob

Re: RangeSum() function results in 0 when all the arguments are Null in it

pranaview — Fri, 06 Mar 2020 13:39:49 GMT

Thanks for the suggestion Jochem

So, I can't check the If condition on RangeSum() against 0 because there could be scenario where I have got both Start and End DateTime fields but are exactly same. But I can use the If condition to check whether IsNull(RangeAvg()) = -1 and if it is that means all the arguments inside the Range function are Null() and hence i can simple make RangeSum() value as Null().

Cheers!

Pranav

Re: RangeSum() function results in 0 when all the arguments are Null in it

pranaview — Fri, 06 Mar 2020 13:41:34 GMT

Hi Rob,

Thanks for your response.

I can also do that but i thought simply checking RangeAvg() for Null should solve my issue. Correct me if i'm wrong though.

Regards,

Pranav

Re: RangeSum() function results in 0 when all the arguments are Null in it

Brett_Bleess — Thu, 02 Apr 2020 12:51:11 GMT

It appears things are working as expected per the Help:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/RangeFunctions/rangeavg.htm

The odd thing is the RangeSum does return 0, not sure why the delta there.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/RangeFunctions/rangesum.htm

Regards,
Brett