topic Numerical & Weighted Distribution in QlikView

Numerical & Weighted Distribution

Wed, 30 Nov 2011 17:20:27 GMT

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

Numerical & Weighted Distribution

swuehl — Thu, 01 Dec 2011 00:21:30 GMT

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.

Numerical & Weighted Distribution

Thu, 01 Dec 2011 11:02:11 GMT

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

Re: Numerical & Weighted Distribution

swuehl — Thu, 01 Dec 2011 13:26:15 GMT

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

Re: Numerical & Weighted Distribution

Thu, 01 Dec 2011 16:07:20 GMT

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

Re: Numerical & Weighted Distribution

swuehl — Thu, 01 Dec 2011 16:21:30 GMT

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.

Re: Numerical & Weighted Distribution

Thu, 01 Dec 2011 16:57:49 GMT

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

Re: Numerical & Weighted Distribution

Thu, 01 Dec 2011 16:58:12 GMT

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.

Re: Numerical & Weighted Distribution

swuehl — Thu, 01 Dec 2011 17:25:14 GMT

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

Numerical & Weighted Distribution

Thu, 01 Dec 2011 20:56:39 GMT

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

Re: Numerical & Weighted Distribution

swuehl — Thu, 01 Dec 2011 23:18:46 GMT

Hi Sergiovery and Jean-Jacques,

yes, I think it is possible, maybe using CodProd as dimension and as expressions for WD

=sum( aggr(if(sum(Fatturato), sum(total<CodCli> Fatturato)), CodCli, CodProd ))

/sum(total Fatturato)

and for ND maybe (since I am not sure what the correct expression is, see above)

=count(DISTINCT CodCli ) / Count({<CodCli= {"=sum(Fatturato)>0"}>} total DISTINCT CodCli)

Re: Numerical & Weighted Distribution

Fri, 02 Dec 2011 08:09:36 GMT

Hello Swuehl

I've problem in open both your files...it seams I haven't licence rights.

I'm using QW10 Personal Edition.

I'm doing something wrong?

Sergiovery

Re: Numerical & Weighted Distribution

swuehl — Fri, 02 Dec 2011 08:59:15 GMT

With PE, you can't open my files without losing one of yours recovery attempts. So don't open these files .

Just use the given expressions in your file.

Re: Numerical & Weighted Distribution

Fri, 02 Dec 2011 09:07:47 GMT

OK Swuehl. I'm going to analyze the algorithm you suggest...at the level of my "poor" knowledge 😉

I'll come back to you soon!

As usual...thanks in advance.

Sergiovery

Re: Numerical & Weighted Distribution

Mon, 05 Dec 2011 09:59:53 GMT

Hello Swuehl,

finally, after several hours spend over this issue, I'm back to you... 😞

I'm really not able to exit the "empasse".

I think an easer version of the layout can better explain the method I was trying to approach:

Period Jan-Jun 2011

======================================================================

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!

Sergiovery

Re: Numerical & Weighted Distribution

swuehl — Mon, 05 Dec 2011 22:36:34 GMT

Sergiovery,

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:

WD:

=sum({1} aggr(if(sum({1} Fatturato), sum( {1} total<CodCli> Fatturato)), CodCli, CodProd ))

/sum({1} total Fatturato)

ND:

=count({1}DISTINCT CodCli ) / Count({1<CodCli= {"=sum({1} Fatturato)>0"}>} total DISTINCT CodCli)

=count({1}DISTINCT CodCli ) / Count({1} total DISTINCT CodCli)

If this does not help you too much, I would prefer if you could build another simple sample together with your expected results.

Regards,

Stefan

Numerical & Weighted Distribution

Tue, 06 Dec 2011 08:19:19 GMT

Hi Swuehl

Again thank for you kindly support.

Im going to analize your suggestion and, probably, I'll come back to you attention.

Sergiovery

Re: Numerical & Weighted Distribution

Tue, 06 Dec 2011 14:34:12 GMT

Swuehl,

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.

Sergiovery