Best practice for reports by Year, Month, Quarter

[PeterJNCK]

Hello,
First of all, I apologize if I have duplicated this topic, but I couldn’t find the right answer.
I’m trying to determine the best practice for organizing data and reporting for a workflow that I believe many others are dealing with.

I have a table called PAYMENTS, where we store all payments our company has made. We also have a RECURRING PAYMENTS table, where we track recurring payments that are automatically added to the PAYMENTS table based on the period set up.

However, we are struggling with reporting our payments for specific periods, such as a year, month, or quarter. Is there any way to solve this using only the Date field? I’ve tried creating a pivot table to sort payments by months for current year and categories. 

So far, I’ve created separate tables for Year, Month, and Quarter, each linked to the PAYMENTS table. Payments are then associated with a specific period. I believe some automation could assign these periods based on the Date field.

This workflow also works when trying to predict what income or expenses to expect in specific months because I have linked the Month field in the RECURRING PAYMENTS table.

Or should we create a top-level table where automation creates a new record for each month and links it to specific Year, Month, and Quarter? This way, each record would represent a month, allowing me to group them by Quarter and Year and collect all payments using a Rollup field. Then, all reports would draw exclusively from this table.

However, I’m not sure if there’s a better way to handle this. I would really appreciate any suggestions.

Thank you!

[DisraeliGears01]

Hmm, I don't do quarter tracking, but the way I'd consider handling this is adding formula fields that transform the date into year, month, and quarter fields, which you can then build filters on. This works much better since AT made formula output formattable into single selects. For month for instance, use "DATETIME_FORMAT({Datefield}, 'MMMM')" and add the individual months as single selects. Do the same for year and then you can create a filter for all records that are 2022 and February (or whatever).

Thinking about the quarter tracking, you can add a nested IF or SWITCH formula that reads the months and transforms them into Q1, Q2, Q3, Q4. 

[ScottWorld]

Right, as @DisraeliGears01 mentioned above, you would want to create a formula field that contains ALL THE DATE VALUES that you would want to see on your pivot table, and make sure that those values are separated by commas.

So, for example, your formula field might end up with a result that looks like this for one particular record:

Month December,Year 2024,Q4 2024

Then, you would create an automation that copies and pastes your formula field into a linked record field that links to a brand new REPORTING table that you setup. Make absolutely 100% sure that your primary field in the reporting table is a "single line text" field.

What this will do is create 3 records in your new REPORTING table.

So, in the example above, you would end up with 3 records in your REPORTING table, each one with one of the following values as the primary field value:

- Month December
- Year 2024
- Q4 2024

Then, all of your values will be "normalized/flattened" across one field in your REPORTING table, so you can create a pivot table from there.

And each one of those records will automatically be LINKED BACK to your original record in the original table, so you can bring in any other values that you want via lookups.

I don't discuss this particular methodology, but I do discuss "flattening data for pivot tables" in this Airtable podcast episode.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo]

Hmm, depends on what kind of reporting you need, really.  If it's just a display thing then you could probably just get away with using a pivot table in an Interface like you mentioned as pivot tables support bucketing by Year, Month and Quarter like you want

Assuming this is what your data looks like:

Here's a pivot bucketed by month:

I couldn't get grouping by category to work with the pivot tables though and ended up creating one pivot table per category for my dashboard which might not work for you

---

If you need to be able to use the data, e.g. in emails, do calculations on them etc, then you'd need to create new table(s) for that.  Whether you use one table per reporting period or just consolidate them all in one table called 'Reports' is going to be pretty subjective based on your workflows and I can't really think of big downsides to either option really

Personally I like separating them out into one table each as it's just easier to think about if that makes sense.  Definitely tedious if you've got a whole bunch of custom reporting periods besides year, month, quarter though.

I usually handle this by creating a formula field that'll output a unique value per reporting period, i.e. the 'Year to paste', 'Month year to paste' fields below.  The category details would be in the formula fields too

Then I have an automation that'll paste those formula field values into the appropriate linked fields

That auto populates the links, which gives me this:

Quarter table:

Month table:

Year table:

Link to base