topic Re: Sum() behaviour with null values in QlikView

Sum() behaviour with null values

Anonymous — Fri, 27 Nov 2015 19:20:45 GMT

Hi all!

Why is it that Sum() returns 0 if the value in that row is actually NULL?

This is (at least) a bit annoying if not wrong in some cases.

For example:

Year	Accidents
2014	1
2015	5
2016	-

Sum(Accidents) for all three Years returns 6 which is correct.

If a User selects year 2016 only, QV will return 0 which is totally incorrect. Its like QV knows, that it will be 0 in all 2016?!

It rather should return NULL...

Does anyone know how to treat this right?

-phil

Re: Sum() behaviour with null values

sunny_talwar — Fri, 27 Nov 2015 19:22:02 GMT

Try this:

If(Sum(Accidents) > 0, Sum(Accidents))

Re: Sum() behaviour with null values

Anonymous — Fri, 27 Nov 2015 20:28:35 GMT

Hi Sunny!

Oh my, simple as that. Tried many much more complicated things....but havent thought about such a nice and easy solution.

Thanks!

Re: Sum() behaviour with null values

maxgro — Fri, 27 Nov 2015 20:35:59 GMT

Re: Sum() behaviour with null values

sunny_talwar — Fri, 27 Nov 2015 20:36:10 GMT

Hahahaha ... I have over-complicated some easy things in the past, so you are not alone there. But I am glad we were able to figure it out.

Best,

Sunny

Re: Sum() behaviour with null values

sunny_talwar — Fri, 27 Nov 2015 20:47:15 GMT

I guess it can be modified to be something like this if there can be negative sums

If(Sum(Accidents) <> 0, Sum(Accidents))

Re: Sum() behaviour with null values

Anonymous — Fri, 27 Nov 2015 20:49:14 GMT

Thx maxgro for the hint! You have to keep that in mind if youre messing around with "if>0"...

Re: Sum() behaviour with null values

effinty2112 — Fri, 27 Nov 2015 23:09:33 GMT

I must defend the honour of QlikView here! It is quite correct. The number of accidents recorded in 2016 is currently zero.

Re: Sum() behaviour with null values

Anonymous — Sat, 28 Nov 2015 06:58:56 GMT

Hi Andrew!

yah, youre not wrong there per se.

But lets assume there are some managers in the company where i work, who are seeing this a little bit different.

Re: Sum() behaviour with null values

Anonymous — Thu, 17 Dec 2015 12:10:27 GMT

Hi,

no im facing some performance problems. As you know "if" is not very performant.

Is there another way to get this done?

Phil

Re: Sum() behaviour with null values

sunny_talwar — Thu, 17 Dec 2015 12:15:23 GMT

You can try this, but not sure how much performance benefit you will get:

Sum({<Year = {"=Sum(Accidents) <> 0"}>}Accidents)

Re: Sum() behaviour with null values

Anonymous — Thu, 17 Dec 2015 12:30:53 GMT

Hi Sunny,

sorry, it also shows "0" instead of "-"

Re: Sum() behaviour with null values

sunny_talwar — Thu, 17 Dec 2015 12:39:29 GMT

You are right. Not sure another way of doing this, except doing this with an if statement