How do I SUM the earlier entries?

Get Help
1

I am new to Fibery and loving it so far, I am using this for managing my bank statements so far.

I have a DB which has all the statement like this in table view

Date | Statement Name / Reason | Type | Amount | Account | Balance |
12-12-2020 | Food | DR | $10 | Personal | $500 |
12-12-2020 | Chair | DR | $100 | Personal | $400 |

  1. The problem is, I want to calculate the Balance using function, Where it should take calculate from all expenses and income earlier and tell me the correct remaining balance in that particular account.

  2. I do not have time information in that date, So It should be sorted as per the “Earliest entry first” in that.

  3. I have accounts db, Where I have 5 difference accounts, So balance should be calculated by that.

I don’t know if I am asking right things, I hope community can help?!

2

Given that the entries in a database can be sorted by a variety of possible parameters (Creation Date, Name (alphabetically), Amount, etc. how do you define the ‘earliest entry’, if there is no time included in the Date field?
If you’re using the creation date order a suitable formula might be something like this:

Account.[Statement entry].Filter(Date <= [This Statement entry].Date AND [Creation Date] < [This Statement entry].[Creation Date]).Sum(Amount)

Note: I’m not sure why you want to calculate the balance in Fibery, given that your example data already includes the balance :person_shrugging:

3

This works, I did minor tweaking and here what it looks like

Account.[Account Statements]
.Filter(
Type.Name = “CR” and
[Transaction Date] <= [This Statements].[Transaction Date]
)
.Sum([Net Amount]) -
Account.[Account Statements]
.Filter(
Type.Name = “DR” and
[Transaction Date] <= [This Statements].[Transaction Date]
)
.Sum([Net Amount])

Not all accounts are from bank, Some has cash deposits, That requires manual entries and some has different tax slabs and stuff, So wanted know remaining balance of each account after an transaction. Thank you so much, This helped.

4

You might need to be careful with that formula, if there can be multiple entities which have the same Transaction Date.

For example, with these entries

04-Sep-2024 +$3
04-Sep-2024 +$5
04-Sep-2024 -$7

The balance will probably show as +$1 for all three of them