Hi,

I previously posted a topic (see below) and @Justin_Barrett has answered the topic to my satisfaction. However, when trying this solution with dates, I can not get it to work. I looked it over multiple times but I’m at a loss here.

Is it possible to do this with dates (so am I doing something wrong?) or is there another solution?

*Previous post:*

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!

Answer by @Justin_Barrett:

Welcome to the community, @Ion_Giskus! 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.

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: