8 Replies Latest reply on May 17, 2017 2:44 AM by appt

# Ranking records

### Title

Ranking records

### Post

I am trying to find a function that will allow me to rank records (tennis players) according to their total points.  The trick is, when there are two players with the same total of points, they share the ranking.  The person's rank following a shared rank skips by the number of people with the shared rank.

For example:

Name     Total Points    Rank

Scott      1860              1

Tony       1650              2

Brent      1650              2

Sam        1500             4

Is there a function that can do this skipping calculation?

Tracy

• ###### 1. Re: Ranking records

Let ( [ PrevRank = GetNthRecord ( Rank ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ;
PrevScore = GetNthRecord ( Score; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ] ;
If ( PrevScore ≠ TotalPoints ; Get ( RecordNumber ) ; PrevRank )
)

This must be in a field of Type calculation with "do not store..." selected in storage options. The records must be sorted in ascending order by Total score.

• ###### 2. Re: Ranking records

Thank you.

I get an error - There are too many parameters in this function.

Total Points = field

PrevRank/PreScore are not fields - correct?  runtime variables?

I appreciate it.

Tracy

• ###### 3. Re: Ranking records

It means I left out some parenthesis when I typed this in.

PrevRank and PrevScore are temporary values that exist only within this calculation.

Let ( [ PrevRank = GetNthRecord ( Rank ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ) ;
PrevScore = GetNthRecord ( TotalPoints; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 )  ) ] ;
If ( PrevScore ≠ TotalPoints ; Get ( RecordNumber ) ; PrevRank )
)

• ###### 4. Re: Ranking records

Brilliant.  Thank you so much! Tracy

• ###### 5. Re: Ranking records

This was a great find! I've been trying to wrap my head around this for days. The only issue I had was if there were more than 2 records tieing for a rank it would not keep the rank going then skip. So where it *should* rank 1-2-2-2-2-6 it stopped at one repeat and returned back 1-2-2-4-5-6.

Any ideas?

Thanks from a FMP newb.

• ###### 6. Re: Ranking records

Got it, made a typo. Brilliant and thank you!

• ###### 7. Re: Ranking records

Hello,

I'd greatly appreciate some help! I used the formula above and it worked great for the first 172 records (out of 190 records in the found set). But on the 173rd record, a "?" appears in the field. If you click on the field you can see the rank in the next few records. But starting with record 178, you can't even click on the field.

The problem may very well stem from the fact that I've never used the "let" function, and I'm not clear on what the "rank" field is in the formula above. I assumed it was the name of the variable that is being defined (which seems circular, by necessity). When first creating the "rank" field, I wasn't able to use "rank" in the formula (as the field didn't exist yet), but I was able to go back into the formula and insert it. What am I not seeing? Thanks!

Cindy

• ###### 8. Re: Ranking records

What if you want it to rank in the opposite order. i.e. for mine the lowest time is ranked number 1