Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
Try like this
= Column(3) / Column(2)
Check in both straight/Pivot tables
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
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
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
Hi
Can you post a sample file with your expected results?
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.
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