Multiple aggregation in an expression

[Anonymous]

Hello Experts,

Just a basic stupid question.

I want to subtract one column from another in a pivot table and I'm confused how should I write the expression. I know only 2 ways we can write it which are below.

1-  Sum(ColumnA - ColumnB)

2- Sum(ColumnA)-Sum(ColumnB)

I got 4 columns in total. Year, Month, ColumnA and ColumnB and the problem is that I'm getting slightly different total when I use above expressions. I thought both expressions mentioned above should give me exact same result but its not. Can anyone please help me explaining the basic difference b/w abover expressions?

Many thanks.

[stephencredmond]

Hi,

If you had the following columns:

A  B

1  1

2  2

3  -

-  4

Then:

  Sum(A+B) would be 6.

and

  Sum(A)+Sum(B) Would be 13.

This is because QlikView will ignore the nulls in the individual totals but anything plus null() is null().  So, do you have null values in your data?

Regards,

Stephen

[Anonymous]

Sorry, I forgot to mention that I'm using a pivot table and not getting the same result with with the expressions I mentioned above.

I think I might need to use aggr.

but how can I add 2 columns using aggr in a pivot table?

Any help?

[stephencredmond]

Pretty simple.

Sum(Aggr(ColumnA+ColumnB, Dimension))

Stephen

[Anonymous]

Hi Stephen,

So If I have 2 dimention for example year and month in pivot table so how would the formula gonna look like?

Pivot table

Year Month Sales

should the sales expression look like?

Sum(Aggr(ColumnA+ColumnB, Year,Month))