Hledger: Combining --pivot and --tree?

jbrains · April 2, 2024, 1:36pm

I'm preparing reports for my accountant and I have found what looks like a limitation of hledger. I'm interested in knowing:

Am I using hledger incorrectly?
If this is a limitation of hledger, is it intentional?
If this feature is desired, how much effort would it be?

I have a tree of Expense accounts and the postings in those accounts are labeled with a significant tag. Now I want to combine the beauty of --pivot on balance amounts per tag value with the beauty of --tree in order to get both the balances per tag value of each subaccount and the rolled-up balance per tag value.

Based on what I've tried so far, if I use --pivot, then --tree appears to have no effect.

hledger --file FILE.journal balance --tree "Expenses:Residential Rental Properties" --pivot="rental_unit"

I get a balance report per rental_unit value, but only for the root account and not the subaccounts.

Workaround

I know that I can list the subaccounts, then run the --pivot report for each subaccount. I'm wondering whether I'm missing something, and if not whether this is intentional, and if not whether what I'm trying to do is something that hledger could feasibly support.

simonmic · April 2, 2024, 5:43pm

--pivot and --tree can work together like so:

2024-04-02
   (Expenses:Residential Rental Properties)   1  ; rental_unit:buildingA:apt1
   (Expenses:Residential Rental Properties)   1  ; rental_unit:buildingA:apt2

$ hledger balance "Expenses:Residential Rental Properties" --pivot rental_unit --tree
                   2  buildingA
                   1    apt1
                   1    apt2
--------------------
                   2

Is the problem that --pivot replaces the account name entirely, rather than appending to it as Ledger does ?

jbrains · April 23, 2024, 6:38pm

No, the problem was that there are effectively two trees: one consisting of subaccounts of Expenses:Residential Rental Properties and the other consisting of the values of the tag rental_unit. As far as I can tell, that means a Morton's Fork.

pivot on rental_unit, but then I can't see the subaccount balances per rental unit, but rather only the total in Residential Rental Properties.
tree on Expenses:Residential Rental Properties, but then I can't see the amounts by rental unit, but rather only the total balance in each subaccount for all rental units.

It makes perfect sense that hledger would behave like this, because the resulting report is a forest and not a tree, but I was hoping I had missed some magic somewhere.

An example to make it more concrete:

Expenses
- Residential Rental Properties
  - Management Fees
  - Ordinary Repairs
  - Electricity

Could I get a tree-style report showing the balance in each of those three subaccounts as well as the parent accounts also pivoted by rental unit tag value in a single command? I know that I could iterate over either the subaccount names or the rental unit tag values.

simonmic · April 23, 2024, 7:33pm

I couldn't quite get a handle on it so I made this example for myself:

2024-04-01
    Assets
    Expenses:Residential Rental Properties:Management Fees  1  ; rental_unit: buildingA:aptA1
    Expenses:Residential Rental Properties:Management Fees  1  ; rental_unit: buildingA:aptA2
    Expenses:Residential Rental Properties:Management Fees  1  ; rental_unit: buildingB:aptB1

2024-04-30
    Assets
    Expenses:Residential Rental Properties:Electricity  10  ; rental_unit: buildingA:aptA1
    Expenses:Residential Rental Properties:Electricity  12  ; rental_unit: buildingA:aptA2
    Expenses:Residential Rental Properties:Electricity  15  ; rental_unit: buildingB:aptB1

These are the separate reports:

~/src/hledger/2024-04-23$ hledger bal expenses -t -E --no-elide
                  40  Expenses
                  40    Residential Rental Properties
                  37      Electricity
                   3      Management Fees
--------------------
                  40  
~/src/hledger/2024-04-23$ hledger bal expenses -t -E --no-elide --pivot rental_unit
                  24  buildingA
                  11    aptA1
                  13    aptA2
                  16  buildingB
                  16    aptB1
--------------------
                  40

