Hello all,
I am having trouble seeing a solution for this problem. Maybe it is obvious to someone else!
We have a base with several tables containing sales data, one table for each sales channel (e.g. website, direct, stores). The data for each channel comes from a different source and so is slightly different - but each record has the fields:
ProductName, Date, Quantity, Value
What I am trying to do is total sales value (and, separately, quantity - but am assuming it will be the same solution) for a given date range.
One solution I can think of is using the Conditional Rollups (can’t add link!) approach. So create new Fields in each of the Sales table - one that checks for ProductX and a date between Y and Z, and if TRUE returns the sales/quantity value, or 0 if FALSE. Then in another table rollup the results.
Whilst this is feasible technically, it is not really manageable, as:
- a growing list of products will quickly become unwieldy to keep adding fields to every single Sales table for every new product
- to summarise a different set of dates would either require editing the date range in all those TRUE/FALSE formula fields in every Sales table, or adding a second (third, fourth…etc) field PER date range PER product in each Sales table.
I am wondering whether anyone has any thoughts on how I might be able to achieve the above? In an ideal world I would be able to create a view or some other ‘window’ where I can simply change the date range as desired to see an updated summary of totals per product.
I can’t see anything obvious from the Blocks options, although I have not played with these extensively as am fairly new to Airtable.
Many thanks in advance for any assistance offered! Happy to answer any questions to clarify.
Sam
