Top Five Sales Flag

[AnthonyDixon]

Hi All,

I have a customer table that has a relationship to a sales order

table based upon customer ID. I am trying to create a calculation

field that would flag the customer as being in the top five customers

of all total sales. Not sure if this makes sense but I have included

my sample file anyway. Anyone have any ideas?

Thanks

-T

FMP 10 Advanced PC/Mac

Top Five Sales.fp7.zip

[eos]

Try this. It uses a new calculation field in the Company to sum up the total sales, and a self-relationship, sorted by that field.

The calculation in your Top-Five field then grabs the total value from the fifth company, compares it with the current company's total

and sets the flag accordingly.

The position five in the formula is hard-coded; you can replace it with a field or global variable to mark the top 2, 3 etc., or grab

multiple position values from the list and compare them with the current value to mark entire groups. (I guess this can be interesting for sport events.)

If you only want a visual marker and no field to search on or filter, you can remove the field and use the formula to conditionally format

the company name or do other stuff.

Top Five Sales_eos.fp7.zip

[AnthonyDixon]

Thanks a million, works great! I really like the option to set the

global variable. Very helpful.

Thanks again.

-T