Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
Pretty simple.
Sum(Aggr(ColumnA+ColumnB, Dimension))
Stephen
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))