Skip to main content

I have a table that uses a Date field as its primary key. Each date in use happens to be a Monday and there are records for every single Monday for the past 6+ years. This means that for a given row, the row below it is always 7 days before it.


Each row also has a Number field that grows from zero (6 years ago) to 7500 (today), but the growth is nonlinear and varies from week to week.


What I want to do is compute a weekly growth rate field. This field would be equal to:


(Number today - Number last week) / Number last week


In order to do this, I need to reference the number field from one week ago. I’m able to compute the date that I should be looking up, but the result is a formula field and I can’t figure out then how to look up the value I need from the appropriate row.


Anyone know a simple way to do this?

I think I found a solution to this, but it’s annoyingly hacky:



  • Create a Zapier task that fires every week on Monday

  • Look at Airtable for the record whose Date field matches the current date

  • Look at Airtable for the record whose Date field matches the previous Monday

  • Calculate the difference (weekly growth) in Zapier Python

  • Update today’s record with the growth percentage


I would really rather keep this workflow in Airtable, and feel like this should be an easy task - it’s just a linked field whose value is computed rather than manual.


Is there really no way to lookup to another field whose ID is computed?

Reply


Terms & Conditions