Extracting maximum year grouped by another field

[Jyo_D]

Hi there,

I am trying to figure out a formula to extract max year for a specific company based on the fields below. Max year should pick a max year for that particular company in the field. I tried using MAX function but it didn’t appear to work, is there a way around this?

[kuovonne]

Thank you for the screen shot. It really helps explain your situation. It also looks like you are accustomed to working with spreadsheets versus databases. Setting up a database can be different from setting up a spreadsheet.

Notice that, in your table, each company is listed multiple times, and the Max year would be the same for each row for that company. In situations like this, it would be best to shift that information to a different table.

You don’t state the name of this table, so let’s call it [Company Years] for this explanation, as you will end up with multiple tables.

1. Create a new table [Companies] that includes each company only once.
2. In to the [Companies] table, add Link to a record field and have it link to multiple records in the [Company Years] table. Link the records.
3. In to the [Companies] table, add a Rollup field named {Max Year} that rolls up the {Year} field in the [Company Years] table. Use the MAX aggregation function.
4. Finally, back in the [Company Years] table, add a lookup field to get the {Max Year}.