Re: table formula help...

[Thierry Banel <tbanelwebmin@free.fr>]

Le 10/12/2014 22:06, Michael Brand a écrit :
> Hi Thierry
>
> On Tue, Dec 9, 2014 at 11:35 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>
>> 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.

My mistake.Fixed to:
  "Empty value is returned when not enough non-empty input is available"

Thanks

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

To further process the aggregations?

> #+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
>

Well... Many different topics here.First of all, empty input & empty
output are unrelated. So let us take them one at a time.

* Input
In the spreadsheet formulas, we have those modifiers:
- E = keep empty fields when counting input vector size.
- N = replace non-numbers (including empties) by zero.

The first version of orgaggregate behaved as thought it had the EN
modifiers.The latest version behaves as thought it had no modifiers at all.

For the shake of consistency, it would be nice to have specifiers in
orgaggregate. E & N, of course, but also p7 (precision 7 digits), %.3
(three decimal places after dot), F (fraction), and so on.

* Output
If a function is able to compute a result, then it should give it. If
for any reason it is not able to compute a result, it should tell it in
some way. Right now, orgaggregate signals problems by leaving a blank
output. But it could be anything else: nan, NA, #ERROR, vmean([]), 1/0,
whatever.

The =sum= aggregation is always able to return a value, even for a zero
length input vector, in which case the sum is zero. On zero length
vectors, =prod= gives one. The =mean= aggregation has a hard time
telling what is the mean of a zero length input vector.

Here is how the spreadsheet handles those cases (without modifiers):
  |   |   |   | sum |      mean | prod |
  |---+---+---+-----+-----------+------|
  | 1 | 2 | 3 |   6 |         2 |    6 |
  |   | 2 | 3 |   5 | 1.6666667 |    6 |
  |   |   | 3 |   3 |         1 |    3 |
  |   |   |   |   0 |         0 |    1 | <--- see
  #+TBLFM: $4=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)

This is correct. Orgaggregate should behave in a similar way.
Fortunately in its latest version it does.

* Summary
Modifiers are lacking in orgaggregate for it to be fully consistent with
the spreadsheet. If someone knowns how to add them easily...

Regards
Thierry