Re: 2 Tables, 1 Filter : LOL Need quick help!

AT_Peasant · ‎Jun 23, 2019

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!

AlliAlosa · ‎Jun 23, 2019

Hi there!

You’ll want to make use of a few conditional rollups :slightly_smiling_face: 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 :slightly_smiling_face: