Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of calculated Column in Pivot

Dear QV Community,

i ve got a question, that seems simple on first sight, but i couldn't solve it by now, nor find a solution ...

I have a pivot table, that contains 4 columns:

Column1, Column2, Column3, Column4 (=Coulmn3 divided by Column2)

A               2               2               1

B               3               9               3

C               4               16               4

D               2               6               3

Total   11             33          3

The Problem i have is, that the total of column 4 ist calculated, insted of summed up.

That sounds simple, but ...

Any hints are so welcome!! Thanks in advance!!

Chris

7 Replies
MayilVahanan

Hi

Try like this

= Column(3) / Column(2)

Check in both straight/Pivot tables

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Hi Mayil,

thanks for your quick answer. MAybe im to "newbie" to understand yout help.

My Column3 (Expression) has the definition = Column(2) / Column(3) but that applies for the total as well, and i want qlikview to subtotal column4 instead of calculationg the subtotal.

That means in my example i have the total of column (4) that is 3 (33/11). I want it to be 11 (1+3+4+3).

Please explain for someone that is not that familiar.-.. thanks alot!!!!

Chris

MayilVahanan

HI

If you want to calculating the subtotal alone, you can try like

= If(isnull(rowno()) or rowno() = 1,(1+3+4+3), (33/11))

(1+3+4+3) // Expression u want for subtotal

(33/11) // Column(3) / Column(2)

Hope tat helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Hi Mayil,

thanks again. But ...

your suggested formula, where do i put that??? is that a new expression in my pivot table, or where do i have to put it?

Thanks in advance!!

Chris

MayilVahanan

Hi

Can you post a sample file with your expected results?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Gysbert_Wassenaar

Try using a straight table instead and set the Total Mode of Column 4 to Sum of Rows instead of Expression Total.

If you want to use a pivot table you will have to use the original expressions of column 2 and 3 and wrap them in another sum using the aggr function:

sum(aggr( ...expression_column2... / ...expression_column3..., DimensionColumn1)). Replace DimensionColumn1 with the name of the dimension of your pivot table.


talk is cheap, supply exceeds demand
Not applicable
Author


Hi G,

thanks for your reply.

Actually the problem is, that column 2 and 3 each contain aggr ( ... ) functions. so that (i learned) i cannot do a function like aggr ( aggr ( ..., ....),  ....). Or is that somehow possible using variables or something?

same problem with using straight tables, i think ... gonna test that a bit further.

the funny thing is, that if i use a bar chart, and stack column 4 (say colum 1 is "Month" and contains Jan, Jan, Jan, Feb, Feb, Mar, Mar, Mar, Mar, ... and so on) for each Month, i get the right subtotal for each month... but not in the pivot chart

a bit obscure...

thanks for any further advice.

@Mayil, i m gonna try to post some example during the day.

ty for your support.

Chris