Building Table based Reports that has live data

[DennisyuTSS]

Hi, non-dev here

I have been an avid user of Airtable and I'm enjoying it but there's some things like proper pivot tables and reports I wish were available.

So anyway, I am currently trying to build a report that uses live data from one table to another.
When we're using excel, this was how it looked, as you can see, we would manually input a lot of the fields, and check them manually, use formulae to help us calculate some of the metrics we wanted.

 But we want live data and data that can be captured weekly on Airtable I've come up with a relatively.. tedious solution do let me know if there's a better way.

Here's what I did

1. I created a new table to host the live data, link all enrolled students into this record, created a column for each cell in that table that is not calculated/formulae (about 200 columns)
2. set up an automation to make sure every new enrolled student is linked to this table. Now I have live data that is extremely hard to view, I can build an interface and be done with this.
3. Now the snapshots and the calculated fields.. I duplicate the table, and change every field from a count (with filters) to a number. Now I create another set of the same fields (i need to compare last week's data with this weeks data. And I create another set of formulae fields to do my calculations.
4. I create an automation that basically triggers every week, find the live data, and find last week's data. Create a new record in the weekly snapshot table, and fill in every field (about 500 - 600 fields)
5. Now I create an interface for this snapshot table for my report

Before I jump into beginning this tedious chore, is there a better way to do this?
Appreciate any form of help

[TheTimeSavingCo]

Could you provide a base with some example data and an example of how you'd want the data from there compiled into an interface / report?  Any advice about the setup is going to heavily depend on how your data's formatted and what you want the final output to be, and the example base goes a long way to clarifying both of those things

[DennisyuTSS]

Hi Sorry if it was hard to understand

https://docs.google.com/spreadsheets/d/1wtUFwTVYUiD357rsX0pQR51inXmuQAibpYqoA4gDGLw/edit?usp=sharing
https://airtable.com/shrMiKzAEKp42CpVU

You can refer to the google sheet and the airtable base I've made for sample purposes.
The goal is to track the occupancy of each coach's time slot
How many of which are new students, how many are students that have just quit, or are on some pause status.

Having a view similar to Google sheet would be preferable for sure. A full pivot table that allows me to calculate individual cells rather than entire columns

[TheTimeSavingCo]

Thanks, this helps massively!  I think you could get away with just creating conditional Count fields in your "Coaches" table actually.  E.g. you could create a "Count" field  called "Paused" and give it a condition to only count students who have an enrolment status of "Pause"

You could combine this with a weekly automation that would compile everything for you in another table, giving you with the weekly stats

Apologies if I've totally misunderstood what you're trying to do!

[DennisyuTSS]

Thanks, I'm not too sure if that makes that much of a difference to what I was doing.
There's no excel/sheets table like the one in my google sheet example where I can add specific formulae for each cell right?

[TheTimeSavingCo]

Hmm, I'm not sure I'm following.  We wouldn't require one field per coach per status, we'd have one count field per status type in the "Coaches" table instead

> There's no excel/sheets table like the one in my google sheet example where I can add specific formulae for each cell right?
You could construct one as a junction table within your Airtable and use formula fields?