Finding the most recent entry

Bradley CO

I am having issues finding the most recent update to my list of fleets. I have read about the Max(Collect()) function and don't quite get the result back that is needed. I get incorrect column value, incorrect funciton, etc. or when I have been able to get it to run I get back a 0 instead of an actual date. I have tried switching the column types to/from date/text & number columns which didn't seem to help.

Here is a sample of my columns.

So for Number 3572 I have two separate dates. I need a function that would show 1/08/24 or somehow mark that row as the most recent one.

The formula that I've gotten to run (but get an output of 0) is:

MAX(COLLECT(Number:Number, Date:Date, Number@row))

When I try using the formula I thought was supposed to be:

=MAX(COLLECT(Date:Date, Number:Number, Number@row))

I get an error message that states #Invalid Column Value

Any help or advice is greatly appreciated.

Chris Mondeau

Hi @Bradley CO,

Looks like you're close. It may be related to swapping your Number and Date ranges in the formula you have. Currently yours looks like it's trying to collect the Numbers instead of dates

=MAX(COLLECT(Number:Number, Date:Date, Number@row))

change to

=MAX(COLLECT(Date:Date, Number:Number, Number@row))

Here's my sample:

I added 2 columns; Recent Date and Recent Value

Recent Date (date column): =MAX(COLLECT(Date:Date, [Fleet Number]:[Fleet Number], [Fleet Number]@row))

Recent Value (checkbox column): =IF([Recent Date]@row = Date@row, 1, 0)

Both are column formulas.

What I'm doing is collecting the maximum date based on the Fleet Number column (displayed in the Recent Date). That should show you the most recent date value for that specific fleet. I also added the Recent Value column, which checks the Recent Date against the Date column to see if they match: if so, then I display a star. This is used to help add some conditional formatting to the fleet number to highlight yellow if that record is actually the most recent shipping date.

Please let me know if you have any questions!

Chris Mondeau

Hi @Bradley CO,

Looks like you're close. It may be related to swapping your Number and Date ranges in the formula you have. Currently yours looks like it's trying to collect the Numbers instead of dates

=MAX(COLLECT(Number:Number, Date:Date, Number@row))

change to

=MAX(COLLECT(Date:Date, Number:Number, Number@row))

Here's my sample:

I added 2 columns; Recent Date and Recent Value

Recent Date (date column): =MAX(COLLECT(Date:Date, [Fleet Number]:[Fleet Number], [Fleet Number]@row))

Recent Value (checkbox column): =IF([Recent Date]@row = Date@row, 1, 0)

Both are column formulas.

What I'm doing is collecting the maximum date based on the Fleet Number column (displayed in the Recent Date). That should show you the most recent date value for that specific fleet. I also added the Recent Value column, which checks the Recent Date against the Date column to see if they match: if so, then I display a star. This is used to help add some conditional formatting to the fleet number to highlight yellow if that record is actually the most recent shipping date.

Please let me know if you have any questions!

Bradley CO

Chris,

Thank you for that. I thought I had been trying that way as well. I was getting an invalid column value error. However, I changed my column type and it finally worked. Thank you for your response!!