Weighted Distribution

[#1]

Hi guys, I'm relatively new to Qlikview and have a little problem I can't solve!

I've had a look at a previous topic which covered this same problem in broken English however it was never resolved...

Numeric distribution referes to the actual number of stores a product is sold in. I have figured that out and got it working fine with the formula below

=count({$<Units={">0"} >} distinct [Store Name]) / count(total [Store Name])

The above expression counts all stores where "Unit Sales" are above 0 and divides it by ALL stores to give a percentage of all stores the product sold in. Easy enough...

However, I am unable to solve the issue when it comes to weighted distribution.

In words: Weighted distribution should take the sum (not count) of ALL sales for a ALL products in a store where each product's sales was greater than 0 and divide this number by the total sales overall.

I've created a summary in xlsx attached to show what I want to achieve, as it is quite hard to define in words. The weighting is to the total Sales of all products.

For what it is worth I want this to be dynamic, in that I have a hierarchy of products as my dimension in a table and would like to be able to dynamically calculate each products weighted distribution. A "text-box" solution isn't an option unfortunately.

Thanks,

Cindy

[Anonymous]

Hello Cindy,

1. add product as your dimension

2. use expression  if (sum(salesamount)>0,(sum(salesamount)/sum(Total salesamount))

what it will do it will chech whether that individual product is grator then 0 , if yes then it will divide that

salesamount by all sales amount like wise it will distribute for all products.

Regards,

anant

[Gysbert_Wassenaar]

try: if(sum(Units)>0,sum({$<Units={">0"} total <[Store Name]> Units)/sum(total Units),0)

[#4]

Unfortunately I just get 100% for every product, which is obviously not correct... I agree with your method/logic though. So frustrating!

[#5]

The problem is we are not getting a total sales for the store, for each Product.

the first part of the expression needs to sum the total sales of all stores that EACH product is in, these stores will change from one product to the next. I think that's the issue but I'm not clever enough to figure out how to recitfy this! hahaha

[#6 · Accepted solution]

Hi Cindy

Try this in your expression

if (sum(Units)>0,
sum(
    aggr(
        sum({<Units={'>0'}>}total <Store> Units), Store, Product
     )
)
/
sum(total Units)
,
0
)

it works on the example file

best regards

Christian

[#7]

Thank you so much! You are my hero... I need to learn what this "aggr" function does, clearly!

[#8]

Hi Cindy,

just ask...here it is.

best regards

christian

[#9]

Excellent! Thank you