topic Re: Weighted Distribution in QlikView

Weighted Distribution

Mon, 18 Mar 2013 13:01:45 GMT

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

Re: Weighted Distribution

Anonymous — Mon, 18 Mar 2013 13:12:11 GMT

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

Re: Weighted Distribution

Gysbert_Wassenaar — Mon, 18 Mar 2013 13:16:30 GMT

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

Re: Weighted Distribution

Mon, 18 Mar 2013 13:23:55 GMT

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

Re: Weighted Distribution

Mon, 18 Mar 2013 13:33:34 GMT

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

Re: Weighted Distribution

Mon, 18 Mar 2013 13:53:35 GMT

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

Re: Weighted Distribution

Mon, 18 Mar 2013 14:04:19 GMT

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

Re: Weighted Distribution

Wed, 20 Mar 2013 11:28:20 GMT

Hi Cindy,

just ask...here it is.

best regards

christian

Re: Weighted Distribution

Wed, 20 Mar 2013 11:39:19 GMT

Excellent! Thank you