Skip to main content

Hey Airtable Kinds and Queens! Need a bit of assistance for a mere airtable peasant.


I have 2 tables.


One table has: rep ID and other sales data. This data is updated monthly with the amount they sold that month.


ID#, Name, Retail Sales, Date

343, John Doe, $5,000, 1/1/2018

343, John Doe, $2,000, 2/1/2018


Second table has the contact details including email, phone number, etc.


Here is where I am struggling.


I want to create a column that will show the Sales Persons last months sales, previous 3 months average sales and average sales for all time.


I have the tables linked by the ID# so the data is linked.


I can rollup the average for all time, but I’m not sure how to put a date function into the rollup or other field to only give me the average for a specific date rang.


Any ideas?


Thank you so much!

Hi there!


You’ll want to make use of a few conditional rollups 🙂 It sounds like your tables are set up appropriately for that. Rather than manipulating the rollup function to filter your results, you’ll want to add a formula field to the table you are rolling up to handle the filtering.


On the table with your sale amounts and dates, add a field with a formula that will only show a result for the time period you want. For example, if you wanted to only show an amount from last month, you could use something like…


IF(AND(MONTH(DATEADD(TODAY(), -1, 'month')) = MONTH(Date), YEAR(DATEADD(TODAY(), -1, 'month')) = YEAR(Date)), {Retail Sales})


The above formula should show the sale amount for anything with date during last month (as I write this, it would be anything with a date in May).


Then, create a rollup field that looks at this new field 🙂


More on conditional rollups below!


You rock. This is so helpful! Thank you so much!

Reply


Terms & Conditions