Sub-totals

Sub-totals

[Cecil Westerhof]

[-- Attachment #1: Type: text/plain, Size: 1538 bytes --]

At the moment I have the folowing:
|--------------+------+-----|
| activity     | Time | sub |
|--------------+------+-----|
| Activity A   |    3 |     |
| Activity B   |    5 |     |
| Activity C   |    2 |     |
| Activity D   |    7 |     |
| Activity E   |    8 |     |
| Activity F   |   12 |     |
|--------------+------+-----|
| Activity 1   |    9 |     |
| Activity 2   |    2 |     |
| Activity 3   |    4 |     |
| Activity 4   |    3 |     |
|--------------+------+-----|
| Activity I   |   23 |     |
| Activity II  |   51 |     |
| Activity III |   37 |     |
|--------------+------+-----|
|              |  163 |     |
|--------------+------+-----|
#+TBLFM: @>$2=vsum(@3..@-1)


But I want the following:
|--------------+------+-----|
| activity     | Time | sub |
|--------------+------+-----|
| Activity A   |    3 |     |
| Activity B   |    5 |     |
| Activity C   |    2 |     |
| Activity D   |    7 |     |
| Activity E   |    8 |     |
| Activity F   |   12 |  34 |
|--------------+------+-----|
| Activity 1   |    9 |     |
| Activity 2   |    2 |     |
| Activity 3   |    4 |     |
| Activity 4   |    3 |  18 |
|--------------+------+-----|
| Activity I   |   23 |     |
| Activity II  |   51 |     |
| Activity III |   37 | 111 |
|--------------+------+-----|
|              |  163 |     |
|--------------+------+-----|
#+TBLFM: @>$2=vsum(@3..@-1)

Subtotals from every range is calculated. Preferable from second to second
last, so it goes OK when a range is added.

Is this possible?

-- 
Cecil Westerhof

[-- Attachment #2: Type: text/html, Size: 2277 bytes --]

Re: Sub-totals

[Michael Brand]

Hi Cecil

What about this?:

|--------------+------+-----|
| activity     | Time | sub |
|--------------+------+-----|
| Activity A   |    3 |     |
|--------------+------+-----|
| Activity B   |    5 |  34 |
| Activity C   |    2 |  34 |
| Activity D   |    7 |  34 |
| Activity E   |    8 |  34 |
| Activity F   |   12 |  34 |
|--------------+------+-----|
| Activity 1   |    9 |  18 |
| Activity 2   |    2 |  18 |
| Activity 3   |    4 |  18 |
| Activity 4   |    3 |  18 |
|--------------+------+-----|
| Activity I   |   23 | 111 |
| Activity II  |   51 | 111 |
| Activity III |   37 | 111 |
|--------------+------+-----|
|              |  163 |     |
|--------------+------+-----|
#+TBLFM: @>$2 = vsum(@<<<..@>>) :: @<<<$3..@>>$3 = vsum(@-I$2..@+I$2)

Michael

Re: Sub-totals

[Cecil Westerhof]

[-- Attachment #1: Type: text/plain, Size: 1323 bytes --]

2014-02-15 16:19 GMT+01:00 Michael Brand <michael.ch.brand@gmail.com>:

> What about this?:
>
> |--------------+------+-----|
> | activity     | Time | sub |
> |--------------+------+-----|
> | Activity A   |    3 |     |
> |--------------+------+-----|
> | Activity B   |    5 |  34 |
> | Activity C   |    2 |  34 |
> | Activity D   |    7 |  34 |
> | Activity E   |    8 |  34 |
> | Activity F   |   12 |  34 |
> |--------------+------+-----|
> | Activity 1   |    9 |  18 |
> | Activity 2   |    2 |  18 |
> | Activity 3   |    4 |  18 |
> | Activity 4   |    3 |  18 |
> |--------------+------+-----|
> | Activity I   |   23 | 111 |
> | Activity II  |   51 | 111 |
> | Activity III |   37 | 111 |
> |--------------+------+-----|
> |              |  163 |     |
> |--------------+------+-----|
> #+TBLFM: @>$2 = vsum(@<<<..@>>) :: @<<<$3..@>>$3 = vsum(@-I$2..@+I$2)
>

It is certainly a big step in the right direction. I have to study it to
understand what it does. (You also changed the part I already had. I have
to look to see why that is better.)

There are a few problems with it:
- As your example shows, the first element is not filled and when the
first range only has one element ...
- I would like to have only the last element of the range filled.

But it is certainly helpful: thanks.

-- 
Cecil Westerhof

[-- Attachment #2: Type: text/html, Size: 2549 bytes --]

Re: Sub-totals

[Cecil Westerhof]

[-- Attachment #1: Type: text/plain, Size: 2280 bytes --]

2014-02-15 18:14 GMT+01:00 Cecil Westerhof <cldwesterhof@gmail.com>:

> 2014-02-15 16:19 GMT+01:00 Michael Brand <michael.ch.brand@gmail.com>:
>
> What about this?:
>>
>> |--------------+------+-----|
>> | activity     | Time | sub |
>> |--------------+------+-----|
>> | Activity A   |    3 |     |
>> |--------------+------+-----|
>> | Activity B   |    5 |  34 |
>> | Activity C   |    2 |  34 |
>> | Activity D   |    7 |  34 |
>> | Activity E   |    8 |  34 |
>> | Activity F   |   12 |  34 |
>> |--------------+------+-----|
>> | Activity 1   |    9 |  18 |
>> | Activity 2   |    2 |  18 |
>> | Activity 3   |    4 |  18 |
>> | Activity 4   |    3 |  18 |
>> |--------------+------+-----|
>> | Activity I   |   23 | 111 |
>> | Activity II  |   51 | 111 |
>> | Activity III |   37 | 111 |
>> |--------------+------+-----|
>> |              |  163 |     |
>> |--------------+------+-----|
>> #+TBLFM: @>$2 = vsum(@<<<..@>>) :: @<<<$3..@>>$3 = vsum(@-I$2..@+I$2)
>>
>
> It is certainly a big step in the right direction. I have to study it to
> understand what it does. (You also changed the part I already had. I have
> to look to see why that is better.)
>
> There are a few problems with it:
> - As your example shows, the first element is not filled and when the
> first range only has one element ...
> - I would like to have only the last element of the range filled.
>
> But it is certainly helpful: thanks.
>

When looking better I saw there was a nasty bug. The following works and
gets rid of the first 'problem':
|--------------+------+-----|
| activity     | Time | sub |
|--------------+------+-----|
| Activity A   |    3 |   3 |
|--------------+------+-----|
| Activity B   |    5 |  34 |
| Activity C   |    2 |  34 |
| Activity D   |    7 |  34 |
| Activity E   |    8 |  34 |
| Activity F   |   12 |  34 |
|--------------+------+-----|
| Activity 1   |    9 |  18 |
| Activity 2   |    2 |  18 |
| Activity 3   |    4 |  18 |
| Activity 4   |    3 |  18 |
|--------------+------+-----|
| Activity I   |   23 | 111 |
| Activity II  |   51 | 111 |
| Activity III |   37 | 111 |
|--------------+------+-----|
|              |  166 |     |
|--------------+------+-----|
#+TBLFM: @>$2 = vsum(@<<..@>>) :: @<<$3..@>>$3 = vsum(@-I$2..@+I$2)




-- 
Cecil Westerhof

[-- Attachment #2: Type: text/html, Size: 4410 bytes --]

Re: Sub-totals

[Michael Brand]

Hi Cecil

On Sat, Feb 15, 2014 at 6:14 PM, Cecil Westerhof <cldwesterhof@gmail.com> wrote:
> - I would like to have only the last element of the range filled.

I always thought that this would not be possible with reasonable
effort. Your question made me think again and it is possible, now I
can change my own use cases with sub-total :-) . See docstring and
take the procedure with vlen etc. from the TBLFM of the new ERT
test-org-table/sub-total here:
http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el

Or read the docstring there and just take this copy that I used to
build the ERT:
|-------+-------+-------|
| Item  |  Item |  Sub- |
| name  | value | total |
|-------+-------+-------|
| a1    |   4.1 |       |
| a2    |   8.2 |       |
| a3    |       |  12.3 |
|-------+-------+-------|
| b1    |  16.0 |  16.0 |
|-------+-------+-------|
| c1    |    32 |       |
| c2    |    64 |  96.0 |
|-------+-------+-------|
| Total | 124.3 |       |
|-------+-------+-------|
#+TBLFM: @>$2 = vsum(@II..@>>) :: $3 = if(vlen(@-I$2..@0$2) ==
vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2) +.0, string("")); EN f-1 ::
@>$3 = string("")

Michael

Re: Sub-totals

[Cecil Westerhof]

[-- Attachment #1: Type: text/plain, Size: 1365 bytes --]

2014-02-16 10:06 GMT+01:00 Michael Brand <michael.ch.brand@gmail.com>:

> > - I would like to have only the last element of the range filled.
>
> I always thought that this would not be possible with reasonable
> effort. Your question made me think again and it is possible, now I
> can change my own use cases with sub-total :-) . See docstring and
> take the procedure with vlen etc. from the TBLFM of the new ERT
> test-org-table/sub-total here:
>
> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el
>
> Or read the docstring there and just take this copy that I used to
> build the ERT:
> |-------+-------+-------|
> | Item  |  Item |  Sub- |
> | name  | value | total |
> |-------+-------+-------|
> | a1    |   4.1 |       |
> | a2    |   8.2 |       |
> | a3    |       |  12.3 |
> |-------+-------+-------|
> | b1    |  16.0 |  16.0 |
> |-------+-------+-------|
> | c1    |    32 |       |
> | c2    |    64 |  96.0 |
> |-------+-------+-------|
> | Total | 124.3 |       |
> |-------+-------+-------|
> #+TBLFM: @>$2 = vsum(@II..@>>) :: $3 = if(vlen(@-I$2..@0$2) ==
> vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2) +.0, string("")); EN f-1 ::
> @>$3 = string("")
>

I made:
    #+TBLFM: @>$2 = vsum(@<<..@>>) :: @<<$3..@>>$3 = if(vlen(@-I$2..@0$2)
== vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2), string(""))

