Calculating a Running Sum

Get Help
1

Hi there!

I’m fairly new to Fibery but it seems like exactly what I’ve been looking for :slight_smile: Thank you everyone for everything you’ve done!!!

I’ve created a few formulas and I’m figuring Fibery out, but there’s one that I can’t crack - and when I search the “community” here, I can’t find it either.

One of the uses of Fibery I want to use is to keep track of all our financial transactions for our little non-profit. As part of that, I’m making a “Space” in which one “Database” is just a list of all our transactions (all money donated, and all money spent, with a timestamp for each.)

I basically want to have a field that is just a running tally of how much money for each row, based on the sum of all previous “donation” fields (up until and including that entry), subtracting all previous expenses from the “expenses” field (up to and including that entry).

The Excel equivalent to this is just the classic column where you add in the previous row’s number to a running total. Basically “first day of excel” haha.

As an example, if on the oldest date (first row, oldest timestamp), we have that the “donation” field got $10 in donations, and then in the second row (for the second-oldest timestamp) has in the “expenses” field an expense of $6… then, for the most recent of the two entries (the second one, with the $6 expense), the running tally field should say that we currently have $4 in our running balance.

I feel like in Fibery, this is probably done with “RunningSum” - but I can’t seem to crack the formula.

I’d love any leads or any help.

Thank you so much :slight_smile:

PS – In trying different formulas to try to figure out how to do this, I’m trying this formula to sum up all the transactions up until that point of that row:

[Dollars Received].Filter(Date <= [This TransactionsTracker].Date).Sum()

But it returns an error: Cannot call “Filter” method – but I can’t understand why.

morgan

2

When using formulas, you can only refer to fields of the entity and/or fields of related entities. Based on what you’ve described, it sounds like the entities in your database are not related to each other, so they don’t ‘know’ about the existence of other entities in the database that are dated before or after them.

Have you considered using a report view? These kind of calculations are exactly what it is suited to.

Otherwise, you would have to find a way to link all transactions to a common entity (maybe you want a database of accounts, and you can link each transaction to a single account).
Then you could use a formula like this:
Account.Transactions.Filter(Date < [This Transaction].Date).Sum([Dollars received])

3

Ahhhh! @Chr1sG This was very clarifying and helpful. I now understand what that didn’t work. Last night, after I wrote this message, I played with the reports and got a report to give the number I wanted, so that worked. Thank you very much :slight_smile: