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

# Numerical and Weighted distribution in retail

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 :

 Date POS Product Qty Sold 01/01/16 POS_1 PRD_1 170 01/01/16 POS_1 PRD_2 76 01/01/16 POS_1 PRD_4 99 01/01/16 POS_2 PRD_1 55 01/01/16 POS_2 PRD_3 6,25 01/01/16 POS_2 PRD_4 61,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-01 2016-02 Weighted Numerical Weighted 3 month back Numerical 3 month back Weighted Numerical Weighted 3 month back Numerical 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.