Skip to main content

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

Hi Sam,


In order to do what you are looking for, all the sales data must be in one table. I would get rid of the separate tables and instead add all the sales record into one table. You can add a new Field with a Single Select option where you specify where the sale was done (store, website, etc.).


Once all the data is one table, you will be able to get all the details you need in several ways. For example, you can use the Charts block, the Group by option, or the Filter.

Hi Sam,


In order to do what you are looking for, all the sales data must be in one table. I would get rid of the separate tables and instead add all the sales record into one table. You can add a new Field with a Single Select option where you specify where the sale was done (store, website, etc.).


Once all the data is one table, you will be able to get all the details you need in several ways. For example, you can use the Charts block, the Group by option, or the Filter.


Hi Mohamed. Many, many thanks for your response!


What you suggest makes sense, however the limitations are record count (already several thousand in just one table) and the mix of data feeds and fields. The data is coming from different sources, and in different formats eg US dates vs non-US, $ and £, etc. I am pulling the data using different methods - some I can query directly using an API to retrieve date, others are CSV imports. If I homogenise the data too much on import I will also lose other data that I might wish to utilise.


The above aside, I am going to investigate your suggestion further. Perhaps I can even create a summary table with all sales data first, or import the data to two locations - individual tables with full data from each source, then a single table with the sales information homogenised.


Will keep thinking on this - it is a very useful suggestion, and is most appreciated. Thank you Mohamed!

Hi Mohamed. Many, many thanks for your response!


What you suggest makes sense, however the limitations are record count (already several thousand in just one table) and the mix of data feeds and fields. The data is coming from different sources, and in different formats eg US dates vs non-US, $ and £, etc. I am pulling the data using different methods - some I can query directly using an API to retrieve date, others are CSV imports. If I homogenise the data too much on import I will also lose other data that I might wish to utilise.


The above aside, I am going to investigate your suggestion further. Perhaps I can even create a summary table with all sales data first, or import the data to two locations - individual tables with full data from each source, then a single table with the sales information homogenised.


Will keep thinking on this - it is a very useful suggestion, and is most appreciated. Thank you Mohamed!


You’re welcome Sam, my pleasure.

Reply


Terms & Conditions