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...
