Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

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

4 Replies

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.

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.

Employee
Employee

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

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

Community Browser