Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can anyone help?
I am fairly new to QlikView. I want to create a table like this below similar to what I have in Excel:
I have created a pivot table. Fuel Cost £, Fuel Litres and Kilometres are values in a KPI dimension field. Actual and Budget are calculated expressions with set analysis - e.g. sum({<KPI = {[Fuel Cost £],[Fuel Litres],[Kilometres]},[Actual Budget]= {Actual} >} Amount).
How do I create Fuel Cost Per Litre £ so that it appears below the Fuel Litres row?
Am I doing this wrong - should I be using another type of table ?
Thankyou for any advice.
Mel
Yes, it could work. Enable "Partial Sums" on the chart on the Presentation tab to get Totals. You will then need to modify each expression to use a slightly different formula to calculate the total cell. The Dimensionality() function can be used to detect the Total cell,. Dimensionality()=0 for the total. So for example:
if(dimensionality() > 0
,sum({<KPI={'Fuel Cost'}>}Amount)
,sum({<KPI={'Fuel Cost'}, [Actual Budget]={'Actual'}>}Amount) - sum({<KPI={'Fuel Cost'}, [Actual Budget]={'Budget'}>}Amount)
)
Updated example attached.
-Rob
Here's the approach I would recommend.
1. Pivot Table with single Dimension: [Actual Budget]
2. Separate expression for each line, e.g.; sum({<KPI={'Fuel Cost'}>}Amount)
3. Drag the Dimension column to the upper right horizontal.
4. Drag the expressions to the left vertical.
The result can look like this:
You can add some "blank" expressions to create separator lines . See attached qvw example.
-Rob
Hi, Rob.
Wow, that's really good.
I have a follow-on question if you don't mind - if you want to show the difference between Actual and Budget as a third column would your solution still work ?
Thankyou,
Mel
Yes, it could work. Enable "Partial Sums" on the chart on the Presentation tab to get Totals. You will then need to modify each expression to use a slightly different formula to calculate the total cell. The Dimensionality() function can be used to detect the Total cell,. Dimensionality()=0 for the total. So for example:
if(dimensionality() > 0
,sum({<KPI={'Fuel Cost'}>}Amount)
,sum({<KPI={'Fuel Cost'}, [Actual Budget]={'Actual'}>}Amount) - sum({<KPI={'Fuel Cost'}, [Actual Budget]={'Budget'}>}Amount)
)
Updated example attached.
-Rob