Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
melwilson
Contributor II
Contributor II

Calculated Dimension values in a table

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:

MW Fuel.PNG

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

1 Solution

Accepted Solutions
rwunderlich

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)

)

2018-09-13_11-37-02.png

Updated example attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

View solution in original post

3 Replies
rwunderlich

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:

2018-09-12_10-44-49.png

You can add some "blank" expressions to create separator lines . See attached qvw example.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

melwilson
Contributor II
Contributor II
Author

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

rwunderlich

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)

)

2018-09-13_11-37-02.png

Updated example attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

View solution in original post