Is your wished-for combined report something like this ?

                  40  Expenses
                  40    Residential Rental Properties
                  37      Electricity
                  22        buildingA
                  10          aptA1
                  12          aptA2
                  15        buildingB
                  15          aptB1
                   3      Management Fees
                   2        buildingA
                   1          aptA1
                   1          aptA2
                   1        buildingB
                   1          aptB1
--------------------
                  40

simonmic · April 23, 2024, 7:42pm

PS here's how --pivot is supposed to work with Ledger; it seems to construct the new account name like this:

PIVOTEDACCT = TAGNAME:TAGVALUE:OLDACCT

Unfortunately I can't make it do anything with --pivot today, maybe a Ledger user can help.

glguy · April 23, 2024, 9:32pm

Are either of these closer to what's desired?

$ hledger -f rent.ledger bal --pivot "acct:rental_unit" --tree
                 -40  Assets:
                  40  Expenses:Residential Rental Properties
                  37    Electricity
                  22      buildingA
                  10        aptA1
                  12        aptA2
                  15      buildingB:aptB1
                   3    Management Fees
                   2      buildingA
                   1        aptA1
                   1        aptA2
                   1      buildingB:aptB1
--------------------
                   0
$ hledger -f rent.ledger bal --pivot "rental_unit:acct" --tree
                 -40    Assets
                  24  buildingA
                  11    aptA1:Expenses:Residential Rental Properties
                  10      Electricity
                   1      Management Fees
                  13    aptA2:Expenses:Residential Rental Properties
                  12      Electricity
                   1      Management Fees
                  16  buildingB:aptB1:Expenses:Residential Rental Properties
                  15    Electricity
                   1    Management Fees
--------------------
                   0

simonmic · April 23, 2024, 9:35pm

Forgot it can do that!

simonmic · April 23, 2024, 9:43pm

glguy solved the Ledger issue. Now I have added a space before the tag values, ledger's pivot looks like this:

~/src/hledger/2024-04-23$ ledger bal expenses --pivot rental_unit
                  40  rental_unit
                  24    buildingA
                  11      aptA1:Expenses:Residential Rental Properties
                  10        Electricity
                   1        Management Fees
                  13      aptA2:Expenses:Residential Rental Properties
                  12        Electricity
                   1        Management Fees
                  16    buildingB:aptB1:Expenses:Residential Rental Properties
                  15      Electricity
                   1      Management Fees
--------------------
                  40

jbrains · December 6, 2024, 2:21pm

I'm finally getting back to this and I appreciate you for giving me a near-perfect solution. The only downside to your command is that it includes the items not tagged with rental_unit. Fortunately, I tried the obvious fix and that worked. I added "tag:rental_unit" in order to select only the transactions with a value for that tag.

$ hledger --file $LEDGER_FILE incomestatement --pivot="rental_unit:acct" "tag:rental_unit"

This gave me a nice report with which I could optionally use --tree, depending on the format I wanted.

Many thanks!

jbrains · December 6, 2024, 2:32pm

Unfortunately, this seems to also match transactions with tags that contain this string. I presume the query is a (partial) regex match and not a full-text match.

For example, I have a tag named trip as well as Stripe-related tags with names like stripe_transaction_id, so when I run this report, it includes transactions with the Stripe-related tags.

$ hledger --file $LEDGER_FILE incomestatement --pivot="trip:acct" tag:trip --no-elide --tree

@simonmic, is this a defect in the product or the documentation?

I found a way to get the result I want without matching the unrelated transactions. At the very least, I believe this merits special mention in the documentation, because it makes sense, but maybe isn't obvious. Note the use of ^...$ for the tag query.

$ hledger --file $LEDGER_FILE incomestatement --pivot="trip:acct" "tag:^trip$" --no-elide --tree

simonmic · December 6, 2024, 5:13pm

It's mentioned generically at hledger's regular expressions, but you're right, tag: deserves more hints. I have added them, thanks for checking.
https://hledger.org/dev/hledger.html#tag-query