How do I group by multiple columns to perform a calculation? For example, in the table below I want to determine the attempt score by combining columns and then finding the mean.
My approach is to group the scores by competitor id, judge id, heat id, and attempt id… something like below:
competitor 1, judge 1, heat 1, attempt 1 (1-1-1-1) would be 5
competitor 1, judge 2, heat 1, attempt 1 (1-2-1-1) would be 6
competitor 1, heat 1, attempt 1 score would be 5.5
(for simplicity, I’m not using the actual row ids, just simple numbers in their place)
Row ID
Score
Competitor Id
Judge Id
Heat Id
Attempt Id
1
5
1
1
1
1
2
4
1
1
1
2
3
5
1
1
1
3
4
6
1
2
1
1
5
5
1
2
1
2
6
6
1
2
1
3
7
8
2
1
1
1
8
8
2
1
1
2
9
6
2
1
1
3
10
6
2
2
1
4
11
7
2
2
1
1
12
8
2
2
1
2
13
5
2
2
1
3
14
7
1
1
1
4
15
1
1
1
2
1
16
7
1
1
2
2
17
2
1
2
2
1
18
8
1
2
2
2
19
8
2
1
2
1
20
2
2
1
2
2
21
6
2
1
2
3
22
8
2
2
2
1
23
1
2
2
2
2
24
5
2
2
2
3
I’m just not sure how to do the grouping using glide. Perhaps I need to do that outside of glide.
Maybe something similar can be done with airtable. I’m not sure if I can pull this off using glide tables. If I hit a deadend, I’ll switch my backend to support the use case.
If I’m understanding correctly, what you actually want to do is take the average score from all judges for each combination of competitor/heat/attempt - correct?
Assuming that’s correct, this is quite simple to do in Glide Tables:
Create a template column that combines CompetitorID, HeatID & AttemptID
Create a multiple relation column that matches the template column with itself
Create a rollup column, target it at the relation column, and select the average Score
You’re confirming that the gap for me was the concept of a multiple relation column. I literally just saw it being used in this video https://youtu.be/YwZF7VZIDkY?t=461
This may be for another thread. I don’t actually have an attempt id yet…
Each judge scores each attempt in order they occur. I figure if I know the sheet order I can derive the attempt number.
The order of the rows is irrelevant for the purpose of deriving the average.
Okay, so given your updated structure, the template column should combine CompetitorID & HeatID.
Although, that will give you the average score of all attempts for each competitor for each heat across all judges. Is that what you want?
The attempt score - this is the average score per attempt (ie. the average from each judge’s score on an attempt)
The two highest attempt scores per competitor (was planning to look into this out later, but it is relevant to the current discussion)
The final score is determined from a competitors two highest attempt scores
I’m realising that leaving out criterion #2 (ie. only the two highest scores are used to determine the final score) has an effect on the approach. This may vary between event types but first implementation uses this as the default mechanism.
For this reason the order in which the attempts were recorded becomes important. The order in which scores are entered is the mechanism I have at the moment to know which attempt a judge was scoring (this may need to be reviewed in the future).
Each judge must score the same attempt. An attempt score is only authentic if it is averaged against the same competitor, heat and attempt. Currently attempt order is based on the order in which each judge scored an attempt.
It’s all very doable, and not that difficult.
But it does sound to me like you’d be much better off with a column for AttemptID, rather than relying on sheet order. Apart from complicating things somewhat in terms of calculating what you need, it also sounds like it may not be a reliable method of determining attempt numbers.
I think we’ve already dealt with this, yes?
Several different ways you could approach this. Once you have the multiple relation, you could use a lookup column to get an array of the scores through the relation, sort them using the array sort plugin, and then either use an array slice column to pick out the last two - the sort will return in ascending order - or (perhaps better) two separate single value columns.
Thank you very much @Darren_Murphy.
I’m jumping back to my “real work” for the rest of the day. Hopefully I can take a look and try out some of your suggestions later.
I tend to agree. I’ll look at the best way to capture this… I need a way that each judge’s entry is tied to the correct attempt id every time. I don’t fully trust the idea of order in sheet.
Thanks @Darren_Murphy… I’ve added a simple way for judges to specify attempt id.
With that in place I did the… combined-field-values template > rel > rollup calculation dance (my name for this) and it works as expected.
I now have a working prototype. Not 100% happy with the UI but I’ll review and see how best to improve that.
Do you mean auto-deriving based on sheet order?
If yes, that’s easy - do a lookup through a relation thats filtered using competitor+judge+heat. This will give you an array of the 3 attempts, where item 1 is attempt 1, item 2 is attempt 2 and item 3 is attempt 3. You can then extract individual items from the array using single value columns.
I’ll leave it out for now, just to avoid the edge case of judges who enter scores in the wrong order. I think it’s an extreme edge case but would rather discuss it with the users before implementing auto-derived attempt ids.
Row ID