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

[dselgo_eidex]

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.

[Saravanan_Desingh]

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

NULL handling in QlikView

[swuehl]

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.

[dselgo_eidex]

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.

[dselgo_eidex]

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

[swuehl]

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))

[dselgo_eidex]

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.

[swuehl]

Henric talks about the reasoning behind in

Set analysis returns 0, for null records

[hic]

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