Hi Chary, I'll start with a disclaimer that I don't have a perfect understanding of the "balance" operation intuition. It is useful but it seems it indeed has limitations and in particular it doesn't work as expected with GROUP BY statements. I think I have seen this mentioned explicitly but couldn't find now, at least in the official docs here: https://beancount.github.io/docs/beancount_query_language.html, only a hint "Access to the previous row is not a standard SQL feature" suggests that it might not work as expected.
In your example the issue is not that it doesn't respect the WHERE filter (I think it does) but that it aggregates balance between different accounts. That will become more obvious if you change 1000 EUR to different amounts (say, 500, 600 and 700) in your example transactions. The partial solution to this is to explicitly use account ~ 'Assets:Bank2' in filter. This allows to get total balances for the particular account or the group of accounts that you specify in the filter. For example, in this case just removing "account" from SELECT will make the query calculate correctly the net worth across all Assets+Liabilities. However, I'll also add that at very least the output of the query (when it includes "SELECT account") seems counter-intuitive to me, if not incorrect. Not sure if there is at least a way to display a warning if some groupping is about to happen that is not related to date groupping (I'm not sure about balances inner workings but it seems it's optimised for that). P.S. If you choose to try a query for reporting periodic balances, like the one I've sent in the neighbouring thread or the one from the fava-dashboards repo example, you'll also likely to come across another unrelated issue: https://github.com/andreasgerstmayr/fava-dashboards/issues/28#issuecomment-2573181426 that the rows will not be generated unless there's at least one transaction present in the time period. Leaving it here just in case. Best, Vasily On Tuesday, March 25, 2025 at 12:17:19 PM UTC+1 Chary Ev2geny wrote: > Dear all, > > I must I still struggle to find a way to use balance to calculate the net > worth on certain date (e,g, 2020-12-31 for my example from this thread) > > It looks like the *balance *field does not respect the WHERE filter. > > In this case, what is the purpose of the balance field all together? > > > On Sunday, June 2, 2024 at 2:29:23 PM UTC+2 Chary Ev2geny wrote: > >> Dan, >> >> is it possible to get the result as in the 1st query, but using the *balance >> *? >> >> account amount (EUR) >> Assets:Bank1 1000.0 >> Assets:Bank2 1000.0 >> >> On Sunday, June 2, 2024 at 12:36:23 AM UTC+2 [email protected] wrote: >> >>> On 01/06/24 21:27, Chary Chary wrote: >>> > I am still trying to find the way to calculate the net worth at a >>> > certain date using *balance *field (I am not sure it will give me any >>> > advantage) >>> > >>> > But somehow it does not quire work as I expect >>> > >>> > E.g. >>> > >>> > ledger_text = """ >>> > >>> > 2020-01-01 open Assets:Bank1 >>> > 2020-01-01 open Assets:Bank2 >>> > 2020-01-01 open Income:Salary >>> > >>> > 2020-01-01 * "Salary 1" >>> > Income:Salary >>> > Assets:Bank1 1000 EUR >>> > >>> > 2020-01-20 * "Salary 2" >>> > Income:Salary >>> > Assets:Bank2 1000 EUR >>> > >>> > 2021-01-20 * "Salary 3" >>> > Income:Salary >>> > Assets:Bank2 1000 EUR >>> > """ >>> > >>> > date_iso = datetime.date(2020,12,31).isoformat() >>> > >>> > query=f""" >>> > select account, SUM(position) as amount >>> > where date <= {date_iso}AND account ~ 'Assets|Liabilities' >>> > """ >>> > >>> > result: >>> > >>> > account amount (EUR) >>> > Assets:Bank1 1000.0 >>> > Assets:Bank2 1000.0 >>> > >>> > >>> > This is correct!! >>> > Now using balance field >>> > query=f""" >>> > select account, LAST(balance) as amount >>> > where date <= {date_iso}AND account ~ 'Assets|Liabilities' >>> > """ >>> > account amount (EUR) >>> > Assets:Bank1 1000.0 >>> > Assets:Bank2 2000.0 <==== This is incorrect! >>> > So, what am I doing wrong? >>> >>> AFAICT, both queries do what they are expected to do. >>> >>> Cheers, >>> Dan >>> >>> -- You received this message because you are subscribed to the Google Groups "Beancount" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/beancount/a56879e2-6416-4639-b643-b595db3aabf5n%40googlegroups.com.
