Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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:

BudgetYEFC
Period
0155
0278
0324


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

BudgetYEFCDelta
Period
01550
0278-1
0324-4


Could you please give me advise how to achieve this?

kind regards
Philip Munz

1 Solution

Accepted Solutions
Not applicable

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.

9 Replies
Not applicable

Calculating in Pivot Table

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.

Not applicable

Calculating in Pivot Table

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

Not applicable

Calculating in Pivot Table

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

Regards,

Kiran.

Not applicable

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

rohit214
Not applicable

Calculating in Pivot Table

hi kiran

try this

delta=coloumn(2)- column(3)

may it helps you

tanks

rohit

Not applicable

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.

Not applicable

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

Not applicable

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.

Not applicable

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