topic Re: Sum(A) + Sum(B) vs. Sum(A + B) in QlikView

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

Mon, 17 Feb 2014 17:16:56 GMT

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

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

Colin-Albert — Mon, 17 Feb 2014 17:21:44 GMT

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

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

maxgro — Mon, 17 Feb 2014 17:36:06 GMT

=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.

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

hic — Mon, 17 Feb 2014 17:40:49 GMT

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

Sum(RangeSum(A,B))

HIC

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

Mon, 17 Feb 2014 18:05:22 GMT

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