--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/dtOply6B8xQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20181101190147.75d3a224xwe7gi2d%40upsilon.cc.
For more options, visit https://groups.google.com/d/optout.
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 beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAOJ%2BOb07re2AcWdYxQqUOTBkiJKm8kX1CpfprifFyF3Fq%2BPUaA%40mail.gmail.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNwjYNXXszXT7%3DkzpQktFZSbTGnTcpTraDVG_H%3D0WUFXg%40mail.gmail.com.
2018-02-09 * "Lots of expenses"
Assets:Checking
Expenses:Gas 40 USD
Expenses:Groceries 20 USD
2018-02-01 * "Groceries"
Assets:Checking
Expenses:Groceries 20 USD
2018-02-01 * "Gas"
Assets:Checking
Expenses:Gas 40 USD
beancount> select account, sum(position), last(balance) where account ~ 'Expenses'
account sum_po last_ba
------------------ ------ -------
Expenses:Groceries 40 USD 120 USD
Expenses:Gas 80 USD 100 USD
beancount> select account, position, balance where account ~ 'Expenses'
account positi balance
------------------ ------ -------
Expenses:Groceries 20 USD 20 USD
Expenses:Gas 40 USD 60 USD
Expenses:Gas 40 USD 100 USD
Expenses:Groceries 20 USD 120 USD
There is a catch though. When there are multiple transactions on the same day, last(balance) may give seemingly meaningless sums:
2018-02-09 * "Lots of expenses"
Assets:Checking
Expenses:Gas 40 USD
Expenses:Groceries 20 USD
2018-02-01 * "Groceries"
Assets:Checking
Expenses:Groceries 20 USD
2018-02-01 * "Gas"
Assets:Checking
Expenses:Gas 40 USD
beancount> select account, sum(position), last(balance) where account ~ 'Expenses'
account sum_po last_ba
------------------ ------ -------
Expenses:Groceries 40 USD 120 USD
Expenses:Gas 80 USD 100 USDThe reason seems to be that order matters for last(balance). Now the actual total is still there - its the maximum value in the last_balance column. But it is not useful for a running total of aggregates - especially if you add ORDER BYs.
I works fine for non-aggregate queries:
beancount> select account, position, balance where account ~ 'Expenses'
account positi balance
------------------ ------ -------
Expenses:Groceries 20 USD 20 USD
Expenses:Gas 40 USD 60 USD
Expenses:Gas 40 USD 100 USD
Expenses:Groceries 20 USD 120 USDI personally would love a "total" displayed as an additional line at the very end (kind of like ledger does) for balances.It would get tricky if there are multiple projected columns. Perhaps that can be specified with an extension to the query language: (SELECT .. TOTAL 1, 2) to total the final output of columns 1 & 2. Or it can be done automatically for any non-text column with only one currency. There might be other caveats/issues.
Alternatively, 'balance' can be computed on the final output of the query rather than per posting before aggregation.
- Shreedhar
On Thursday, November 1, 2018 at 1:49:36 PM UTC-5, ethan.gla...@gmail.com wrote:I was wondering if it's possible to build some kind of bean-query that would produce output like the fava "Balance Sheet" view does. If you haven't seen it, it looks like this: https://fava.pythonanywhere.com/example-beancount-file/balance_sheet/ . It shows the "net worth" of the beancount file over time.You can get it per-account using `BALANCES AT COST FROM date < 2018-09-05`. And using `EXPLAIN`, I was able to turn that into `SELECT sum(cost(position)) WHERE account ~ '(Liabilities|Assets):.*' and date < 2018-09-05`, which works great for a single point in time. Finally I was calculate to see month-to-month changes using `SELECT year(date), month(date), sum(cost(position)) WHERE account ~ '(Liabilities|Assets):.*'`. But I don't have any way to produce a running total (the sum of those changes up to each month).Is there a bean-query mechanism for doing accumulation or "running count" operations? I could "roll my own" using a subquery but I see from the documentation that sub-selects aren't supported.Thanks!Ethan
--
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 beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/7ac2a4fd-af4d-465f-aed9-b8e62e26d7ab%40googlegroups.com.
On Wed, Nov 14, 2018 at 12:13 AM <shreedha...@gmail.com> wrote:There is a catch though. When there are multiple transactions on the same day, last(balance) may give seemingly meaningless sums:
2018-02-09 * "Lots of expenses"
Assets:Checking
Expenses:Gas 40 USD
Expenses:Groceries 20 USD
2018-02-01 * "Groceries"
Assets:Checking
Expenses:Groceries 20 USD
2018-02-01 * "Gas"
Assets:Checking
Expenses:Gas 40 USD
beancount> select account, sum(position), last(balance) where account ~ 'Expenses'
account sum_po last_ba
------------------ ------ -------
Expenses:Groceries 40 USD 120 USD
Expenses:Gas 80 USD 100 USDThe reason seems to be that order matters for last(balance). Now the actual total is still there - its the maximum value in the last_balance column. But it is not useful for a running total of aggregates - especially if you add ORDER BYs.Interesting. Indeed you're correct. I've never come across this issue.Maybe what's needed is not an automatically generated column, but a feature (a function?) at the SQL level which refers to another column, computing a running sum, something like this:> select account, sum(position) as pos, ACCUMULATE(pos) where ...Something like that.I wonder if there's precedent for something like this in some variant of SQL.
Another idea would be to provide the value for the previous column, so you could do> select account, sum(position) as pos, (prev.balance + pos) as balance where ...
I works fine for non-aggregate queries:
beancount> select account, position, balance where account ~ 'Expenses'
account positi balance
------------------ ------ -------
Expenses:Groceries 20 USD 20 USD
Expenses:Gas 40 USD 60 USD
Expenses:Gas 40 USD 100 USD
Expenses:Groceries 20 USD 120 USDI personally would love a "total" displayed as an additional line at the very end (kind of like ledger does) for balances.It would get tricky if there are multiple projected columns. Perhaps that can be specified with an extension to the query language: (SELECT .. TOTAL 1, 2) to total the final output of columns 1 & 2. Or it can be done automatically for any non-text column with only one currency. There might be other caveats/issues.
Alternatively, 'balance' can be computed on the final output of the query rather than per posting before aggregation.This would definitely be an extension, e.g., your TOTAL idea.
Yeah. I was thinking of it as a short hand for the window function with the 1,2 being the grouping/partition-by columns. But honestly, window functions are the most flexible and powerful.