Top 5 Selections within 7 day Rolling Period

user06231

10.6.22-Endeavors PCC_ Grievances FINAL.xlsx

I'm creating a dashboard to track complaints filed against a facility. Each record in my dataset is assigned to a unique ID. Each unique record displays 1 "topic" they selected. The "topic" is the reason for their complaint. I need to find the Top 5 Topics selected on each ROLLING 7 day period. So today's 7 day period would be a different date range than yesterday's 7 day period, and so on. If more than 1 topic reflects the same COUNT, and therefore takes the same "place" or "ranking" as another topic, then all those topics would fall under the Top 5. I don't see any other way of doing it, because then we would have to choose which one with the same count was included in the Top 5..which we can't do. The purpose of this report is to be able to compare on a daily basis, how the Top 5 is changing, and trending between multiple 7 day periods. Their sample graph shows three 7 day periods, in a grouped bar chart. I've attached. I noticed the sample report is reporting on the SAME Top 5 topics, but I want to show what was the top 5 as of today for the past 7 days, then I want to see top 5 for the 7 days before that, and the 7 days before that. And each day, this will change. I want to then show trending of some type. I'm not sure though, if I can show trending if I'm not reporting on the same 5 topics. SO I thought maybe I need to show top 5 for past 7 days, then show the count for THOSE SAME top 5 topics for the prior 7 day periods. I've attached 2 screenshots, 1 showing the sample graph I was given, and the other showing a verbal description of what I was asked to do. Let me know if there's feedback on my interpretation as well.

I attached a spreadsheet, which an export of the dataset I'm working with, and all my transform work currently is in Magic ETL V2.