Conditional Rollup Date Range


#1

Tried finding a solution for this, but to no avail.

I have a rollup field in my “Items” table that pulls data from my “Shipments” table. I’d like to show sales by year (2018, 2019) instead of total sales. There is a date field I could pull from in the Shipments table.

I think I’d have to do rollup with an IF / date range formula, but not sure how to do this.


#2

Well, the simplest way would be to pull the sales year into your [Items] table and then group the view by {Sales Year}. Since you’d be grouping based on a calculated field (lookup or rollup), you’ll lose some standard grouping functionality (e.g., modifying grouping data in a record by dragging it from one group to another) — but that doesn’t seem to be an issue here.

If you need to create per-year summations as a field in another record (for use in a dashboard or annual report, for instance), the task is a lot harder. You can find some code to do annual (or maybe it was monthly; works the same way, though) summaries in my Sales CRM Dashboard in Airtable Universe. Essentially, you’re performing a multi-record calculation — and the preceding link leads to a post and example bases that discuss such beasts in far more detail than anyone could possible want.