topic Expressions Differences in QlikView

Expressions Differences

Mon, 08 Nov 2010 23:04:49 GMT

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?

Expressions Differences

johnw — Mon, 08 Nov 2010 23:18:31 GMT

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.

Expressions Differences

pover — Mon, 08 Nov 2010 23:23:05 GMT

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.

Expressions Differences

Mon, 08 Nov 2010 23:24:11 GMT

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

Expressions Differences

Mon, 08 Nov 2010 23:47:22 GMT

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.

Expressions Differences

johnw — Mon, 08 Nov 2010 23:54:19 GMT

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.