9 Replies Latest reply: Oct 25, 2011 4:56 AM by Philip Munz

# Calculating in Pivot Table

Hi experts,
I'm rather new to Qlikview and need some hint to solve my problem with pivot tables.
Enclosed you find a picture of my pivot table.
Structure is:

 Budget YEFC Period 01 5 5 02 7 8 03 2 4

I'd like to add a column containing the difference:

 Budget YEFC Delta Period 01 5 5 0 02 7 8 -1 03 2 4 -4

kind regards
Philip Munz

• ###### Calculating in Pivot Table

Hi Philip,

You can get this thru straight table very easliy.

Dimensions: Period

Experssion2  (YEFC): Sum( {<[Year End Forecast]={'Budget'}>} Value)

Expression3 (Delta): Budget-YEFT

Hope this helps,

Kiran.

• ###### Calculating in Pivot Table

Hi Kiran,

I agrree, that a straight talbe would be possible - unfortunatetly this means that my users have limited possibilities analyzing the data.
Has anyone else an idea how to achieve calculated columns (see above) using pivot tables?

kind regards
Philip Munz

• ###### Calculating in Pivot Table

Why not use the same dimensions and expression in pivot table?

Regards,

Kiran.

• ###### Calculating in Pivot Table

Hi Kiran,
in the columns we use an attribute called "data type" that shows which value is shown (YEFC, Budget, Orders, Sales Forecast etc.)

I'd like to give the users the possibility to select e.g. Orders and YEFC but to see the column the Delta (YEFC - Budget) as well. The Problem now is that - with Budget not beeing selected - the Caluculation only shows the YEFC in the Delta ( Calculation YEFC - 0 ).
I use this expression to calculate the delta column - maybe this is wrong:

Sum

( {\$ <Datatype = { "Year End Forecast" }>} VALUE) - Sum ( {\$ <Datatype = { "Budget" }>} VALUE)

kind regards
Philip Munz

• ###### Re: Calculating in Pivot Table

Hi Philip,

The expression you are having for delta is right. Can you share a sample of our document? If not try the below expression:

Sum({\$1<Datatype = { "Year End Forecast" }>} VALUE) - Sum ( {\$1<Datatype = { "Budget" }>} VALUE)

Regards,

Kiran.

• ###### Re: Calculating in Pivot Table

Hi Kiram,

I tried your formular, but it did not work. As far as I understand from the handbook of qlikview the \$1 meens the sum sould use the last selection settings - not the current ones - can you confirm this?

I attached a sample of the data.

kind regards

Philip Munz

• ###### Re: Calculating in Pivot Table

Philip,

Attached the updated document. Let me know if I am missing anything. I suggested \$1 with no knowledge on your data model.

Regards,

Kiran.

• ###### Re: Calculating in Pivot Table

Hi Kiran,

that was just fantasy data...
I see that you created keyfigures for each datatype. I suspect that this is the only way to handle the problem although this means a lot of work for me (about 15 datatypes each with units, values and costs).
Maybe I can achieve it dynamically using variables.

But what I learned is that the attribute "Datatype" can not be part of the pivot-table structure because the keyfigure will always be separated by this attribute.

Thanks a lot for your help
kind regards
Philip Munz

• ###### Calculating in Pivot Table

hi kiran

try this

delta=coloumn(2)- column(3)

may it helps you

tanks

rohit