Accessing field sums for analytics

[George_LoSasso]

Hello everybody.

I have built a base that calculates individual client spending for four different years. Each client is a record, and contains the spending for each year, so essentially I have four yearly spending columns, and I need to access the total yearly spending (aka the vertical sum of each of those fields.) I know that airtable automatically shows what those sums are at the bottom of the table, but I need those numbers for year over analytics, and it seems that there is no way to retrieve that sum in the software. Another thing that I am struggling with is getting a numerical count of records, something akin to the “countif” function in excel. I just need to do basic analytics, like divide the yearly total by number of clients, or find percentage difference in spending between years, and I would like to do that within airtable. I can’t figure out how to do those things with blocks either. I understand that airtable is a “relational database” and not a spreadsheet, but it seems ridiculous that there is no way to do even BASIC operations and analytics.

Is there something that I’m missing?

[Justin_Barrett]

One common way of collecting summaries is to make a summary table. I did a simple example of this recently in another thread. In short, it involves linking every record in the main table to a single summary record in another table (or multiple records, depending on how you want to summarize your data), then using formula and other fields to count records and do other operations.

While creating these links can be done manually, for tables with lots of records it can become cumbersome. One way around that is to begin by building a formula that simply contains the name of the to-be-linked record as a string. Using my example from that other discussion, I named my summary record “Summary,” so that’s what I’d put into the formula field:

"Summary"

This will fill every record with that same string. Now you convert the field type to a link pointing to the summary table, and it will turn those strings into links.

I hope that the details in the other thread linked above will be enough to spark the process for you. If not, though, just holler.

[George_LoSasso]

Thanks, that was really helpful. One question I have is about displaying those numbers. I realized since formulas can only operate on things in their same row, if I wanted to do any math with those numbers it would all have to be in the same row, so now I just have one long unsightly record called summary that houses all of those numbers. Do you know of any other way to display those? I tried using chart blocks, pivot tables, etc, but since its only one record that doesn’t work very well.

[Justin_Barrett]

Yeah, summary records are great for aggregating data, but not the best for displaying it. You could try using Page Designer to arrange key info onto a single page, though even that option has limitations. Perhaps @Bill.French can recommend some alternative solutions that can be accomplished via custom software that pulls data from that lone record into a more pleasing-to-the-eye report.

[BillFrench]

I’ve been following this thread and it seems to be a common challenge with Airtable - the need to arbitrarily render KPIs and computations independent of record and field organization.

On one hand you need aggregation tables, but on the other, you have specific presentation requirements for “data consumers”. Typically, we regard these requirements as external dashboard or reporting processes.

Unfortunately there is no simple (or easy answer) and the Page Designer block is the likely candidate as a first step. I don’t regard this as a good answer unless you already have your data consumers on a Pro plan.

I almost always tackle customized reporting and rendering external to Airtable using the API not because it serves my interests well, but because it serves data consumers well. These are people who rarely (if ever) actually use Airtable. Their interests are the conclusions about the data. They want the KPIs, the summaries, the assessments, and the morning briefing in a concise PDF document that finds them wherever they might happen to be - iPhone, desktop, whatever.

But this does raise a key requirement for all information services including Airtable - we must think about data producers, data workers, and data consumers separately. These are each unique personas whose needs tend to be very different.

ps - I miss CountIF() as well.

[Justin_Barrett]

As an alternative to using Page Designer, a PDF can be built with the help of Integromat, which has an HTML to PDF module. I originally used Page Designer for creating my business invoices, but that requires manually exporting a PDF, then attaching that PDF back to my invoice record. I also wanted my invoice files to follow a certain naming convention. Making this name was easy via a formula, but I still had to copy the result from the formula field and paste it into the save dialog when exporting the PDF. Now all I do is click a link, and the named invoice PDF appears as an attachment seconds later.

That link is a webhook for an Integromat scenario, and passes in the Airtable record ID for my invoice. The scenario runs the HTML to PDF module, pulling the HTML that I made in Airtable (in lots of hidden fields across multiple tables, all looked up and assembled in the invoice record). The PDF file is saved to Google Drive, and the Google Drive share URL is fed into the attachment field in my invoice record.

It takes some time to set up the HTML fields where they’re needed, along with testing the HTML to PDF conversion to make sure the final layout is what you want, but IMO it was well worth the effort. My client gets a clean, professional invoice, and I get the ease of making it with one click.

[George_LoSasso]

I’ll check it out, thanks so much for your help.