Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
karolina_
Creator II
Creator II

sum of rows in pivot when using column()

Hi All

I know help says "Pivot table totals are always calculated as expression total."
But maybe someone findout workaround for this so I don't need to change expression:

Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0

to huge sums with aggr?

Each of column is set analysis with variables.

Simple example to play with attached.

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

View solution in original post

10 Replies
Anil_Babu_Samineni

Hello Karolina,

What are you expecting and where we need to work.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
karolina_
Creator II
Creator II
Author

I would like to have total for last column with expression Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  to be SUM OF ROWS not total of expression
I want to keep Column() function, not rewriting it to huge expression

prma7799
Master III
Master III

Please share expected output...

Here i am not understanding about >>> " Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  "

karolina_
Creator II
Creator II
Author

PIVOT.png

prma7799
Master III
Master III

Convert this pivot chart  into straight chart >> Expression > Total Mode >set as Sum

sum.png

karolina_
Creator II
Creator II
Author

Question is about pivot. I know straight table can do this - I even post a screen of it to show needed value.

Frank_Hartmann
Master II
Master II

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

karolina_
Creator II
Creator II
Author

In my org app I use sums with set analysis and variables so such expression would be quite big.

I also display part of this expression in different columns for example Column(3)-Column(4) is column(7).

If sum of rows would be somehow possible in pivot I would use simple Column(14)-Column(11)

if not I will need write expression with all sums and aggrs

Frank_Hartmann
Master II
Master II

i guess you will have to write the expressions with sum and aggr.

As Sunny mentioned in this post, column function is not working in this case!

hope this helps