Hello, I have a problem I can find a solution for in Excel but can’t seem to figure out in AirTable. Any advice much appreciated!
Here’s an example that is analogous (not exactly our case but in the most simple terms I could think of):
-
We have a table of Customers and a table of Products, with the junction being a Sales table with one customer and one product only for each record (customers can buy many products but in the Sales table we create a record for each Customer/Product combination) and also a date of the sale.
-
For each year, I need to report the number of NEW customers per product. So if Customer X bought apples last year, I do not count them again this year (Customer X/Apples). But if the same Customer X didn’t buy oranges last year and did this year, we would count them this year (Customer X/Oranges). (Reporting ALL customers per product per year is easy enough, but capturing just the new ones is eluding me!)
In Excel I could manage it with a countif formula like this (Excel formula: Flag first duplicate in a list | Exceljet), looking for the first instance of the Customer/Product combination and setting up a rollup to count the correct code. But I don’t know how to achieve this in AirTable as I cannot compare data across records.
Any ideas?
