Hello,
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!
