Numerical & Weighted Distribution

[#1]

Hello!

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?

Sergiovery

[swuehl]

Sergiovery,

your expressions (e.g. in a text box) could look like this (in my understanding of the requirements):

WD:

=sum({<CustomID=p(),ProductID=>} Invoice_Value) / sum({<CustomID=,ProductID=>} Invoice_Value)

ND:

=count( CustomID) / count({<ProductID=>} CustomID)

here I count each purchase (anytime) of Customer, also multiple buys. If you need to count the distinct customer in either the numerator or denominator, please add a DISTINCT qualifier.

Hope this helps,

Stefan

P.S. the set analysis I've used will be problematic if you use this in charts with dimensions and you expect it to regard the dimensions values, e.g. if you create a chart and use ProductID as dimension, the expressions won't be evaluated per ProductID value. You need to select one productID to see the correct values.

[#3]

Hi Swuehl, Thanks for you reply.

I've tried the suggested formula...but all the figures are always 100% whatever i filter on my Customer or Product fileds and at any level of their hyerarchies...

Where am I doing wrong?

Sergiovery

[swuehl]

Hm, not sure what's going on on your side. What do you mean with any level of their hierarchy?

Please have a look at attached sample and check if the results look reasonably.

Regards,

Stefan

[#5]

Swuehl,

finnaly I failed in the adaptation of you suggestion to my problem

I thin it is much easer to send you a piece of my job...as soon as I understand how to attach a file to this discussion (I told you: I'm now to QV ad to the group!). I hope that with it you can have a better clarificaion of my needs.

I do not want to take advantage of your availability, so I will try to do a file very simple and focused on the point.

In the while thanks again

Sergiovery

[swuehl]

You can attach a file using the advanced editor (link on right upper corner of the editor window).

The advanced editor is also used if you modify an existing post.

[#7]

OK Swuehl,

here attached the two files I hope can better explain what I would like to do...

1. XSL the desired output (obviously it is not dynamic)

2. QVW filled with the full hierarchyes of Products/Customers and SalesMan (real codes) and the turnover figures (invented)

Feel free to comment every think you wont.

Thanks in advance for your time, support and availabilty 🙂

Sergiovery

[#8]

Hello, I am new to this technology and am very interested how your project works out. I resolve these same types of business problems regularly but with different tools. Please keep me in the loop as of your experience. If I was stronger with the tool i'd gladly assist.

...In time.

[swuehl]

Sergiovery,

please have a look at attached.

I just used the expressions above with field names adapted.

It looks like the WD number is correct for the samples I checked.

The ND number is much smaller in my calculation. In your table, I think you only take those customer into account with any Fatturato. In your attached sample files, it seems that you have a lot of CodCli that seems to have no Fatturato. Shall these be taken out from the denominator count?

Then, I noticed of course that your excel table shows a cross table with dimension CodProd. As I said above, if you want something like that, this approach will come to its limit since it will not look at the actual dimension value.

If this is really what you need, we must look for a different approach (which should be possible, but not as "simple" as above).

But first, we shall agree on the expressions we need to calculate WD and ND.

Regards,

Stefan

[#10]

Hi  Stefan

I spend yesteday some big minutes in  Sergey issue's  with no solution to calculate the  WD (market share of all the outlets who have sold the product).

Do you think it's possible  to build a chart instead of a text box with

ProductId   as Dimension

WD    as Expression 

For ND  I used   =count(DISTINCT {<Product=P(Product)>} Id )   /  Count(  ALL DISTINCT  Id)

Thanks in advance Stefan if you find a solution to WD in a straigt table

Regards

JJ