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

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
Best Answer
-
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)
Answers
-
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 Yes, that should work. I was hoping to avoid adding columns but your solution is very clean.
Thanks,
dm
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives