How to find (max less one) from a list of entries

Dale Murphy · 03/15/22

To assess whether a team is progressing, my user needs to show the most recent score AND the score just before that one. The difference between the two will show direction of development.

Finding the score associated with max([date of score]@row) is reasonably straightforward.

Any ideas about how to find the score for the date that is not max, but max-1?

For context:

Row structure is roughly [Score Type] [Score] [Date of Evaluation] [Team member]

Over time there are several entries for each type of scoring for each team member.

dm

Paul H · 03/16/22

You can identify them with two helper checkbox columns

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 · 03/16/22

You can identify them with two helper checkbox columns

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)

Dale Murphy · 03/16/22

@Paul H Yes, that should work. I was hoping to avoid adding columns but your solution is very clean.

Thanks,

dm

How to find (max less one) from a list of entries

Best Answer

Answers

Categories