How to create a formula to show accumulated amount per account

Ask for Help
6

UPDATE 2024: There is now a simpler way, see How to create a formula to show accumulated amount per account - #11 by dmitry-grist!

Hi Garret, this is indeed tricky, but I believe exploring this example will help: Cumulative - Efficient - Grist.

Everything is on one screen: on the left is a table of transactions for a couple different accounts, sorted by date. On the right is a summary (grouped by account) and transactions for the account selected in the summary.

The key to this approach is in using the sortedcontainers library which allows for more efficient operations with ordered lists. Three formulas are involved:

  1. In the summary table, a SortedKeyList of transactions is created for each account, sorting by Date, disambiguated using manualSort. (The whole extra class definition of SList is unfortunate, but addresses a subtle issue: that SortedKeyLists must never appear equal to Grist when they aren’t in fact identical. It also produces a more concise representation.)
    from sortedcontainers import SortedKeyList
class SList(SortedKeyList):
  def __eq__(self, other): return self is other
  def __repr__(self): return "SList<{}, #{}>".format(len(self), id(self))

SList($group, key=lambda r: (r.Date, r.manualSort))
  2. In the Transactions table, we construct a reference to the “Previous” transaction, according to that sort order, within the current account. The fact that lst is a SortedKeyList makes the operation many times faster than it would be with regular lists:
     lst = Transactions_summary_Account.lookupOne(Account=$Account).Sorted_Transactions
 index = lst.index(rec)
 return lst[index - 1] if index > 0 else None
  3. Once the notion of “Previous” is available, the formula for cumulative sum is easy:
    ($Previous.Cumulative or 0) + $Amount

In reality, it’s similar to @jperon’s solution, with two optimizations: the lookupRecords and the sorting happens once per account, in the summary table (rather than for each transaction), and the lookup in the sorted list (which is done for each transaction) is extra efficient (sublinear) thanks to using the sortedcontainers library.

Give it a shot, I think it should work efficiently for large tables.

3 Likes