-- 
Cecil Westerhof

[-- Attachment #2: Type: text/html, Size: 2173 bytes --]

Re: Sub-totals

[Nick Dokos]

Cecil Westerhof <cldwesterhof@gmail.com> writes:

> 2014-02-16 10:06 GMT+01:00 Michael Brand <michael.ch.brand@gmail.com>:
>
>     > - I would like to have only the last element of the range filled.
>    
>     I always thought that this would not be possible with reasonable
>     effort. Your question made me think again and it is possible, now I
>     can change my own use cases with sub-total :-) . See docstring and
>     take the procedure with vlen etc. from the TBLFM of the new ERT
>     test-org-table/sub-total here:
>     http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el
>    
>     Or read the docstring there and just take this copy that I used to
>     build the ERT:
>     |-------+-------+-------|
>     | Item  |  Item |  Sub- |
>     | name  | value | total |
>     |-------+-------+-------|
>     | a1    |   4.1 |       |
>     | a2    |   8.2 |       |
>     | a3    |       |  12.3 |
>     |-------+-------+-------|
>     | b1    |  16.0 |  16.0 |
>     |-------+-------+-------|
>     | c1    |    32 |       |
>     | c2    |    64 |  96.0 |
>     |-------+-------+-------|
>     | Total | 124.3 |       |
>     |-------+-------+-------|
>     #+TBLFM: @>$2 = vsum(@II..@>>) :: $3 = if(vlen(@-I$2..@0$2) ==
>     vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2) +.0, string("")); EN f-1 ::
>     @>$3 = string("")
>
> I made:
>     #+TBLFM: @>$2 = vsum(@<<..@>>) :: @<<$3..@>>$3 = if(vlen(@-I$2..@0$2) == vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2), string(""))
>

