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

    Ranking records

    TracyKeenan

      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?

      Thanks for your help.  

      Tracy

        • 1. Re: Ranking records
          philmodjunk

          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
            TracyKeenan

            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
              philmodjunk

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

              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
                TracyKeenan

                Brilliant.  Thank you so much! Tracy

                • 5. Re: Ranking records
                  MichaelPalma

                       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
                    MichaelPalma

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

                    • 7. Re: Ranking records
                      CindyFirenzi

                           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
                        appt

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