Whilst slightly obscure, this task has multiple steps in order to reach the final result which require you to think about how data is handled. This solution outlines each step in order to reach the final result.
To calculate the average we need to use the DAvg() function which looks at a particular field for all participants who meet a filter.
To find the average we need to use each participants Run leg time as a deciamal value using Run.Decimal.
DAvg("[Run.Decimal]";.....)
When using D Functions the fields and filters must both be wrapped in quote marks.
For the filter we need to consider which participants we want to compare to, we need to consider only participants who have finished the run, and those of the same Contest and Gender.
When we want to compare fields in a filter to only consider participants with the same value we have to escape quotes around the field, for example:
[Contest]=""" & [Contest] & """
The same as our field, the whole filter must then be wrapped in quote marks. The full DAvg expression is as follows
DAvg("[Run.Decimal]";"[Run.TimePositive]=1 AND [Contest]="""&[Contest]&""" AND [Gender]="""&[Gender]&"""")
This returns the average Run time for each gender as a decimal value. We then also used format() to display this as a regular time in the Time Format "HH:mm:ss".
The difference from the average is calculated for each participant by subtracting the average time as a decimal from Run.Decimal.
[Run.Decimal]-DAvg("[Run.Decimal]";"[Run.TimePositive]=1 AND [Contest]="""&[Contest]&""" AND [Gender]="""&[Gender]&"""")
The values returned provide the difference from the average, however these differences may be both positive and negative, if the list was sorted by this then the lowest (most negative) value would be returned first. To sort this correctly we need to ignore whether times are positive or negative, this is done using the abs() function.
abs([Run.Decimal]-DAvg("[Run.Decimal]";"[Run.TimePositive]=1 AND [Contest]="""&[Contest]&""" AND [Gender]="""&[Gender]&""""))
The time is then finally formatted as "HH:MM:Ss" which shows the relevant units only, the final expression to display the difference is written as
format(abs([Run.Decimal]-DAvg("[Run.Decimal]";"[Run.TimePositive]=1 AND [Contest]="""&[Contest]&""" AND [Gender]="""&[Gender]&""""));"HH:MM:Ss")
The output list is simply grouped by Contest.Name and then set to Filter with Ignore on MaleFemale, the sorting is based on the absolute value of the difference which we already calculated.
To rank participants we use AutoRank, which considers the Output grouping and sorting to rank participants. We don't create a rank for this as the aggregation functions are not fast and put heavy load on the server. By only calling the fields directly in the Output then they are only calculated when the Output is loaded.
