3 Replies Latest reply: Apr 23, 2012 5:19 PM by Henric Cronström RSS

    Calculate expression based on Input box variable

      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

        • Re: Calculate expression based on Input box variable
          Henric Cronström

          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