Sum(A) + Sum(B) vs. Sum(A + B)

[#1]

We have a table consisting of:

RowId - a unique identifier for each row

A - any real number

B - any real number

There are a couple of ways to sum A and B together:

• Sum(A) + Sum(B)
• Sum(A + B)
• Sum(Aggr(A + B, RowId))

Sum(A + B) is equivalent to Sum(Aggr(A + B, RowId))

When A or B can be NULL, Sum(A + B) is not equivalent to Sum(A) + Sum(B).

Why? Because any calculation involving NULL will yield NULL!

To take care of this, you can alter the field on load:

IF(IsNull(A), 0, A) AS A

Would there be any situations in which you need a metric to be NULL? If so, is there a way to treat NULL as 0 in Sum(A + B)?

[Colin-Albert]

You can use the NullAsValue function to set how QlikView interprets nulls in the load script.

[maxgro]

=alt(null(), 0)

help online

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

[hic · Accepted solution]

If you instead of A+B write RangeSum(A,B), you will treat NULL as zeros. Hence

     Sum(RangeSum(A,B))

HIC

[#5]

Hi Dirk, If the fields are number (either real or intergers) you can use the ALT function.

Alt(A,0) AS A

Alt(B,0) AS B