I an Airtable newbie. I work at a public library and use Airtable to manage and track our booklists. Since there are no sales or orders per se, we calculate usage using physical counts. Every few weeks I count them and record in Airtable the current qty of each booklist and the number added (if any) for each booklist. It's easy to determine all time usage: ([starting qty]+[number added])-[current qty]. But I am stuck on how to derive annual usage via formulas.
There are two tables involved: Booklists and Inventory. Booklists uses rollup fields to grab numbers from Inventory. Inventory has fields both for qty added and for physical count.
What I want I a view grouped by year, sub-grouped by booklist, showing annual usage. I can brute force it by adding a new field every year for the ending qty, but I'm hoping there's a more elegant way to do it via formula
The formula would be something like this for each booklist: For a particular year, find the earliest record date. Get the number from the physical count field from that date. Add to that the number added field for every record that falls within that particular year. Get the number from the physical count field of the latest record date within that year. Subtract that and you have your annual usage.
Any help would be gratefully appreciated!
