2 Replies Latest reply: Oct 29, 2016 3:34 PM by Joaquin Labado RSS

    RANK Formula

    Joaquin Labado

      Hi,

       

      So I have a dataset with the following dimensions:

       

      MARKET; BRAND; LAB; MOLECULE; SALES TYPE (UNITS OR LOCAL CURRENCY); PERIOD

       

      and the following measures: 1; 2 (based on the sales value for period1 and previous period (2))

       

      I created a Pivot Table in Qlik Sense with the following parameters:

       

      Rows: BRAND

      Columns: SALES TYPE; PERIOD; MEASURES

      Measures: Sum([1]), MS%1

       

      MS%1 (Market Share in period1) is calculated the following way:

      aggr(NODISTINCT {<BRAND, MOLECULE, LAB>} sum([1]),BRAND, PERIOD, [SALES TYPE])/aggr(NODISTINCT {<BRAND, MOLECULE, LAB>} sum([1]),MARKET, PERIOD, [SALES TYPE])

       

      I need to create a RANK measure on the Master Items based on MS%1. Could you please help me to elaborate the formula? I cant get it to work.

       

      Thanks!