1 Reply Latest reply: Feb 13, 2016 9:45 AM by Gysbert Wassenaar RSS

    What if Analysis/ Percentage Changes of a Row in Field

      Hi,

       

      I am currently building a what-if section for my qlikview dashboard.

       

      As of now I am working with an equation that is a multiplication

      i.e sum(RATE*HOURS) = Revenue.

      I was trying formulas such as sum(RATE*HOURS)*(1+$(Percent Change in Rate or Hours)/100) but as you can see this really doesn't provide an accurate measure of percentage increases and if I have the two percentage change multiplication in the expression a percent change in either one will give the same answer, so is not really providing an accurate what-if analysis

       

      I now want be able to alter RATE or HOURS by percentage changes and then multiply these altered rates for revenue. I have exacerbated all alternatives of manipulating this expression and I have realised to make an accurate and meaningful what- if I must be able to alter the rows of the fields individually first before multiplying them together because:

       

      sum(RATE*HOURS)  does not equal sum(RATE)* sum(HOURS).


      So what I would like to do is build a what-if field for each of these based of some variable/or other method upon which I can toggle percentage changes in the fields and then get a summation based on row level. Attached is a photo of what I would like to be able to do in Qlik and somehow manage to keep Percentage Change a dynamic element in the process.

      WHAT IF.PNG

       

      Any help would be greatly appreciated before I lose all my hair!!!!!

       

      Thanks,

       

      James

       

        • Re: What if Analysis/ Percentage Changes of a Row in Field
          Gysbert Wassenaar

          Create two variables vPercRate and vPercHours and use for example an input box object or two sliders to change the values of these variables. Then use these variables in the expression: Sum(RATE*HOURS*$(vPercRate)*$(vPercHours)).

           

          If you need separate Percent Change values for each individual record then you need to add two Inputfields to the table in the script so you can change the values in these fields on the fly in the UI.