Blog
Formula - How to rank times including joint times and ties
September 29. 2024
This article walks through how to rank items (such as race times) including how to deal with ties
When building apps for competitions or races (such as marathons or sports days), ranking times can be challenging, especially when dealing with ties and joint times.
This article walks through an example of how to carry out this task.
Example Demonstration - Walkthrough
Another feature of this collection is that the times are defined as strings rather than time objects. This provides a practical demonstration of how to deal with data from external data sources.
How to rank the items by time
To rank these participants based on their race times, here's a formula that derives the result. If necessary, we could build a collection with this result by calling ClearCollect.
The key points to note about this formula are as follows:
Sort - We sort the source collection by the Time value in ascending order. Because Time is a string, we convert it to a time by calling TimeValue so that we sort correctly.
AddColumns - This function adds a new column to the output called Rank.
CountIf - The value of the Rank calls CountIf to count the number of participants whose race time is less than the current participant’s race time. By adding 1 to this count, we get the rank of the current participant.
The greater than (>) operator in the call to CountIf may look odd. To clarify, let's assume the call to AddColumns is currently parsing Alice's record. In this instance:
TimeValue(baseDate.Time) = 00:12:34.
There are no records in sortedDate/colRaceParticipants where '00:12:34' is greater than the time value. Therefore, the call to CountIf returns 0, and the rank will be (0 + 1)
This approach of calling CountIf ensures that each participant is ranked correctly, even when there are ties.
The screenshot below illustrates the output when we apply the AddColumns function to the Items property of a gallery. We can see here how the result is ranked correctly in time ascending sequence, and how Bob and Charlie are both ranked 2nd since they have identical race times.
This article walks through an example of how to carry out this task.
Example Demonstration - Walkthrough
To demonstrate this task, we’ll use a sample collection of race participants and their respective times, as shown below.
The example data shows how Bob and Charlie share the same race time of 13:45, and will therefore be ranked joint second.
ClearCollect(
colRaceParticipants,
{ Participant: "Eva", Time: "00:16:34" },
{ Participant: "Alice", Time: "00:12:34" },
{ Participant: "Jack", Time: "00:21:45" },
{ Participant: "Charlie", Time: "00:13:45" },
{ Participant: "David", Time: "00:15:23" },
{ Participant: "Hannah", Time: "00:19:23" },
{ Participant: "Frank", Time: "00:17:45" },
{ Participant: "Ian", Time: "00:20:34" },
{ Participant: "Grace", Time: "00:18:56" },
{ Participant: "Bob", Time: "00:13:45" }
);
How to rank the items by time
With({sortedDate:Sort(colRaceParticipants, TimeValue(Time))},
AddColumns(
sortedDate As baseData,
Rank,
CountIf(
sortedDate,
TimeValue(baseData.Time) > TimeValue(Time)
) + 1
)
)
Sort - We sort the source collection by the Time value in ascending order. Because Time is a string, we convert it to a time by calling TimeValue so that we sort correctly.
AddColumns - This function adds a new column to the output called Rank.
CountIf - The value of the Rank calls CountIf to count the number of participants whose race time is less than the current participant’s race time. By adding 1 to this count, we get the rank of the current participant.
The greater than (>) operator in the call to CountIf may look odd. To clarify, let's assume the call to AddColumns is currently parsing Alice's record. In this instance:
TimeValue(baseDate.Time) = 00:12:34.
There are no records in sortedDate/colRaceParticipants where '00:12:34' is greater than the time value. Therefore, the call to CountIf returns 0, and the rank will be (0 + 1)
This approach of calling CountIf ensures that each participant is ranked correctly, even when there are ties.
The screenshot below illustrates the output when we apply the AddColumns function to the Items property of a gallery. We can see here how the result is ranked correctly in time ascending sequence, and how Bob and Charlie are both ranked 2nd since they have identical race times.