Nice! Worth a mention in the org-hacks section on Worg I think.

-- 
Nick

Re: Sub-totals

[Michael Brand]

On Wed, Feb 19, 2014 at 1:54 PM, Nick Dokos <ndokos@gmail.com> wrote:
> Nice! Worth a mention in the org-hacks section on Worg I think.

Maybe. Instead I would like to favor to look at the page with the Worg
tutorials and in the spreadsheet section to follow the link to the
"Emacs Regression Test (ERT) for Org tables" with example use cases
for TBLFM:
http://orgmode.org/worg/org-tutorials/index.html#Spreadsheet

Michael

Re: Sub-totals

[Michael Brand]

Hi Cecil

On Sun, Feb 16, 2014 at 10:06 AM, Michael Brand
<michael.ch.brand@gmail.com> wrote:

> #+TBLFM: @>$2 = vsum(@II..@>>) :: $3 = if(vlen(@-I$2..@0$2) == vlen(@-I$2..@+I$2), vsum(@-I$2..@+I$2) +.0, string("")); EN f-1 :: @>$3 = string("")

I should have simplified the condition in "if" to

#+TBLFM: @>$2 = vsum(@II..@>>) :: $3 = if(vlen(@0..@+I) == 1,
vsum(@-I$2..@+I$2) +.0, string("")); EN f-1 :: @>$3 = string("")

It's now also in the ERT.

Michael