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.

       

      Thanks,

      Cindy