topic Re: Why does Sum() and Count() never equate to NULL? in QlikView

Why does Sum() and Count() never equate to NULL?

dselgo_eidex — Mon, 15 Feb 2016 15:44:23 GMT

According to the Help Guide (in the Aggregation Functions section):

"If no value is found, NULL is returned for all Aggregation Functions, except Sum and Count which both return 0."

I'm just curious if anyone knows the reasoning behind always making Sum and Count 0. I can think of numerous ways that it would be beneficial to let them equate to null.

Re: Why does Sum() and Count() never equate to NULL?

Saravanan_Desingh — Mon, 15 Feb 2016 15:57:11 GMT

Please look into the below link for various ways of handling NULL.

NULL handling in QlikView

Re: Why does Sum() and Count() never equate to NULL?

swuehl — Mon, 15 Feb 2016 16:18:29 GMT

A Count() returning zero when no value is found seems quite ok to me. Can you tell one of the scenarios you want NULL instead?

Sum() might be a different case, one can definitely find arguments pro returning NULL when no values found.

Re: Why does Sum() and Count() never equate to NULL?

dselgo_eidex — Mon, 15 Feb 2016 17:58:37 GMT

I agree that it makes sense for Count(), but if I wanted to do a sum of values that can either be >0, 0 (meaning that this is the actual value), or NULL (record is missing), then I would want the sum to be NULL if all of the values were NULL. That way I could tell that there isn't a value for that field.

Re: Why does Sum() and Count() never equate to NULL?

dselgo_eidex — Mon, 15 Feb 2016 18:02:18 GMT

I have read those articles before, but I wasn't able to find a reason for sum to always equate to 0.

Re: Why does Sum() and Count() never equate to NULL?

swuehl — Mon, 15 Feb 2016 18:45:30 GMT

I see your point regarding Sum().

As a workaround, you can try something like

=If(NumericCount(FIELD), Sum(FIELD), NULL())

As a shortcut, you can create a variable in the script:

SET MySum = If(NumericCount($1), Sum($1), NULL() );

Then you can call it in any expression like

=$(MySum(FIELD))

Re: Why does Sum() and Count() never equate to NULL?

dselgo_eidex — Mon, 15 Feb 2016 19:30:35 GMT

Good advice! I've been doing a similar workaround in my expressions, but I'd still like to know what the thought process behind forcing zero values is.

Re: Why does Sum() and Count() never equate to NULL?

swuehl — Sat, 20 Feb 2016 23:24:54 GMT

Henric talks about the reasoning behind in

Set analysis returns 0, for null records

Re: Why does Sum() and Count() never equate to NULL?

hic — Mon, 22 Feb 2016 10:28:49 GMT

I suggest you look at Scales of Measurement.

The Sum() function should only be used for Ratios/Amounts, and for these a zero is equal to an absence. Hence, it is more logical to have Sum() return a zero than a NULL.

HIC