How to index a broken list in Coda
When your data stream is irregular
You may have noticed that once in a while I share on Twitter the evolution of the diesel price in Belgium. This price is an important variable to calculate the rentability of people transport besides for example the driver costs, the expected repair costs on certain trajets and so forth.
The fuel costs changes in Belgium with irregular intervals. The government dictates the max fuel price, almost like in a communist state. They publish these max prices on week days, but not every day there is a change. It happens that the max diesel price remains unchanged for a week.
Our source is an excel sheet that contains the dates and sometimes a value, like you see below. The dates are also irregular as well.
I created a button that translates these values into values Coda understands. Commas got replaced by dots and the date is rewritten to the USA format. Both are necessary for proper calculations. In my experience any other date type than the USA format may break.
Anyway we end up with a cleaned list as you see below.
By putting a table filter on it, we only show the diesel prices.
What we want is the delta between each price. We need two functions: an index and a calculation related to previous row. I wrote about the previous row logic in the blog below and argued that in the context of stock management this is not the way forward. You better use buttons. However in this context it is useful.
We set up two calculations to get the delta.
The diesel index
The first step is to get the positon of each row in the table and as you see our indexDiesel shows the same numbers as the light grey numbers in front of this filtered table. In the index we apply the same filter we applied on table level and we sort using False() to get the most recent value on top, this is the same sorting as I applied on the table as well. Put differently I imitate the filter and sorting as applied on the table to get the view you see below.
It is a pity that we cannot reference these table settings for filtering & view directly. We can however indirectly via controls and us such create a more dynamic index construction. This is what I will show in the second part of the blog. We start with imitating the Table filter settings manually.
The Delta value
Once we have the dieselIndex, thus the index filtered as the table view we can make use of the logic I showed in the forementioned blog on previousRow.
It is not exactly the same, but the pattern is. We first filter as we have filtered the table view. The outcome we rename as Base. Second we count the base values and create a virtual index we use as currentvalue to distract the actual from the next one. We finish using Slice() and Last() to get the corresponding value per row.
To keep tables fast, we could add a button and print the delta value in an other table which we use as base table for graphs etc.
Bring controllers into play
We deal with two controlling elements in our function. One is sorting and one is that we only want to have items in the column Diesel that are not blank. As you know, items in a list can be blank as well as I explain in one of my most read blogs.
We start with the sorting. That is either true or false and a controller resembling this output or behavior is checkbox or a toggle. The same function, different look.
The second one is a filter Diesel. In this case we observe that the data structure (based on an excel sheet) fails us. We should be able to filter on Diesel, but that we cannot. We don’t have a column with fuel types containing Diesel and all the others, while we need this to relate to in a controller type lookup. Spreadsheets work different you remember. Unless I rework the table, our options are limited.
While controllers would be the best way to handle the creation of specific table views, it is only possible when your date set is defined well. This is not the case in my table. We have to stick with a manuel set up.
My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done.
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.
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.
