Re: Get the latest value based on its "date" related field

498 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


I will try to make it as simple as possible:
We are in the Property management industry, where rent amount is calculated based on the latest value of an index of a given quarter. A rent has a base quarter (Q1 or Q2 or Q3 or Q4) at year N which is used for the calculation of this new rent. When the new “base” quarter index is published (at N+1) then the rent value is updated. Indexes, in general, are updated on a quarterly basis (and are roughly 2 quarters behind based on today’s date). There are 4 indexes, each rent uses only one of them. The index value usually goes up but can go down (in a economy slow-down situation).
My challenge is to automatically calculate the new value of a rent when it’s base quarter index is updated at N+1.

Current setup:
I have a table with all rents, their related index, their base quarter index, the initial rent value (base quarter year N), and this table should host the updated yearly rent
I have a table with quarterly values of each index, with the reference column is the date of publication of the index (Q1-20XX), and in columns the values of each index.
I have a table with the four quarters (unlink to years) where I hope to identify the "latest quaterly value of each index.

I need to extract this latest index value (it is not the MAX index value as the index value can go down) based on the quarter “date”.

What I did so far:
I managed to do a manual scenario where I identify the “last quarter” (for each index) with a checkbox and rollup the max value of these boxes checked, but it is NOT satisfactory long term.

TEST BAIL - Airtable

Actipole Total Management System

I’d like to find the automated solution to this calculation.

Anyone can help?
Thanks tons!

1 Reply 1

I wrote a script to lookup the first/last linked value in a linked records field for a contest being judged this week.

If you find the script helpful, I’d appreciate the feedback on the contest page, as I believe that community usefulness is taken into account in the judging.