Quartile Ranks

[kims]

I'm stumped. I need a calculation that will return the quartile rank for a record within a set. I was able to easily do this in Excel, but not so sure how to do this in FileMaker, or if it is even possible to do so. Any tips would be greatly appreciated!

 

For example: I have 14 records. I need to know in which quartile they ranked in based on the amount of their contributions. 

[jbante]

If you sort the records by contribution, you can set the quartile for each record with the calculation:

Ceiling ( Get ( RecordNumber ) / Get ( FoundCount ) * 4 )

 

[LaRetta]

If you use this in a calculation, be sure to set the field Options > Storage Options to 'do not store calculation results'.

Nice, Jeremy! 

[LaRetta]

Kims,

What do you wish to do in case of tie?  For instance, two identical amounts can actually appear in different quartiles - the first being in section 3 (for example) and the next duplicate in section 4.  Sorting, of course, sorts by Amount but then by order of creation.  If you wish ties to hold the same quartile number, it might get more complicated.

[kims]

Thanks for the suggestions. I got that to work, but what if I need to figure out quartiles for multiple fields? So I have the quartile rank based on contribution. Then I need another quartile rank based on account balance. Then another quartile rank based on number of people. 

[jbante]

1 hour ago, kims said:

Thanks for the suggestions. I got that to work, but what if I need to figure out quartiles for multiple fields? So I have the quartile rank based on contribution. Then I need another quartile rank based on account balance. Then another quartile rank based on number of people. 

Then use stored number fields instead of calculation fields, and set each quartile field in a script that does separate sorts before setting each quartile field. A script also potentially makes it easier to handle ties, to address LaRetta's point.

Edited October 3, 2017 by jbante