topic Multiple aggregation in an expression in QlikView

Multiple aggregation in an expression

Anonymous — Fri, 24 Jun 2011 09:32:18 GMT

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

stephencredmond — Fri, 24 Jun 2011 14:10:58 GMT

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

Anonymous — Fri, 24 Jun 2011 14:15:59 GMT

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

stephencredmond — Fri, 24 Jun 2011 14:18:29 GMT

Pretty simple.

Sum(Aggr(ColumnA+ColumnB, Dimension))

Stephen

Multiple aggregation in an expression

Anonymous — Fri, 24 Jun 2011 14:42:50 GMT

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