Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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.

View solution in original post

9 Replies
Not applicable
Author

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
Author

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
Author

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

Regards,

Kiran.

Not applicable
Author

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
Creator III
Creator III

hi kiran

try this

delta=coloumn(2)- column(3)

may it helps you

tanks

rohit

Not applicable
Author

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
Author

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
Author

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
Author

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