Hi Martin
On Fri, Nov 16, 2012 at 12:33 PM, Martin Gross <[email protected]> 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
