Effective (AC) Distribution calculation??

[#1]

Hi All,

Seems like no one has had this question before, but a VERY valuable metric in CPG analysis is effective/AC distribution. I've only seen it calculate automatically in Nielsen/IRI.

Basically the calculation is this:

Total Sales of Stores that Carry Item / Total Sales of All Stores *100 (or show as a percentage)

I'll illustrate below:

Store	Product 1 $Sales	Total Store Sales
1	$100	$1000
2	$50	$200
3	-	$300
4	-	$500
Total	$150	$2000

The product has sales, and therefore has distribution, at stores 1 & 2. Therefore the numerator is $1000+$200 = $1200

For our denominator we use all store sales in this universe. Therefore the denominator is $1000+$200+$300+$500 = $2000

So, our Effective (AC) Distribution is:

$1200/$2000 = 60 (or 60%)

So, the question is, how can I determine a qliqview formula when I want to show the effective distribution for each item:

Product	$Sales	Number of Store Listed	Effective Distribution
Product 1	$150	2	60

Any help would be greatly appreciated!

Accenture couldn't figure it out, hopefully you guys can make me look like a boss over here

[Qrishna]

Here we go.

[Kushal_Chawda]

krishna_2644‌ I think your solution will not work when more store is added. Lets Say Total 4 Stores.

Product 1 has 2 store

Product 2 has 3 store

Product 3 has 4 store

[Kushal_Chawda]

laurapwads‌ it will be some what complex to implement from front end. I suggest to handle at script level.

Please see the attached. I have tested with adding more store to check the distribution is correct.

[Qrishna]

@Kush141087 What will happen when the product 2 has sales in all the stores and product1 sales is greater than sales of product2 but only in store3 and store4  etc etc?

There are many possibilities to work with.

I just did with the data what i have been given.

neither extra added nor removed.

cheers.

[Kushal_Chawda]

have you resolved your issue?

[qlikviewwizard]

Hi laurapwads

If you get the answer, please close the thread. Thank you.