How to Coda Time Related variables?
Keep track of changing VAT, Salaries etc.
More often than we assume, things change. For certain services and goods we are used to changes like the price of a Bitcoin or the fuel price. For these type of items we automatically create tables with dates.
However also a salary changes over time, like the VAT rates we pay. This blog is about how to Coda time dependent variables that only change once in a while.
Salary
We deal with the logic of a salary. Somebody starts to work in a company and over time the person enjoys a salary raise, or somebody is paid less.
In the table below we bring together key elements. The ID of the employee, the hourly rate (in this case people get paid per working hour) and the date until the salary is valid or no date at all. No date at all means that the salary is valid for Today() .
In the name column of the table we use the function below. Working with a blank is an easy way to get the most recent value assuming that the table is filled out well, meaning the dates are in the right order.
All you have to do is to set up a filter like below:
This works fast as long as we look for the most recent one and in most cases this approach is sufficient.
What if we want to have the value of any given date for example to calculate the salary at a certain point last year? Than the last value is no longer of use, because that is the value for today. We have to evaluate each date in this least and relate it to the date we check for.
My suggestion below looks like the table I used in the community to help one out with a number range. However in this case we have one number value, the Until column (dates are numbers as you know) and not two, but since dates are numbers as well we can use logical operators like greater or smaller than and that is exactly what we gonna do.
5 Steps
In short, we blend a Filter with FormulaMap() and Find() logic to get the job done.
We start with the base filter in step 01, nothing fancy, but an very important foundation we need back again in the end. I already put the WithName() in place, but it does nothing yet, it only provides a visual bridge to step02
Next we add an evaluation to check if the date we have living on this row is smaller than any of the dates in the table. We evaluate each date via FormulaMap() which receives its consecutive positions 1, 2, 3, 4 via the virtual index we generate with Sequence() and inject into the Nth() via the Currentvalue
In step 3 we look for the first ‘false’, thus for the first time the evaluation is false, this is the position in the list we need to find the value we are looking for since dates are end dates (until). To get this position we use Find() I wrote earlier about.
The output are numbers, as many as there are items in the list we evaluate, in our case thus 4 numbers. Step4: we only need one number, so we add First() and this number we use in the last part of our function´when we ask for the item that lives on the position we found.
In step05 we use the first step again, the filtered outcome to ask for the hourly compensation per position we found.
This is the heavier and complete approach. If you start with Coda the first provided option (Filter — IsBlank() is great, it is an easy and does the job well. Once you want to do more with the data living at your fingertips, you can use this alternative that confirms what we often say: In Coda everything is a list.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
