Hello,
I am trying to rank the most recent year and quarter for us and our competitors so I can show trends for the most recent four quarters of data, but cannot figure out how to make the ranking reset for each company. For example, this is what my data looks like before ranking:
| Company | Company Owner | Year | Quarter | Rank |
| Company 1 | My Company | 2019 | 1 | |
| Company 1 | My Company | 2019 | 2 | |
| Company 1 | My Company | 2019 | 3 | |
| Company 1 | My Company | 2019 | 4 | |
| Company 2 | Competitor | 2018 | 4 | |
| Company 2 | Competitor | 2019 | 1 | |
| Company 2 | Competitor | 2019 | 2 | |
| Company 2 | Competitor | 2019 | 3 | |
| Company 3 | Competitor | 2018 | 3 | |
| Company 3 | Competitor | 2018 | 4 | |
| Company 3 | Competitor | 2019 | 1 | |
| Company 3 | Competitor | 2019 | 2 | |
As you can see, the most recent quarters:
- Company 1: 2019 Q4
- Company 2: 2019 Q3
- Company 3: 2019 Q2
I would like the ranking to behave as followed:
| Company | Company Owner | Year | Quarter | Rank |
| Company 1 | My Company | 2019 | 1 | 4 |
| Company 1 | My Company | 2019 | 2 | 3 |
| Company 1 | My Company | 2019 | 3 | 2 |
| Company 1 | My Company | 2019 | 4 | 1 |
| Company 2 | Competitor | 2018 | 4 | 4 |
| Company 2 | Competitor | 2019 | 1 | 3 |
| Company 2 | Competitor | 2019 | 2 | 2 |
| Company 2 | Competitor | 2019 | 3 | 1 |
| Company 3 | Competitor | 2018 | 3 | 4 |
| Company 3 | Competitor | 2018 | 4 | 3 |
| Company 3 | Competitor | 2019 | 1 | 2 |
| Company 3 | Competitor | 2019 | 2 | 1 |
This way, the most recent quarter for each company is the highest rank. I have tried aggregation on Year, Quarter, Company, and attempted many other things, but to no avail. Any input would be greatly appreciated!
