Hledger: Combining --pivot and --tree?

General
,
1

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.

2

--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 ?

3

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. :slight_smile:

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.

4

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
5

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.

6

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
1 Like
7

Forgot it can do that!

8

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