1 Reply Latest reply on Nov 18, 2016 5:16 AM by Cyrille Thybert

    Numerical and Weighted distribution in retail

    Cyrille Thybert

      Hi,

      I would like to calculate the Numerical and Weighted distribution for a retail company.

       

      In my exemple I have

       

      • 5 POS (point of sales)
      • 5 Products
      • Qty Sold (Quantity sold)
      • over months.

       

      Set of data in the workbook :

             

      DatePOSProductQty Sold
      01/01/16POS_1PRD_1170
      01/01/16POS_1PRD_276
      01/01/16POS_1PRD_499
      01/01/16POS_2PRD_155
      01/01/16POS_2PRD_36,25
      01/01/16POS_2PRD_461,5
      ..............

       

      Numerical distribution :

      %age of presence : Number of POS where the product is sold / Number of total POS

       

      Weighted Distribution :

      Weighted %age of presence : Total sales of POS where the product is sold / Total sales

      (the total sales of the POS not only the product sales)

       

      I need also to show the presence of product over 1 month or 3 months / 6 months back.

       

      The result should be a table like this :

       

         

      2016-012016-02
      WeightedNumericalWeighted 3 month backNumerical 3 month backWeightedNumericalWeighted 3 month backNumerical 3 month back
      PRD_1%%%
      PRD_2%%
      PRD_3%
      PRD_4
      PRD_5

       

      One Exemple :

      the Product PRD_1 is sold in 3 POS on 5

      Numerical = 3/5 = 60%

       

      Those 3 POS have a total Qty sold of 800 on 1000 for all POS

      Weighted : 800/1000 = 80%

       

       

      I've started a dashboard with only the numerical but it doen't work totally if I select more than 1 month.

       

      doing :

      {EXCLUDE  [POS]: sum(if [Qty Sold]>0 then 1 else 0 END)} / {fixed  : countd([POS])}

       

       

      So far, I can't figure out my problem.

      Anyone can help me on that ?

       

      Thank you,

      C.