How can I use the Rollup function to output the value before the Max(value)?

[Ion_Giskus]

Hi,

I am trying to find a way to find the value that is the second to the max value of multiple linked record using rollup. I can find the max value, but I am still unable to find a way to find the second to max value.

So if I have, 21840, 20194, 21664, I need to be able to work with the max(21840) and the second to max(21664).

I hope I have explained my situation properly and hopefully someone can help me.

Thanks in advance!

[Justin_Barrett]

Welcome to the community, @Ion_Giskus! :grinning_face_with_big_eyes: This is doable with a couple of back-and-forth rollup fields between the two related tables. Just an FYI, though, this will only work if the numbers are all unique. If there are any repeats, this won’t work.

You didn’t mention the names of your tables, so I’ll just go with [Values] for the table with the records containing these values, and [Rollup] for the table where you’re currently rolling up those values from linked records.

Let’s say that {Max Value} is your current rollup where you’re using MAX(values) as the aggregation formula to find the highest number. Back in the [Values] table, add a rollup to bring that max value back over, using this as the aggregation formula:

IF(MAX(values) != {My Value}, {My Value})

BTW, {My Value} is what I’m calling the field where the number is stored; change as needed. :slightly_smiling_face:

What this will do is effectively echo the same value into a different field, but only on records that don’t match that max value that you’ve rolled up already. I chose to name this field {Not Max} because that’s effectively what it’s giving you: every value except the max value. That now makes the next highest value the new max in this new field, so you might guess what happens next: go back to your [Rollup] table, and add another rollup field collecting from the {Not Max} field in [Values], again using the MAX(values) aggregation formula. That will give you the next highest value.

Here’s a test that I ran with some simple numbers. First the [Values] table:

And the [Rollup] table: