8 Replies Latest reply: Mar 20, 2013 7:39 AM by Nicholas Child RSS

    Weighted Distribution

      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.