topic Re: Effective (AC) Distribution calculation?? in QlikView

Effective (AC) Distribution calculation??

Thu, 30 Jul 2015 14:02:22 GMT

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

Re: Effective (AC) Distribution calculation??

marcus_sommer — Thu, 30 Jul 2015 16:28:10 GMT

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

Re: Effective (AC) Distribution calculation??

Thu, 30 Jul 2015 16:33:17 GMT

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

Re: Effective (AC) Distribution calculation??

Qrishna — Thu, 30 Jul 2015 17:18:49 GMT

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

Re: Effective (AC) Distribution calculation??

Thu, 30 Jul 2015 19:28:43 GMT

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

Re: Effective (AC) Distribution calculation??

Qrishna — Thu, 30 Jul 2015 19:32:22 GMT

i have already attached it.

Re: Effective (AC) Distribution calculation??

Thu, 30 Jul 2015 19:47:04 GMT

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.

Re: Effective (AC) Distribution calculation??

Kushal_Chawda — Thu, 30 Jul 2015 20:08:22 GMT

Please see the attached

Re: Effective (AC) Distribution calculation??

Qrishna — Thu, 30 Jul 2015 20:12:29 GMT

hopefully this should do.

Re: Effective (AC) Distribution calculation??

Thu, 30 Jul 2015 20:27:26 GMT

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.

Re: Effective (AC) Distribution calculation??

Qrishna — Thu, 30 Jul 2015 21:06:43 GMT

Here we go.

Re: Effective (AC) Distribution calculation??

Kushal_Chawda — Fri, 31 Jul 2015 07:16:51 GMT

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

Re: Effective (AC) Distribution calculation??

Kushal_Chawda — Fri, 31 Jul 2015 07:21:16 GMT

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.

Re: Effective (AC) Distribution calculation??

Qrishna — Fri, 31 Jul 2015 14:25:04 GMT

@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.

Re: Effective (AC) Distribution calculation??

Kushal_Chawda — Sat, 01 Aug 2015 17:29:04 GMT

have you resolved your issue?

Re: Effective (AC) Distribution calculation??

qlikviewwizard — Sun, 02 Aug 2015 05:07:36 GMT

Hi laurapwads

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