Calculate expression based on Input box variable

[#1]

Problem - To calculate a value in a pivot table based on user-input via the input box

The expression to be calculated is Inflation per Material between two periods - Actual Year and Baseline Year.

The Actual Year and Baseline Year are provided as inputs via user variables (Input Box).

Inflation must be calculated for only those materials that have Quantity > 0 in Actual Year and Baseline Year.

Table Structure:

Material  Quantity  Year         Spend

123             1           2011           120

124             0            2011           0 

123              1           2012           140

124              1            2012           140          

In the above example, when Actual Year = 2012 and Baseline = 2011, Inflation must only be calculated for Material 123. (Material 124 has Quantity = 0 in 2011)

Question :

1. In the expression for Inflation per material, how do I retrieve only those materials which have Quantity > 0 for baseline year?

Question 2

How do I create an expression for Inflation Per Material for ALL materials that have Qty > 0 in Actual and Baseline years :

Inflation Formula =  (Spend in Actual Year ) - (Spend in Baseline Year ) / (Spend in Baseline Year)

Thanks a lot for your help

[hic · Accepted solution]

First of all, I wouldn't use input boxes. I'd use standard QlikView list boxes displaying fields in logical islands (unconnected fields). See attached file. The objects to the left show what I mean. I have used Sum(if(...)) in my expressions, but you can do the same thing using set analysis.

You could use input boxes and have variables in the expression, if you want.

Further, I would probably also skip the idea with user input altogether - you can calculate the inflation for all combinations of materials and years directly. See the pivot table to the right. And then you do not need other input than standard selections in fields in the data model.

/HIC

[#3]

Thanks a lot for your help Henric. The List Box and Pivot table combination has greatly reduced the complexity of the expression. The challenge now is to be able to roll-up the Material Inflation to the 'Category' level. ( A category contains a collection of materials).  The attached QlikView file displays the Category Inflation. The value however is incorrect.

The logic to calculate inflation at the Category level is provided in the attached Excel spreadsheet ( Pls refer tabs : 'To_Be_Application' and 'Material Weight Logic').

Your help with calculation of the Category Inflation will be extremely useful.

NOTE:  If Material spend for a particular actual year is 0, that material is not considered for calculation of Category Inflation.

Thanks

[hic]

Then I think you should define the yearly diff and last year's spend already in the script. Then the calculation of the inflation gets simpler.

HIC