7 Replies Latest reply: Oct 22, 2013 2:55 AM by Christian Germeroth

# 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

• ###### Re: Sum of calculated Column in Pivot

Hi

Try like this

= Column(3) / Column(2)

Check in both straight/Pivot tables

• ###### Re: Sum of calculated Column in Pivot

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

• ###### Re: Sum of calculated Column in Pivot

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

• ###### Re: Sum of calculated Column in Pivot

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?

Chris

• ###### Re: Sum of calculated Column in Pivot

Hi

Can you post a sample file with your expected results?

• ###### Re: Sum of calculated Column in Pivot

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.

• ###### Re: Sum of calculated Column in Pivot

Hi G,

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