Expressions Differences

[#1]

I would have thought that the following expression:

Sum ( A - B )

would have produced the same results as:

Sum (A) - Sum (B).

But they don't, When A = 600 and B = 0, the first expression produces nothinng while the second expression produces 600. Why?

[johnw]

Is B NULL instead of just 0? Anything minus a null is null. But it looks like sum(B) returns 0 for nulls. So in that case, you'd see 0 for the first expression and 600 for the second expression.

[pover]

Sum (A-B) means QlikView subtracts the values in column B from column A that are found in the same row. This is obvious when you have rows where A or B are null.

Sum(A) - Sum(B) sums all column A values in all the rows first and then subtracts the sum of all column B values in all the rows.

For example:

A B
1 -
2 2

sum(A-B)= 0
sum(A)-sum(B) = 1

Regards.

[#4]

I did an example by myself, and I found the same problem. Could you upload your example?

[#5]

Thanks John.

You are correct the value of the B field is null in the data. I am use to treating null values equivalent to 0 in calculations and never paid that much attention to the data values because of this.

[johnw]

Although it sounds like sum(A)-sum(B) will work fine for you, if you really want to do the subtraction on a row-by-row basis, here's one way to do that:

sum(rangesum(A,-B))

The rangesum() function is just like addition except that it treats nulls as 0.