topic Calculating in Pivot Table in QlikView

Calculating in Pivot Table

Fri, 21 Oct 2011 09:52:24 GMT

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

Could you please give me advise how to achieve this?

kind regards
Philip Munz

Calculating in Pivot Table

Fri, 21 Oct 2011 10:55:46 GMT

Hi Philip,

You can get this thru straight table very easliy.

Dimensions: Period

Experssion1 (Budget): Sum( {<Datatype={'Budget'}>} Value)

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

Expression3 (Delta): Budget-YEFT

Hope this helps,

Kiran.

Calculating in Pivot Table

Mon, 24 Oct 2011 10:01:06 GMT

Hi Kiran,

thanks for the answer.
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

Mon, 24 Oct 2011 12:18:56 GMT

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

Regards,

Kiran.

Calculating in Pivot Table

Mon, 24 Oct 2011 14:08:37 GMT

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

Calculating in Pivot Table

rohit214 — Mon, 24 Oct 2011 14:56:19 GMT

hi kiran

try this

delta=coloumn(2)- column(3)

may it helps you

tanks

rohit

Re: Calculating in Pivot Table

Tue, 25 Oct 2011 05:13:36 GMT

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

Tue, 25 Oct 2011 07:11:25 GMT

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

Tue, 25 Oct 2011 08:12:46 GMT

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

Tue, 25 Oct 2011 08:56:34 GMT

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