number of customer who makes 80% of turnover

[fredericmante]

hi all,

i need your help
I would like to display in a text object, the number of customer who makes 80% of turnover.

Thanks in advance for your help

Fred

[Anonymous]

It's a classic ParetoSelect - see APIGuide. There is example wich is 100% your solution.

[#3]

Hi Fred,

here are your macro:

'*************************************************************
'* Pareto 80_20
'*************************************************************
sub Pareto
set f = ActiveDocument.GetField ("Company")
f.ParetoSelect "Sum (Revenue)"
end sub

QV Version 9 will replace it by an ACTION (create a button and select an action: PARETO SELECT).

You also will have the option to choos the percentage (80/20 or 70/30 etc.).

Rainer

[johnw]

ParetoSelect is great if you actually want to SELECT the top 80% and don't mind macros. But if all you want to see is a count, then it isn't really what you want. This comes up often enough that I eventually just sat down and worked out a solution. In the attached example, the expression calculates the number of weeks required to hit a given percentage of sales (specified in input variable Limit). I'm guessing that it could be easily modified to count customers instead. I can give it a shot if it ends up not being so simple. Here's the expression:

1 - sum(aggr(sum(if(Week<=N,Amount))/sum(total Amount)<Limit,N))

[yblake]

Bonsoir Frédéric,

Ci-après un exemple de visiteurs médicaux.Cette expression indique le nombre de VM qui ont effectué 80 % des visites d'officines (dans mes données, 23 visiteurs sur 30).

= count(if(aggr(rangesum(top(count(Visites),1,rowno())) / rangesum(top(count(Visites),1,noofrows())),VM)<.8,VM,null()))

dans ton cas, j'essayerais :

= count(if(aggr(rangesum(top(sum(Sales),1,rowno())) / rangesum(top(sum(Sales),1,noofrows())),IdCustomer)<.8,IdCustomer,null()))

au plaisir

[johnw]

Ah! Yes, that's a better approach than mine, as it doesn't require an extra field and seems more flexible in general. However, it doesn't include the customer that pushed you to or over 80%. And like my expression for Weeks, it just sorts by Customer ID, which likely isn't what you want here.

And unfortunately, I can't figure out how to tell the aggr() or top() functions a sort order. Aggr appears to always sort by the dimensions given.

Anyone else know how to add a sort by sum(Sales) to the expression?

Here's my simplification and fix for getting the customer that pushes you over 80%:

-sum(aggr(rangesum(top(sum(Sales),1,rowno()-1))/rangesum(top(sum(Sales),1,noofrows())),Customer)<Limit)

I've attached an example with that expression.

[fredericmante]

Thank you for your responses. I will try your suggestions and you respond very quickly.

Fred

[#8]

John,

Thank for fir your example, help me solve my pareto analysis. Your comment about sorting, I solved the sort problem by having the dimension sort by, in your example, Sum(Amount)

HTH,

Stephen

[johnw]

As long as you have a dimension to sort by, that should do the trick. But if you JUST want the final number using an aggr() function, I'm not sure how to do the equivalent of sorting the dimension. Still hoping someone comes up with an answer to that part.

Example has been modified to include your solution in the chart, and to demonstrate that we still have a problem with the simple expression.

[#10]

Solved it with a Macro (not my favorite way) but a much cleaner looking presentation to the user without having to show the straight table detail for the user to find the numbers. Look at "GetCell" in the API. It loops through the columns and rows in my straight table. So I use the code below to put the pareto count into a variable that I display in an input box. Below, when the counter reaches 17 it is at the column and row that has the total of the "1's" in the "pareto count".

sub ParetoCount

vCounter = 0

set table = ActiveDocument.GetSheetObject( "CH03" )

for RowIter = 0 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

vCounter = vCounter + 1

if vCounter1 = 17 then

set v = ActiveDocument.Variables("vParetoCount")

v.SetContent cell.Text,true

exit sub

else

end if

next

next

end sub