Preceding values in Coda
Why date time data is essential in your set up
In the community every once in a while questions surface on how to get the previous row value to calculate with. Mostly the questioner simplifies the question to get an answer in its simplest form and that means in many cases stripped from the original date time relation. As a result we introduce a virtual index, we blend sort, filter and make it work. I wrote about the issue as you can read below and here as well.
In the context of a spreadsheet, the “previous row” refers to the row that immediately precedes the current row. For example, if you are on cell A2 in a spreadsheet, the previous row is row A1. The previous row can be used to reference cells or to calculate values. In Coda we do things differently.
My central argument
In this blog I’ll state that without a proper date time notation, you should not set up a ‘previous row’ logic. The main reason is that previous not only means the row before this row in this sorting, but also the value related to a previous moment. To understand why I mention Sort() have a look here:
Interest rates
Spreadsheet addicts are used to a previous row logic to handle (compound) interest. In Coda we solve interest related puzzles differently. In the blog below I offer a solution for a standard interest related calculation and for the NVP logic, which is the mirrored value of compound interest rates.
In the context of interests the concreate years are replaced by year 3 or something like 10 years from now. That is fine, you don’t need a specific date in this context. The power function handles the year values without the need for a date time stamp.
Date Time Stamps
Having parked the interest discussion, I believe we should leave behind any set up in which we want to calculate preceding values (depending on the sorting of course) unrelated to a date time value. The sorted row index often written as below is duct tape at best.
thisTable.Find(thisRow)It shows (finds) the relative position of a row in a table. When you sort your table, your index resorts. For many this looks like a messed up view.
We continue with Filter() and we try to avoid ForEach() — previously known as FormulaMap(). ‘For each item in this list’ implies we have to evaluate each item in the list and the outcome of this operation is the same list a bit different. This is an expensive operation in terms of computing power.
In my blog from early 2021 I used ForEach(). Today we apply a filter based logic instead.
You can interpret this as the fruits of advancing insight over time. I am explicit about it to avoid the impression that I may have always know this and simply forgot writing about it. That is not the case. It happens I have to update my understanding. This is what I call progression and as you know progress, not perfection.
Step01 — the previous row to get the delta
We filter on the time slots. In this scenario it is rather easy because there is no spill over to the next day. This second scenario we pick up later on looking at a Night Line. First the easy part. We apply as promised a filter and we take the last item of that list. Below you see how that goes. We compare time slots.
The next step is to calculate with this preceding value. It is a simple subtraction. The key in this set up is the filter in which we ask for smaller than. Voila, this is step one.
Step 02— defining the driving periods
In Europe we have strict rules for professional drivers in buses and trucks. You can drive max 4h30 without a break. Between two periods of 4h30 you need a break of at least 45 minutes, but you can split this break in two (15m and 30 min). When I work with these numbers it feels off. When I am driving between Amsterdam & Ghent or Brussels it is about 2h30 and I am always glad to park the car. Imagine two hours extra and that two times a day. That is quite something. Anyway, a planner in a bus company deals with the numbers as I do.
They know the drill, bring in the P1 (short break) early to allow the driver to have 4h30 available, which she may need in case of a traffic jam. Most trips are planned at 4 hours max.
The set up below prepares for checking the time between breaks.
There are a few steps to make. You can check these in the example doc.
- We isolate the first period
- We isolate the breaks
- We calculate the other periods by using the number attached the to period (like driving period 4). Since we already have number one attributed to the first period, we add one.
The essence is that we Filter(), no ForEach(), no Sequence(), no Find(), but mainly Filter. That is possible because we have the numbers already in our table represented by the time slots.
Step 03 — defining the driving time per period
Below the code snippet and the complete table to get the time per period. It shows why we need the period. We integrate the condition ‘Driving’ to filter only on driving time and since border crossings are a bit special, we filter them out as well.
The reason border crossings are on the list is related to an European legal requirement when companies active in public cross border people transport ask for permission to produce a line. On every international line you have this element.
Spill over effect
It happens that a bus trip ends the next day. Nightlines are a good example. A bus leaves at 19h40 and arrives at 8h35. The passengers (also named Pax) slept during the night and wake up at the destination. We have a look at the line below. It is a line between Mons (Belgium) and Hamburg (Germany).
In the case a bus leaves before midnight and arrives after midnight, the outcome is what you see below.
This is not what we want and we can solve the issue rather easy and maybe a bit unexpected as well.
It may appear unexpected, but it suffices to understand that you can add days to date time values using numbers (like 1, 2 or 3). You cannot add years or months without the use of functions like Year() and RelativeMonth(). The number one you see represents one day or 24 hours. This gives us the desired outcome.
Some rows don’t have time values because these stops are inactive. In that scenario we add ‘ — ‘. In the other scenarios we filter on the precding value. Important is that your stort your table before you ask for more.
Spreadsheets have previous rows, in Coda we have preceding values.
The alternative in this tables is that you add hardcoded a date and you take that into consideration. Thus Feb 2, 2222 until midnight and Feb 3, 2222 after midnight. In that scenario the issue is solved for you. We call this ‘bricolage’, but it works and one extra column may not hurt. The issue is only that you easily make manuel mistakes and that is the main reason to avoid it. We stick to the set up we have shown. The result you see below.
Conclusion
Interest calculations we solve with Power() and for other so called previous row logics, we apply filters based on date time based values to get the preceding value. When we work in tables that have times (without a date value related to it) we add the 1 day. When you have finished reading this blog, you will always pay attention to the date time stamps in your tables as numbers helping you to get the preceding value.
My name is Christiaan and blog about Coda. Since the summer of 2023 mainly about how to Coda with AI to support organisations dealing with texts and templates. Why I focus on Coda AI you can read here: ⤵️
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. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
