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

[marcus_sommer]

You will need totals within your expressions, maybe something like this:

sum(total <Store> Sales) / sum(total Sales)

What it is and how it worked see:

What does the TOTAL qualifier do?

Totals in Charts

- Marcus

[#3]

yes, that was my immediate reaction, however what this does is gives all store sales, not just the stores that carry the product.

[Qrishna]

Hi Laura.

I guess you  can be the boss.

And yes , the formulae might be something like this:

Sales : Aggr(Sum(Product1_$Sales),Product)

number of Stores : Count({$<Product1_$Sales = {''}>}Store)

effective dist:

num(Aggr(Sum({$<Product1_$Sales -= {''}>}Total_Store_Sales),Product) / Aggr(Sum(Total_Store_Sales),Product),'##%')

See attached.

Hope that helps you

[#5]

do you mind attaching you QVW file? omg we are so close.

[Qrishna]

i have already attached it.

[#7]

you're right sorry didn't show up in this inbox view.

okay, I get what you did above, only problem is that my data isn't laid out that way so simply.here is an example of data that would be using:

Store	Product	Sales
1	Product 1	4
1	Product 2	5
1	Product 3	6
1	Product 4	7
1	Product 5	8
1	Product 6	9
1	Product 7	10
1	Product 8	11
2	Product 1	1
2	Product 3	2
2	Product 4	3
2	Product 5	4
2	Product 6	5
2	Product 7	6
2	Product 8	7

So, you can see that if i want effective distribution of Product 2 it would be:

Sum of sales for Store 1 (because it is listed there and has sales of 5)

/

Sum of all stores (which i know would just be sum(total Sales))

It's the numerator i'm having trouble with.

[Kushal_Chawda]

Please see the attached

[Qrishna]

hopefully this should do.

[#10]

I totally dig where you guys are going with this. And feel better about my own skills knowing that this actually is very difficult.

Both of you gave me the same result, essentially calculating the share of the product within the total sales:

Product	$Sales	Number of Stores Listed	Effective Distribution
Product1	5	2	6%
Product2	5	1	6%
Product3	8	2	9%
Product4	10	2	11%
Product5	12	2	14%
Product6	14	2	16%
Product7	16	2	18%
Product8	18	2	20%

What i'm looking for is this:

Product	$Sales	Number of Stores Listed	Effective Distribution
Product1	5	2	100%
Product2	5	1	68.1%
Product3	8	2	100%
Product4	10	2	100%
Product5	12	2	100%
Product6	14	2	100%
Product7	16	2	100%

The reason Product 2 has a distribution of 68.1% is this it only has sales in store 1.

The sum of store 1 sales for all products is 60, the sum of all sales is 88. therefore 68/88 = 68.1%

it's a crazy complicated metric, totally get it.