Calculate difference between values on separate rows

[William_Huxley]

Hi all,

Here’s my problem:

Every 24 hours, Zapier creates a new row (in Data table) with the latest number of app ratings.

In another table (i.e. Calculation table) in the same base, I’d like to calculate the difference between today’s rating count and yesterday’s rating count.

Here’s an example of my base: https://airtable.com/shrZjNT9KAzXbDVEh

I’ve made numerous attempts but I’ve not managed to find a way to do it.

Appreciate any help you can give!

[Julian_Kirkness]

Hi

I think the most promising approach for this would be to do it in the Zap - when it runs, use a find record action to get yesterday’s record and store the value in a field in the new record (today’s) along with the value for today. You can then compare them.

I hope this helps!

[Hashim_Warren]

I think that’s a great solution. With one tweak:

Create a field in “data table” called “yesterday’s rating” and link it to the rating field in “data table”

When Zapier creates a new record, have it look up and link to yesterday’s record.

[William_Huxley]

Thanks for your suggestions!

Ideally, I’d like to avoid using Zapier – I’m using it for far too much Airtable stuff atm. Do you know if it’s possible to do it with Airtable functionality alone?

I’ve found this post: Reference entry of record in same table, eg “previous” record. It may offer the answer to my question, but I can’t quite work how to set it up.

Thanks again!

[Csaba_Vass]

Hi!
Thanks for asking me. My solution is create a linked field with ie. ‘Last days ratings’ and an other with today’s rating. You need a calculated field ‘difference’ wich calculates de difference. You can link difference field back to data table.
To create a row, and link a record automatically I have no solution. You need to create a row with actual date’s rating, and link manually the data fields in calculation table.

[Hashim_Warren]

I agree. To avoid using Zapier too much you can just link each one manually.

[W_Vann_Hall]

Actually, calculating a yesterday/today difference is relatively simple in Airtable. I explain how to do so in this reply to an earlier post, which also contains a link to an example base showing how it’s done.

[Julian_Kirkness]

Hi William

There are some good ideas here and I hope you’ve found a solution that works for you.

I just wanted to comment on your Zapier views - I find that Zapier is really an essential tool for use with Airtable - it provides the automation and integration tool which Airtable lacks and is pretty good at it (it could be improved of course - especially with the addition of instant / web hook based zap triggers).

Julian