4 Replies Latest reply: Jun 24, 2011 10:42 AM by Waqas Saeed

# Multiple aggregation in an expression

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.

• ###### Multiple aggregation in an expression

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

• ###### Multiple aggregation in an expression

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?

• ###### Multiple aggregation in an expression

Pretty simple.

Sum(Aggr(ColumnA+ColumnB, Dimension))

Stephen

• ###### Multiple aggregation in an expression

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