topic Re: Weighted Distribution in QlikView

Weighted Distribution

ajitkjain — Thu, 26 Jul 2018 17:55:06 GMT

Hi All,

I have a client requirement to calculate the weighted Distribution of an Product.

Weighted Distribution -Sum of Sales of Stores selling the product by the Total Sales of all Stores.

We have to breakdown the Weighted Distribution on Products but I am struck in the expression for Numerator i.e. Sum of Sales of Stores selling the product.

I am able to write the expression to calculate the weighted Distribution for consolidated Product but when I have to breakdown the same to show the Weighted Distribution for each Product its giving me the Sum of sales of Product not the sum of Sales of Stores selling the product.

Below is the expression for WD for consolidated Products, which is working fine:

Numerator - sum({1<PickLevel = {"Total Store"},$(=$(vSetAnalysisCurrentPeriod)),storeid={$(=chr(39) & concat(distinct storeid,chr(39)&','&chr(39)) & chr(39))}>} $(=$(Sales))))

Denominator - SUM({< PickLevel = {"Total Store"}, >} $(=$(Sales)))

Product	Storeid	Picklevel	Sales	No Prod cat
A	1	Total Stores	10	All Shop
B	2	Total Stores	20	All Shop
C	3	Total Stores	34	Non Shop
A	4	Total Stores	36	All Shop
B	5	Total Stores	65	All Shop
D	6	Total Stores	456	All Shop
A	7	Total Stores	64	Non Shop
D	8	Total Stores	34	All Shop

Can someone help me to write the expression.

Re: Weighted Distribution

sunny_talwar — Thu, 26 Jul 2018 18:41:41 GMT

Not entirely sure I understand, but may be try this

Sum({1<PickLevel = {"Total Store"}, $(=$(vSetAnalysisCurrentPeriod)), storeid = p(storeid)>} $(=$(Sales))))

Re: Weighted Distribution

ajitkjain — Tue, 31 Jul 2018 09:52:02 GMT

Hi Sunny,

Thanks for the quick respond for my query, but is still not fulfilling my query.

Please refer the below sample data:

Product	Store	Sales
A	1	10
A	2	20
A	3	30
B	1	40
B	2	50
C	3	60
C	4	70
D	3	80
D	5	90

Store	Sales
1	1000
2	5000
3	800
4	1500
5	2000
Sum	10300

So now WD for Product A will be (2800/10300)*100 = 66.019%.

I had written the expression as

sum({store =<P({1<prod = {'A'}>}store)>}Sales) - But its not giving me correct output.

Could you please help me to help me what is wrong in this expression or with the new expression.

Re: Weighted Distribution

sunny_talwar — Tue, 31 Jul 2018 15:33:31 GMT

I am not sure I understand... why do you have Sales in both the tables? How do you get 2800 from the data you have provided?

Re: Weighted Distribution

ajitkjain — Wed, 01 Aug 2018 17:40:16 GMT

Second Table contains the Sales of different stores and the first table contains the Sales of Product sold on different stores.

Please rename the column Sales from 2nd Table as "StoreSales"

Let suppose I have to calculate the weighted distribution for Product A

WD of Prod A = Sum of sales of all the stores where product A is being sold / sum of sales of all the stores.

So, in my data Product A is being sold on 3 stores i.e. 1,2,3 so Numerator value consists of the sum of sales of these 3 stores where product A is being sold.

Re: Weighted Distribution

ajitkjain — Tue, 07 Aug 2018 07:53:41 GMT

I found the solution to my problem. Thank you for suggestions.