Link to another record via a computed date field

[spencer_wright]

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?

[spencer_wright]

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?