Conditional Rollup Date Range

[Scott_Lusignan]

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.

[W_Vann_Hall]

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.

[Joshua_Owen]

I am having a similar problem, I have a table with a date field and a currency field and I have this link to another table by a category field. I am trying to do a rollup on only the records with the current month, is there a way to do this.

[Jeremy_Oglesby]

@Joshua_Owen
In the table with the Currency and Date fields (the one you want to rollup), create another Formula field with a formula like this:

IF(
   AND(
      MONTH({Date Field}) = MONTH(TODAY()),
      YEAR({Date Field}) = YEAR(TODAY())
   ),
   {Currency Field}
)

This will reproduce the value in the Currency field, but only if the record is from the current month. Now you can rollup this new formula field, rather than the currency field, and it will rollup only records from this month.

Since you can’t apply logic in Table A that operates on Table B, you just have to create the conditions in Table B itself with formula fields. In other words, instead of being able to express a complex query against Table B from within Table A, you have to apply the query logic in Table B itself, so that you can just make a simple query from Table A.