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.
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
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.
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 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