Operations on group summaries

[Ben_Lillie]

I have a base that keeps track of financial performance for our company. In one view I have daily estimates of revenue and actual revenue. I group those by month and use the totals.

For each day I can compute, e.g., how well we did relative to the projection – (actual revenue)/(projected revenue) by creating a formula column. I’d love to be able to compute a monthly equivalent, but I don’t see a way to reference the group summary – the summary of the formula column just averages or sums the individual percentages, which isn’t correct for a monthly summary.

Is there a way to do this?

Thanks!

[Jeremy_Oglesby]

There is not a way to reference the summary bars in Airtable formulas.

I believe it’s possible to do what you are wanting to do by creating another table, maybe call it “Summaries”. Then, you’ll link each daily performance record from your original table to a monthly record in the “Summaries” table. From there, you should be able to use Rollups to get the summaries you are after.

Table 1 (your existing table) has daily records:

Day -------- Projection -- Actual -- % of Proj -- Link to Summary
5/1/2018 --- $100 -------- $90 ----- 90% -------- {May}
5/2/2018 --- $100 -------- $110 ---- 110% ------- {May}
5/3/2018 --- $100 -------- $80 ----- 80% -------- {May}
etc..

Table 2 (Summaries) has monthly rollups:

Month ---- Linked Days ------------------------------ Proj Rollup -- Actual Rollup -- % of Proj
May 2018 - {5/1/2018},{5/2/2018},{5/3/2018},etc... -- $300 --------- $280 ----------- 93.33%

EDIT: I realize I’m probably not representing exactly the kinds of calculations you need to make, but I know nothing of these sorts of things and am just trying to represent a general process for you.

It requires manually linking each daily record to the Monthly Summary record, but once you get the hang of doing it, it becomes second nature and takes half a second.
Alternatively, you could use Zapier to automatically link a new daily record that is created to the appropriate month, and avoid having to do it manually. That’s a little more involved to set up, but I can help you with that if needed.

This was just a quick overview - if you need more help with the details of setting this up.

[Ben_Lillie]

Ah, that should work. Thank you!