0 Replies Latest reply: Apr 4, 2017 10:42 AM by Stijn Cottenie RSS

    Define client position to calculated quartiles

    Stijn Cottenie

      For a simulation analysis, I am trying to calculate the effect of a product price change on the total net turnover.

       

      In the graph below you find the net price level (y-axis) per unit and the sales volume (x-axis). Net price is calculated as the ratio of net turnover ([NTO]) to volume ([VOL]). Each dot represents a separate customer ([CUST]) For the dataset, the first and third quartile net price are calculated and also stored in a variable - see the red and green horizonal line on the chart. The price increase a customer gets depends on its current position to Q1 and Q3. Customers that are below Q1 will get the biggest price increase, while those already above Q3 will get a much smaller increase. The user of the sheet can set this percentage price increase. These inputs are stored in a variable.

       

      I guess that the expression to calculate the effect on net turnover would look like (in the example of customers already above Q3):

       

      sum({$<SETANALYSIS>)}sum([NTO]))*vQ3incr

       

      With 'vQ3incr' the percentage price increase for customers already above Q3, as indicated by the end user.

       

      I am not sure however what SETANALYSIS expression to use. Online I found the following syntax, but this does not work in the sheet:

       

      [CUST]={"=(Sum([NTO])/sum(VOL))>= $(vUpper)"

       

      Here 'vUpper' is the variable storing the Q3 net price.

       

      Any help on this would be greatly appreciated!

       

      2017-04-04 16_19_35-EQS - 2. Price Advisor - Pricing potential (6) _ Werkbladen - Qlik Sense.png