Best practice for reports by Year, Month, Quarter

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. 

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

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