
Re: Calculate expression based on Input box variable
Henric Cronström Apr 21, 2012 6:18 AM (in response to Hemanth Babu+Shekar)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

BaseLineYear.qvw 156.2 K

Re: Calculate expression based on Input box variable
Hemanth Babu+Shekar Apr 23, 2012 11:45 AM (in response to Henric Cronström )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 rollup 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

Category_Roll_Up.xlsx 20.9 K

BaseLineYear.qvw 159.2 K

Re: Calculate expression based on Input box variable
Henric Cronström Apr 23, 2012 5:19 PM (in response to Hemanth Babu+Shekar)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

Inflation.qvw 157.0 K


