Set Analysis: Count only latest record per Player and Skill
Hi,
I have a raw table as shown below, where I have "Players" that get assessed on "Skills" multiple times.
Player
Skill
Rating
Category
Assessment Date
Lionel Messi
Dribbling
3.5
3-4
1/1/2021
Lionel Messi
Dribbling
4
3-4
5/1/2021
Lionel Messi
Free Shots
3.5
3-4
1/1/2021
Lionel Messi
Free Shots
3.9
3-4
5/1/2021
Luis Suarez
Dribbling
2
2-3
1/1/2021
Luis Suarez
Dribbling
2.8
2-3
5/1/2021
Luis Suarez
Free Shots
3
3-4
1/1/2021
Luis Suarez
Free Shots
3.5
3-4
5/1/2021
Pedri
Dribbling
2.2
2-3
1/1/2021
Pedri
Dribbling
3.1
3-4
5/1/2021
Pedri
Free Shots
1
1-2
1/1/2021
Pedri
Free Shots
1.6
1-2
5/1/2021
Ansu Fati
Dribbling
2.1
2-3
1/1/2021
Ansu Fati
Dribbling
2.6
2-3
6/2/2021
Ansu Fati
Free Shots
1
1-2
1/1/2021
Ansu Fati
Free Shots
1.2
1-2
5/1/2021
My output should be a table with skills, and counting number of players on each category, then I need to consider the Rating obtained on the last assessment only. The assessment date might differ between players also, so using Max(date) didn't work as it takes only the max date of the whole table and will get nulls on players that do not have that date.
Skill
0-1
1-2
2-3
3-4
Dribbling
2
2
Free Shots
2
2
For dribbling, level 3-4 it counts 2 players (Messi and Pedri both assessed on the same date)
*** For dribbling, level 2-3 it counts 2 players (Luis Suarez on Jan 5th, and Ansu Fati on Feb 6th....)
etc etc...
Any idea of a workaround for this? - I don't want to loose the historical data though so I can build also later evolution charts...
