Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
Why not use the same dimensions and expression in pivot table?
Regards,
Kiran.
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
hi kiran
try this
delta=coloumn(2)- column(3)
may it helps you
tanks
rohit
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.
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
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.
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