Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Not applicable

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

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

1 Solution

Accepted Solutions
Highlighted

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

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

     Sum(RangeSum(A,B))

HIC

View solution in original post

4 Replies
Highlighted

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

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

Highlighted
MVP
MVP

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

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

Highlighted

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

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

     Sum(RangeSum(A,B))

HIC

View solution in original post

Highlighted
Not applicable

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

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