Finding the second to last in a collection

Mark Hibyan

Hello -

I'm using INDEX to find the value of [Scanned Location] that corresponds to the MAX [ROW#] of a collection that matches my current [Asset ID]. My formula is =INDEX([Scanned Location]:[Scanned Location], MAX(COLLECT([ROW#]:[ROW#], [Asset ID]:[Asset ID], [Asset ID]@row))).

Now what I want to do is find the second to last [Scanned Location] value of that same collection. Any ideas on the syntax here? So, if asset 13 has a collection of locations (A, D, B, Z), it would return "B". Right now, I can isolate "A" (using INDEX, 1), and "Z", using MAX. I'd like to isolate the second to last, but I can't use 3, since that collection could be 1 or many values.

Thanks,

Mark

Paul H

See my solution in this other post, you could use the same approach

Paul H

Mar 16, 2022

You can identify them with two helper checkbox columns

https://us.v-cdn.net/6031209/uploads/GGECJS5W3WZD/image.png

Newest:

=(IF([Date Of Evaluation]@row = MAX(COLLECT([Date Of Evaluation]:[Date Of Evaluation], [Score Type]:[Score Type], [Score Type]@row, [Team Member]:[Team Member], [Team Member]@row)), 1))

Newest -1:

=IFERROR(IF([Date Of Evaluation]@row = MAX(COLLECT([Date Of Evaluation]:[Date Of Evaluation], [Score Type]:[Score Type], [Score Type]@row, [Team Member]:[Team Member], [Team Member]@row, Newest:Newest, 0)), 1), 0)

Paul H

See my solution in this other post, you could use the same approach

Paul H

Mar 16, 2022

You can identify them with two helper checkbox columns

https://us.v-cdn.net/6031209/uploads/GGECJS5W3WZD/image.png

Newest:

=(IF([Date Of Evaluation]@row = MAX(COLLECT([Date Of Evaluation]:[Date Of Evaluation], [Score Type]:[Score Type], [Score Type]@row, [Team Member]:[Team Member], [Team Member]@row)), 1))

Newest -1:

=IFERROR(IF([Date Of Evaluation]@row = MAX(COLLECT([Date Of Evaluation]:[Date Of Evaluation], [Score Type]:[Score Type], [Score Type]@row, [Team Member]:[Team Member], [Team Member]@row, Newest:Newest, 0)), 1), 0)

Mark Hibyan

Thank you, use of helper columns here got me on the right track.