Sum() behaviour with null values

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try this:

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

[Anonymous]

Hi Sunny!

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

Thanks!

[maxgro]

[sunny_talwar]

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

[sunny_talwar]

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

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

[Anonymous]

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

[effinty2112]

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

[Anonymous]

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.

[Anonymous]

Hi,

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

Is there another way to get this done?

Phil