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 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 Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Consultant & Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
