How to Coda a Rolling Total?
Using FormulaMap() and Sequence() to get the job done
About a year ago I wrote a blog about how to deal with a Previous Row logic. In this blog I present a different approach to tackle the same issue. Last year I assumed that Coda would solve the issue programmatically, but I am no longer convinced that this challenge is on their to-do list.
In the mentioned article I use Slice()
to get the job done in combination with the Row Index formula : thisTable.Find(thisRow)
to generate an ascending number for each row.
thisTable.Delta.slice(1,thisRow.RowIndex).Sum()
Although that works fine, the mentioned solution with FormulaMap()
has its benefits and merits of its own.
This morning I had to create a total for each month including previous months.
One of the benefits is getting acquainted with a logic that is applicable in various settings. Below is the solution I deployed in my blog a year ago to find the delta between two rows.
The above can be written a bit shorter using WithName()
and then you get:
However we can also use FormulaMap()
and Sequence().
We apply the logic and below you see a slightly longer formula.
The step 01 column contains the output without the function Last()
It shows all the items and as you see below it outputs the same results as the earlier used function.
The main reason that I would advice to go with the FormulaMap()
based solution is that it permits for more flexibility. Maybe you want to skip numbers or you want something else a bit of the normal. In that case the Sequence()
function helps you out.
I hope these examples inspire you to make use of the proposed suggestion and to explore the many creative paths opening up for you.
Interest Rates
Last but not least, do not use the method of previous rows to calculate interest rates. It suffices to apply the Power()
function. I wrote in this blog about interest rates.
Coda is a great tool to set up and maintain complex calculations. You avoid getting lost by looking for the right function in the rich Coda toolbox.
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.