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

[Ion_Giskus]

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! :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:

[Justin_Barrett · Accepted solution]

It’s possible to do this with dates, but unfortunately it takes an extra field. I’m not sure why Airtable behaves this way, but I ran into this issue the other day when trying to do date rollups.

If the aggregation formula is just MAX(values), the rollup field properly outputs a datetime that can be used elsewhere for date comparisons and operations. Normally you can build more elaborate formulas in rollup fields (I’ve done it a lot). Translating the solution that I posted in the other thread into this context using the following formula, I assumed that the output of MAX(values) inside it would be the same as when it’s standing on its own, meaning I’m comparing a datetime to another datetime.

IF(MAX(values) != Date, Date)

Sadly it’s not. So what is it doing? I’m not entirely sure yet, nor am I sure about how to figure out what it is.

In lieu of keeping it simple and only using a rollup field to pull this off, it can be done with only one more field. In the rollup (which I’ll call {Max Date}), leave the aggregation formula as MAX(values), which will bring over the same latest-date value from the other table. Then add a formula field with the following formula, replacing {Date} with your actual date field name:

IF({Max Date} != Date, Date)

Now you can go back to your other table and add a MAX(values) rollup of that formula, and you’ll have your next-to-latest date.