Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Not entirely sure I understand, but may be try this
Sum({1<PickLevel = {"Total Store"}, $(=$(vSetAnalysisCurrentPeriod)), storeid = p(storeid)>} $(=$(Sales))))
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.
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?
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.
I found the solution to my problem. Thank you for suggestions.