Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

waqqas2426
Contributor

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.

4 Replies
stephen-a_redmo
Valued Contributor II

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

waqqas2426
Contributor

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?

stephen-a_redmo
Valued Contributor II

Multiple aggregation in an expression

Pretty simple.

Sum(Aggr(ColumnA+ColumnB, Dimension))

Stephen

waqqas2426
Contributor

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

Community Browser