We often get asked about creating an aggregate or summary table for a tables with multiple rows. These are two ways I like to do it. Would love to hear about other ways you do it or places where you have used something like this.
Using a filter formula in a new table. This is useful when you want data from multiple table or want to do further calculations without adding more to the base table.
For me the second use case is used more often because I’ll typically have an underlying “stats” table that continually grows. If I’m applying groupings to the underlying table, then I would have to ungroup the table to do spot checks of my table (to ensure new data is being added correctly).
However, I think grouping of the base table is easily done with a view off of the underlying table. Coming from Excel, I still prefer creating a summary table when the goal is to create a neat report where I don’t want to show irrelevant data points to the end user. Similar to using a filter to get summary stats, SUMIF() and LOOKUP() can be used (thread here).
@Al_Chen I may have to disagree with you here—I love the inherent drill-down that comes with using an aggregate view. Also, it is easier to explain to non-technical users of Coda if they want to look for themselves!
True I think it depends on the use case. For drilling down I think the aggregate view makes sense when you are doing some analytical work on your own. But for anything you need to present in a meeting to a client or to management, the summary tables work better from a data viz perspective.
I have a customer table with various data. Each row is one customer.
I have an invoices table pulling from QBO daily. I want to rollup that data (total invoices, for example) and link it to the customer table. So then the customer table has a field called 2018 sales (for example) that is continuously updated when new invoices is added.
@anon10352527 yes! Shouldn’t be too difficult to put together.
In your Customers table, add a column “2018 Sales”. It should be =[Invoices].filter([Invoice Date] > date(2017,12,31) AND [Invoice Date] < date(2019,1,1) AND [Customer]=thisRow).[Amount].sum()
In this formula, you ask Coda to search through the invoices and select only those which match the current row and are within 2018. Then, take the dollar values of those contracts and sum them here. This formula assumes you are using a Lookup column to select the customer in your invoices table (which is best practice!).
Chris - thank you so much! I think it worked! I changed my field names to match yours and so far, so good. Can I contact you directly if I need more help? I have a granola company…can send granola! : )
Sure thing! Feel free to DM me, or if you’d like to share test documents to illustrate a problem, you can send to chris@emissary.io. Happy to help, and I do love granola!
