Creating quarterly reports in Coda
Keep it simple
Last year, I wrote a blog post about managing quarters, explaining how linking months to your preferred quarter gives you flexibility. That post focused on data architecture.
This post builds on that idea and explores another approach related to presenting your data. While Coda’s grouping feature is useful, it doesn’t always work well with standard quarterly data.
Like here, we sometimes need a separate table with some of the same information as our original table. This post will show you how to populate this second table.
We have four stores, each with data for 12 months, which we’ve grouped into quarters using a simple formula. January, February, and March make up Q1, and so on. With four stores and 12 months of data per store, the table has 48 rows.
The screenshot shows the formula to transfer the data from one main table to the other table. We’re focusing on: the years, the stores, the individual records, and the quarters.
A good dataset makes formulas easier to write. We first check the years used, looking at the year table. Then, using the store table, we create four rows in our new table — one for each active store. We’ve already removed inactive stores from the shop table via our filter.
These two variables are our foundation. The loop for years and stores is still running, which affects all the formulas that follow. We want it to apply to the right records in our main table.
The First() function in the formulas grabs the first value from a list. For example, the year list has 12 values, but we only need one. Since they're all the same, taking the first one works. The same goes for the quarter columns: they each have three values, and we only need one, so we grab the first.
The result of this process is shown below.
The first table shows the data from a different angle, helping us spot patterns we might miss in the second, main table. This is helpful for visualizing and getting a general overview. Keep in mind, though, that the actual data lives in the main table, organized by row.
You can see this logic in action in this doc.
Hopefully, this post has shed some light on this common Coda challenge and empowered you to create more interactive and insightful data experiences. On a personal note, creating these in-depth posts takes a lot of time and effort. While I love sharing my knowledge, a little support goes a long way. If you found this helpful, what about a donation or sharing this post with your fellow Coda enthusiasts? Every bit of encouragement helps!
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
