From mboxrd@z Thu Jan  1 00:00:00 1970
From: Michael Brand <michael.ch.brand@gmail.com>
Subject: Re: table formula help...
Date: Wed, 10 Dec 2014 22:06:59 +0100
Message-ID: <CALn3zog0eocBkU3iJeYehMfQXx6DVDRCYAe=C6bbQN_=Qgjovg@mail.gmail.com>
References: <87ppbxfi5f.fsf@ericabrahamsen.net>
	<CALn3zogy-J5cSHUgOHmxpN2UH=g54QcU2=pqyAMGgMf1Pcd6Jg@mail.gmail.com>
	<54838805.8090705@free.fr> <8761do6t01.fsf@ericabrahamsen.net>
	<CALn3zoj-7zhTNtiE+TsZrxi2ibuQUe=vJX-_UBOiENZ8hf69Aw@mail.gmail.com>
	<5484CD5C.9070604@free.fr>
	<CALn3zoja2+16+bXkSi9psyS1Lj0q-L7Q2xD+TJTYYsGfkkhvHg@mail.gmail.com>
	<5487471D.5070807@free.fr> <5487793C.1080800@free.fr>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Return-path: <emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org>
Received: from eggs.gnu.org ([2001:4830:134:3::10]:54422)
	by lists.gnu.org with esmtp (Exim 4.71)
	(envelope-from <michael.ch.brand@gmail.com>) id 1XyoTO-0005eU-M3
	for emacs-orgmode@gnu.org; Wed, 10 Dec 2014 16:07:03 -0500
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71)
	(envelope-from <michael.ch.brand@gmail.com>) id 1XyoTM-0003S8-H4
	for emacs-orgmode@gnu.org; Wed, 10 Dec 2014 16:07:02 -0500
Received: from mail-qa0-x235.google.com ([2607:f8b0:400d:c00::235]:39797)
	by eggs.gnu.org with esmtp (Exim 4.71)
	(envelope-from <michael.ch.brand@gmail.com>) id 1XyoTM-0003S0-CH
	for emacs-orgmode@gnu.org; Wed, 10 Dec 2014 16:07:00 -0500
Received: by mail-qa0-f53.google.com with SMTP id bm13so2598681qab.40
	for <emacs-orgmode@gnu.org>; Wed, 10 Dec 2014 13:06:59 -0800 (PST)
In-Reply-To: <5487793C.1080800@free.fr>
List-Id: "General discussions about Org-mode." <emacs-orgmode.gnu.org>
List-Unsubscribe: <https://lists.gnu.org/mailman/options/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/emacs-orgmode>
List-Post: <mailto:emacs-orgmode@gnu.org>
List-Help: <mailto:emacs-orgmode-request@gnu.org?subject=help>
List-Subscribe: <https://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=subscribe>
Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
To: Thierry Banel <tbanelwebmin@free.fr>
Cc: Org Mode <emacs-orgmode@gnu.org>

Hi Thierry

On Tue, Dec 9, 2014 at 11:35 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> A clean design has been implemented for handling empty cells.

Very good.

> On output, empty cells are generated when the aggregation function does
> not have enough input. For instance, =mean= needs at least one value,
> otherwise a division by zero happens.

The above "not enough input" contradicts with "no input" from the
docstring of orgtbl-aggregate-apply-calc-1arg-function:

    Empty value is returned when all input values are empty.

If this function would follow its docstring by having "(if (cdr vec)"
also for sum, min, max and prod then the user could benefit from
adding "E" and/or "N" or not in the mode string of the TBLFM:

#+TBLNAME: test
| Item | Value_1 | Value_2 |
|------+---------+---------|
| a    |       2 |         |
| a    |       2 |       2 |
| b    |       2 |       2 |
| b    |         |       2 |
| c    |         |       2 |
| c    |         |       2 |
| d    |         |       1 |
| d    |         |      -1 |

#+BEGIN: aggregate :table test :cols ("Item" "sum(Value_1)" "sum(Value_2)")
| Item | sum(Value_1) | sum(Value_2) |   |   |    |
|------+--------------+--------------+---+---+----|
| a    |            4 |            2 | 3 | 3 | >  |
| b    |            2 |            4 | 3 | 3 | <  |
| c    |              |            4 | 4 | 2 | NA |
| d    |              |            0 | 0 | 0 | NA |
#+TBLFM: $4 = vmean($2..$3) :: $5 = vmean($2..$3); EN :: $6 = if("$2"
== "nan" || "$3" == "nan", string("NA"), if($2 > $3, string(">"),
if($2 < $3, string("<"), string("eq")))); E
#+END

The current orgaggregate for comparison:

| Item | sum(Value_1) | sum(Value_2) |   |   |    |
|------+--------------+--------------+---+---+----|
| a    |            4 |            2 | 3 | 3 | >  |
| b    |            2 |            4 | 3 | 3 | <  |
| c    |            0 |            4 | 2 | 2 | <  |
| d    |            0 |            0 | 0 | 0 | eq |

One could still get the current behavior by adding the column formula
~$2 = if("$2" == "nan", 0, $0); E~ etc. for the aggregated columns.

What do you think?

Michael