I'm new both with QV(10) and in the group...so please be patient 😉
Question: I would like to build up a report usefull for all the people used to work in FMCG (Fast Moving Consumer Goods) Market. This report is called Numerica & Weighted Distribution Analysis. It gives the idea about your distribution over the market with your product portfolio. So if someone say "this product ha a WD/ND of 75/83" it means that he has listed this product in 75% of the outlets where is developed the 83% of the total (country) value.
Now the question.
My DB has this fileds:
- CustomID
- ProductID
- Invoice_Value
Rule to calculate the WD: anytime a customer (CustomerID), in a fixed timerange, has purchased a specific product (ProductID), I have to add his TOTAL Turnover (not only the turnover developed with the specific product, but the one realized with all the products) to a variable (i.e. vBuyersTO). At the end the value of the variable vBuyersTO has to be divided by the total turnover developed by ALL the customers (including also the ones that haven't purchased the specific product) with ALL the product (idem).
Rule to calculate the ND: anytime a customer (CustomerID), in a fixed timerange, has purchased a specific product (ProductID), I have to count it into a variable (i.e. vBuyersNR). At the end the value of the variable vBuyersNR has to be divided by the total count of the ALL the customers (including also the customers that haven't purchased the specific product).
Does anybody time and willness to help me in doing this exercise?
Prod.s Trea- Trea- Total customer turnover and treated products . ters ND ters TO WD Alfa Beta Gamma Lambda Epsilon Zeta ======================================================================
Total 6 3.180 750 620 832 550 428 0 ====================================================================== A 3 50% 2.010 63% x x x A.a 2 30% 1.582 50% x x A.b 1 10% 832 26% x A.c 2 30% 1.260 40% x x --------------------------------------------------------------------------------------------------------------------------- B 3 50% 1.798 57% x x x Ba. 2 30% 1.370 43% x x B.b 2 30% 1.178 37% x x --------------------------------------------------------------------------------------------------------------------------- C 2 30% 1.452 46% x x ---------------------------------------------------------------------------------------------------------------------------
D 1 10% 550 17% x ---------------------------------------------------------------------------------------------------------------------------
E 3 50% 1.920 60% x x x ---------------------------------------------------------------------------------------------------------------------------
F 5 83% 2.810 100% x x x x x ======================================================================
Explanations:
1. Prod.s = Product hierarchy that could consists of hundreds of product codes...
2. Treaters = Customers that has purchased at least once the specific product. Totally there are 6 treaters (in the selected timeframe, customer Zeta has not put in place any order but it partecipates to the calculation in anycase)
3. ND --> one of the objectives of the report --> Numerical Distribution = Number of Customers that have purchased the product in the selected timeframe over the total customers
4. Treaters TO = Total Turnover of the customers treating the specific product. This value is NOT the turnover developed by the treating customer with the specific product. It is its total value purchased with all the products developed in the indicated timeframe..
5. WD --> the second objective of the report --> Weighted Distribution = total turnover of the customers treating the specific product / Total Company Turnover with all products and all customers boths calculated referring to the specified timeframe.
6. the table include, at this point, the information needed: total turnover per customer (with all the products) and if the customer treates the specific product ("x" in columns).
These are the problem of mine:
1. how totalize the total turnover of a customers if it treats a product (as you can see, product "A" has, in column "Treaters TO" 2'010 because the 3 customers treating that product have a own total turnover of 750+832+428=2'010. This total IS NOT the Turnover developed by the product across the 3 treaters! It may be that the products belonging to Group A (A.a, A.b, A.c) in the selected timeframe could develop a total turnvoer of 350 (clearly different by 2'010)
2. how keep the Customer turnover independent by the Product selection (at line "A.a" the sum is always referring to the Total Turnover of the customer tretating that product --> 750+832=1'582)
I do really hope this can clarify to you the problem and allows you to give me additional support. In any case, thanks a lot for whatever support you can give me!
thank you for the clarification, I see especially ND now clearer.
Have you checked my above expressions in your app? I can replicate your excel file values with these.
I see two potential issues:
- If you are using more than CodProd dimension, maybe for your Product hierarchy, the stated expression will probably look different (you'll need to adapt the aggr() dimensions and maybe also the expression within).
- If you select on Product, but want the results to not change, you will need to disregard this selection, for example, to disregard all selections, thus having constant results in your table, you could use:
Here is the fully detailed calculation process. I hope it is clear enough to understand the concept.
Please note the Database in the sheet "DB".If you would like to set a different timeframe to see what happen...feel free, to modify cells I3, J3 and K3 as you prefer.
To run the filter (I don't know if you have knowledge about Excel mechanisms...sorry if you are familiar with excel!) simply combine Alt + A + Q + Alt+O + Enter.
As usual...thanks for your support. Don't hesitate to ask for additional details.
