Likely Average

[Salesman0Gil]

Here is a strange question,

I have a field staff each with 20 pieces of equipment. They only need so many to use for each customer each month.

For instance this is

Customer / #of pieces of equipment needed for that customer

A/ 0

B/ 3

C/ 3

D/ 4

E/ 5

F/ 5

G/ 6

H/ 6

I/ 6

J/ 7

K/ 16

In this example he could take care of almost 90% of his customers with only 7 pieces of equipment, and if need be he could borrow the other 9 from someone else for that last customer.

Is there a way for File maker to calculate this???

I was thinking average, but that did not work out right.

Any Ideas?:

Salesman

[Oldfogey]

Yes and no. First you need to work out just which calculation you want.

In your example, the average is 5.5 not 7. Do you really want the average? Averages are nasty.

Do you want to achieve a 90% (or 80%, or ...) hit rate?

Do you need to balance the benefit of fewer pieces of equipment against the cost of borrowing?

There are many other choices.

[Salesman0Gil]

I've been working on it for a while now, and the closest number I can get is (the average of (adding the standard dev. to the avearage)and (subtracting the standard dev. from the max) It is amazing how close that number comes. It is usually only 1 or 2 off.

The greatest problem is the variablity of the factors. What if I made a calculation that threw out the highest number and the lowest number, and then took the average....

My goall is about 90% I want this person to be able to do about 90% of there work with the equipmewnt they have, and then borrow the other 10%.

Salesman

[Charles Delfs]

keep it simple:

sort your list on the # of pieces then if you want 80% hits go to the 80th % record, that is go to the record count * .80 'th record and that is you number.

Does that makes sense, if not I can explain further.

Charles

[Salesman0Gil]

That would be great but the file contains the whole field staff. All this work is done in a sub-summary. Then to veiw it GetSummary

[Oldfogey]

If you sort by customer within staff member, you can do all the summary calcs for that customer.

It's drinkies time here, so this might not be 100%.

Use a self-join on customer. (I presume a customer is handled by only one employee? If not, you need a combined key - EMployee&Customer.)

Set up three fields

Count1 - Sum(Rel::NumTools) - NumTools

Count2 = GetSummary(StaffNum, NumTools)

Signal = Case(Count2/Count1 >= .9, "X", "")

This will put an 'X' in Signal for the frst and subsequent customer record which will achieve your 90% target. You can sort out (note deliberate pun) getting the first record. Depends on your report. Should be obvious. You could also set another field which has the customer num if Signal is 'X' and yet another field

defined as

Case(Min(Rel::[copy of cust num] = [copy of custnum], "X", "")

Then you'll get just one 'X' - on the record with THE number. Sorry, you also need to fix that last bit to ignore the earlier customers.