From: Michael Brand <michael.ch.brand@gmail.com>
To: Martin Gross <m-gross@gmx.net>
Cc: Org Mode <emacs-orgmode@gnu.org>
Subject: Re: sum up variables from different org-mode tables
Date: Fri, 16 Nov 2012 16:16:48 +0100 [thread overview]
Message-ID: <CALn3zoi4oyakLiKNshvqzmO8os6LySEaSPPn2Rg8x2L3Rrr80g@mail.gmail.com> (raw)
In-Reply-To: <CAJkULmktbtUYOnZbhf_VLbhHoh8fiJca_6uOFK2qr7nDT0O+8Q@mail.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
next prev parent reply other threads:[~2012-11-16 15:16 UTC|newest]
Thread overview: 9+ messages / expand[flat|nested] mbox.gz Atom feed top
2012-11-16 11:33 sum up variables from different org-mode tables Martin Gross
2012-11-16 15:16 ` Michael Brand [this message]
2012-11-19 11:54 ` Martin Gross
2012-11-19 13:50 ` Michael Brand
2012-11-21 17:01 ` Martin Gross
2012-11-21 18:46 ` Suvayu Ali
2012-11-19 21:21 ` Eric Schulte
2012-11-22 10:03 ` Martin Gross
2014-01-04 15:29 ` Michael Brand
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=CALn3zoi4oyakLiKNshvqzmO8os6LySEaSPPn2Rg8x2L3Rrr80g@mail.gmail.com \
--to=michael.ch.brand@gmail.com \
--cc=emacs-orgmode@gnu.org \
--cc=m-gross@gmx.net \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).