How to use summary of data (Avg) number in another formula

[Troy_Panehal]

Hello! I am using Airtable for our workflow and project forecasting. We have a view that calculates the time in which it took to complete all jobs in the last 45 days, grouped by the separate categories each of those jobs fell under (see image below). I want to use the Avg. amount of time (calculated in weeks) as a figure in a formula to create the installation date of a job in that same category sold today. Is there a way to do this?

I have attempted the separate table, rollup field solution, however, the problem is is that the data being calculated by this view is dynamic. Jobs are added (as they are completed) throughout the day and some jobs that age out of the 45 day range that this table is calculating fall off. The rollup field (in my experience) only grabs the jobs that are done at the time of creation of the rollup field.

Anybody else been able to do this?

Thank you in advance!

Count is number of jobs completed in that category.

[TheTimeSavingCo]

Troy_Panehal:

Is there a way to do this?

Unfortunately, the separate table, rollup field one is the only way to handle it I believe

Troy_Panehal:

The rollup field (in my experience) only grabs the jobs that are done at the time of creation of the rollup field.

Yeap you’re right. I think you’re going to need an automation that pastes the value in Category into the linked field you’ve created so that your rollup always has access to the latest data. Something where the trigger is “If Category is not empty and [Linked field] is empty”

Troy_Panehal:

Jobs are added (as they are completed) throughout the day and some jobs that age out of the 45 day range that this table is calculating fall off.

I don’t fully understand this part, but it feels like the conditional rollups might be helpful here?