Group by month

Ask for Help
1

I have the following tables:

  1. Players: name, phone, score
  2. Activities: start date, end date, player, game, duration (h)
  3. Groups: name, players

The activities table track players activities in the games.

My goal is to view the activities in the admin panel.
The activities should be grouped by month, and for each month a list of players with calculated fields like:

  • Total durations Hours (sum of all duration for current month)
    – Days active

Here is an example of how the view should looks like:

March 2024

  • Player A | 40 hours | 5 days
  • Player B | 20 hours | 3 days
  • Player C | 50 hours | 6 days
    September 2024
  • Player A | 80 hours | 10 days
  • Player B | 50 hours | 6 days
  • Player C | 60 hours | 7 days
    January 2024
  • Player A | 40 hours | 5 days
  • Player B | 30 hours | 3 days
  • Player C | 100 hours | 20 days
2

On the backend:

  • Create a math column in the Activities table: YEAR(D)*100+MONTH(D) with D being the date of the record. We get a “month number” from this.

  • Create a query in that same table, filter by player ID being the same as this row > player ID and month number equals this row > month number.

  • Rollup through that query to get the sum of duration, and the count of days.

  • Add a rowID column to that table, if you haven’t.

  • Add a single value > first column to get the first rowID from the query.

On the frontend:

  • Add a collection pointing to the Activities table, group by month (you can add a Format Date column to beautify the month name for this step).

  • Filter the records by rowID equals Single Value > first column.

  • Show the rollup values.

1 Like