A FM equivalent of the Excel/Access Rank function!

[Fractallyte]

Both Microsoft Access and Excel have a very useful Rank function which returns the rank of a number in a list of numbers. Duplicate numbers are given the same rank, which affects the ranks of subsequent numbers. For example, if a number appears twice in a list and has a rank of 3, then the next number in the list would have a rank of 5 (no number would have a rank of 4).

FileMaker doesn't appear to have an equivalent function, so attached here is a small example file containing a script that does the same thing. (FileMaker 7 format!)

It's simply a list of names, with scores, and an empty rank column. Sort the Scores in ascending or descending order, then run the Rank Scores script from the Script menu. The rank of each score should then appear in the Rank column.

Note that this script isn't dynamic: you'll need to sort the data first, in ascending or descending order, before ranking it. If the data changes, you'll need to re-sort then re-rank it.

Rank.zip

[cjaeger]

Here is a better approach:

define a value list of field "score". value lists are always sorted, and entries appear only once.

Then use the value list items function to get all values into a calc. Add a leading and trailing q| (return), and search for the position of q| & Score & q| in the expanded list.

Now simply count the # of returns in Left(list; position(q| & list & q|;q| & Score & q|) - that's your rank.

Revised sample is attached. Also includes a custom function, Rank_cf. (name changed because of conflicting field names...)

Rank.fp7.zip

[Tombstone0]

The only problem with this custom function is that if there's a tie it will not skip the subsequent ranking.

it will provide the ranking [color:red]1, 2, 2, 3

rather than [color:red]1, 2, 2, 4

To correct this issue I changed my number I was ranking (in the value list) from

[color:red]average

to

[color:red]average & "-" & serial

and I changed the custom function to the following:

[color:red]Let(

list = ValueListItems ( Get(FileName) ; ValueListName) ;

PatternCount(

Left("¶" & list & "¶";

Position("¶" & list & "¶";"¶" & Field & "-";1;1));

"¶")

)

Basically I changed

[color:red]"¶" & Field & "¶"

to

[color:red]"¶" & Field & "-"

[Jake Greer]

I'm having trouble implementing this feature.

My problem is that I want to evaluate the ranking on a calculated row, and I can't make that calculated row into a value list.

So it does order whenever I'm sorted by the calculated row, but not when everything is all jumbled up. I want to create multiple rankings so it would be a hassle to completely reorder each column to get each ranking.

Is there a way around this? Any help would be greatly appreciated.

[Jake Greer]

I found a temporary work around by adding sort records to the script dialog... but I'd prefer something without sorting.

[David Jondreau]

You can do this with a custom function, but you need an "Advanced" version of Filemaker to create one.