Re: [O] aggregate: percent, table headers, float format

[Thierry Banel]

On 28/06/2018 14:53, Uwe Brauer wrote:
> Hi
>
> Org-aggregate is great and very helpful.
>
> However I have some problems/questions which I think a maybe interesting
> for the list.
>
> Take the following code
>
>
> #+begin_src emacs-lisp
> (setq org-calc-default-modes '(calc-internal-prec 12 calc-float-format
>                     (fix 3)    ;;; the default here was (float 8)
>                     calc-angle-mode deg calc-prefer-frac nil 
> calc-symbolic-mode nil calc-date-format
>                     (YYYY "-" MM "-" DD " " Www
>                           (" " hh ":" mm))
>                     calc-display-working-message t))
>
> #+end_src
>
> Which sets the float format to (fix 3) is taken into account by the
> org-table but ignored by org aggregate as the example below shows.

Iwill look into that.

In the meantime you can use a format specification ;f3which means "fix 3
decimal positions".

#+BEGIN: aggregate :table "raw-data" :cols "CalJunio count() 
(count()/19)*100;f3"
| CalJunio | count() | (count()/19)*100;f3 |
|----------+---------+---------------------|
| AP       |       7 |              36.842 |
| NT       |       1 |               5.263 |
| SS       |       5 |              26.316 |
| NP       |       6 |              31.579 |
#+END:

>
> #+TBLNAME: raw-data
> | Number | CalJunio |
> |--------+----------|
> |      1 | AP       |
> |      2 | NT       |
> |      3 | SS       |
> |      4 | SS       |
> |      5 | SS       |
> |      6 | AP       |
> |      7 | SS       |
> |      8 | NP       |
> |      9 | AP       |
> |     10 | NP       |
> |     11 | NP       |
> |     12 | AP       |
> |     13 | NP       |
> |     14 | AP       |
> |     15 | NP       |
> |     16 | AP       |
> |     17 | SS       |
> |     18 | NP       |
> |     19 | AP       |
> #+TBLFM: address@hidden
>
>
> I want that org aggregate counts the entries in the second column of the
> tale raw-data, which it does (thanks to Thierry), but I also would like
> to have, automatically, to calculate its percentage. And that it seems I
> can only do manually as the below example shows.
>
> #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()  
> (count()/19)*100"
> | CalJunio | count() | (count()/19)*100 |
> |----------+---------+------------------|
> | AP       |       7 |    36.8421052632 |
> | NT       |       1 |    5.26315789474 |
> | SS       |       5 |    26.3157894737 |
> | NP       |       6 |    31.5789473684 |
> #+END:

In this example the hardcoded 19 value is annoying. It is the total
count() of rows in raw-data table. It should be computed automatically
by some other mean. One way is to use another named aggregation:

#+name: total-count
#+BEGIN: aggregate :table "raw-data" :cols "count()"
| count() |
|---------|
|      19 |
#+END:

Then the cell containing 19 can be accessed using a "remote" formula:
remote(total-count,@2$1)

#+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()"
| CalJunio | count() |        |
|----------+---------+--------|
| AP       |       7 | 36.842 |
| NT       |       1 |  5.263 |
| SS       |       5 | 26.316 |
| NP       |       6 | 31.579 |
#+TBLFM: $3=$2*100/remote(total-count,@2$1);f3
#+END:

Here a regular spreadsheet expression was used, instead of an aggregate
formula. Orgaggregate takes care of the table formulas between
re-computing. So you can refresh the aggregated table as often as needed.


>
> Is there a more elegant solution and how can I change the float format?
>
> By the way is there a way to customize the header of the above table,
> something like
>
>
> #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()  
> (count()/19)*100"
> | CalJunio | count |       percent |
> |----------+-------+---------------|
> | AP       |     7 | 36.8421052632 |
> | NT       |     1 | 5.26315789474 |
> | SS       |     5 | 26.3157894737 |
> | NP       |     6 | 31.5789473684 |
> #+END:
>

The header can be set with this cell formula: @1$3=percent

#+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()"
| CalJunio | count() | percent |
|----------+---------+---------|
| AP       |       7 |  36.842 |
| NT       |       1 |   5.263 |
| SS       |       5 |  26.316 |
| NP       |       6 |  31.579 |
#+TBLFM: $3=$2*100/remote(total-count,@2$1);f3::@1$3=percent
#+END:

Again, the #+TBLFM specification survives refreshes (performed with C-c
C-c on the #+BEGIN: line). Everything else is recomputed.

> thanks
>
> Uwe Brauer 
>
>
>
Regards
Thierry