Re: sum up variables from different org-mode tables

[Michael Brand <michael.ch.brand@gmail.com>]

Hi Martin

On Fri, Nov 16, 2012 at 12:33 PM, Martin Gross <m-gross@gmx.net> wrote:
> Now I would like to get some statistics that consider all institutions
> together.  For example summing up the "variable" TAE from all the
> tables in the file to get the very total cost of outgoings.

Below are two approaches to achieve this. For future development I
would like to point out two things:

1) Assignment of empty field: I think that, generally and not only for
   your example, some existing or future format specifier should take
   care to not fill the destination cell with 0 when the source cell
   is empty. The format specifiers E and L how they work now do not
   help in this case. Sometimes I use a workaround like
   $3 = if($1 && $2, $1 + $2, string(""))
   which works only for non-zero numbers.

2) Indirection of remote table name: Although there is a very nice
   formula editor C-c ' (org-table-edit-formulas) which helps a lot in
   this case, in both variants editing of the total formula scales
   badly with the number of tables. A nice solution for variant 2
   would be if
   @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B, @>>$$#)
   could be simplified to
   @I$3..@II$7 = remote($8, @>>$$#)

Your example with the two approaches:

* Institution A
  #+TBLNAME: A
  |   | In      |  # |   € | Out     |  # |   € |
  |---+---------+----+-----+---------+----+-----|
  |   | Title P |  1 |  45 | Title A |  1 |  15 |
  |   |         |    |     | Title B |  2 |  28 |
  |---+---------+----+-----+---------+----+-----|
  | # |         |  1 |  45 |         |  3 |  43 |
  | ^ |         | TE | TEE |         | TA | TAE |
  #+TBLFM: $TE=vsum(@I..@II)::$TEE=vsum(@I..@II)::$TA=vsum(@I..@II)::$TAE=vsum(@I..@II)

* Institution B
  #+TBLNAME: B
  |   | In      |  # |   € | Out     |  # |   € |
  |---+---------+----+-----+---------+----+-----|
  |   | Title Q |  1 |  24 | Title C |  2 |  31 |
  |---+---------+----+-----+---------+----+-----|
  | # |         |  1 |  24 |         |  2 |  31 |
  | ^ |         | TE | TEE |         | TA | TAE |
  #+TBLFM: $TE=vsum(@I..@II)::$TEE=vsum(@I..@II)::$TA=vsum(@I..@II)::$TAE=vsum(@I..@II)

* total variant 1
  |   | In | #  | €   | Out | #  | €   |
  |---+----+----+-----+-----+----+-----|
  | # |    | 2  | 69  |     | 5  | 74  |
  | ^ |    | TE | TEE |     | TA | TAE |
  #+TBLFM: $TE = remote(A, $TE) + remote(B, $TE) :: $TEE = remote(A,
$TEE) + remote(B, $TEE) :: $TA = remote(A, $TA) + remote(B, $TA) ::
$TAE = remote(A, $TAE) + remote(B, $TAE)

* total variant 2
  |   | In |  # |   € | Out |  # |   € | institution |
  |---+----+----+-----+-----+----+-----+-------------|
  |   |    |  1 |  45 |   0 |  3 |  43 | A           |
  |   |    |  1 |  24 |   0 |  2 |  31 | B           |
  |---+----+----+-----+-----+----+-----+-------------|
  | # |    |  2 |  69 |     |  5 |  74 |             |
  | ^ |    | TE | TEE |     | TA | TAE |             |
  #+TBLFM: @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B,
@>>$$#) :: $TE=vsum(@I..@II) :: $TEE=vsum(@I..@II) ::
$TA=vsum(@I..@II) :: $TAE=vsum(@I..@II)

“$$#”: the first “$” is for “column” and “$#” is for the number of the
current column to copy a row column by column, just like the
equivalent “@@#” mentioned in “3.5.1 References" in the manual.

Michael