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.
BaseLineYear.qvw 156.2 K
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.