Sub-totals

emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed

* Sub-totals
@ 2014-02-15 14:27 Cecil Westerhof
  2014-02-15 15:19 ` Sub-totals Michael Brand
  0 siblings, 1 reply; 9+ messages in thread
From: Cecil Westerhof @ 2014-02-15 14:27 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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 --]

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-15 14:27 Sub-totals Cecil Westerhof
@ 2014-02-15 15:19 ` Michael Brand
  2014-02-15 17:14   ` Sub-totals Cecil Westerhof
  0 siblings, 1 reply; 9+ messages in thread
From: Michael Brand @ 2014-02-15 15:19 UTC (permalink / raw)
  To: Cecil Westerhof; +Cc: Org Mode

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

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-15 15:19 ` Sub-totals Michael Brand
@ 2014-02-15 17:14   ` Cecil Westerhof
  2014-02-15 17:31     ` Sub-totals Cecil Westerhof
  2014-02-16  9:06     ` Sub-totals Michael Brand
  0 siblings, 2 replies; 9+ messages in thread
From: Cecil Westerhof @ 2014-02-15 17:14 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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 --]

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-15 17:14   ` Sub-totals Cecil Westerhof
@ 2014-02-15 17:31     ` Cecil Westerhof
  2014-02-16  9:06     ` Sub-totals Michael Brand
  1 sibling, 0 replies; 9+ messages in thread
From: Cecil Westerhof @ 2014-02-15 17:31 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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 --]

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-15 17:14   ` Sub-totals Cecil Westerhof
  2014-02-15 17:31     ` Sub-totals Cecil Westerhof
@ 2014-02-16  9:06     ` Michael Brand
  2014-02-19  5:00       ` Sub-totals Cecil Westerhof
  2014-02-20  7:55       ` Sub-totals Michael Brand
  1 sibling, 2 replies; 9+ messages in thread
From: Michael Brand @ 2014-02-16  9:06 UTC (permalink / raw)
  To: Cecil Westerhof; +Cc: Org Mode

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

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-16  9:06     ` Sub-totals Michael Brand
@ 2014-02-19  5:00       ` Cecil Westerhof
  2014-02-19 12:54         ` Sub-totals Nick Dokos
  2014-02-20  7:55       ` Sub-totals Michael Brand
  1 sibling, 1 reply; 9+ messages in thread
From: Cecil Westerhof @ 2014-02-19  5:00 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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 --]

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-19  5:00       ` Sub-totals Cecil Westerhof
@ 2014-02-19 12:54         ` Nick Dokos
  2014-02-19 19:39           ` Sub-totals Michael Brand
  0 siblings, 1 reply; 9+ messages in thread
From: Nick Dokos @ 2014-02-19 12:54 UTC (permalink / raw)
  To: emacs-orgmode

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

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-19 12:54         ` Sub-totals Nick Dokos
@ 2014-02-19 19:39           ` Michael Brand
  0 siblings, 0 replies; 9+ messages in thread
From: Michael Brand @ 2014-02-19 19:39 UTC (permalink / raw)
  To: Org Mode

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

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: Sub-totals
  2014-02-16  9:06     ` Sub-totals Michael Brand
  2014-02-19  5:00       ` Sub-totals Cecil Westerhof
@ 2014-02-20  7:55       ` Michael Brand
  1 sibling, 0 replies; 9+ messages in thread
From: Michael Brand @ 2014-02-20  7:55 UTC (permalink / raw)
  To: Cecil Westerhof; +Cc: Nick Dokos, Org Mode

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

^ permalink raw reply	[flat|nested] 9+ messages in thread

end of thread, other threads:[~2014-02-20  7:56 UTC | newest]

Thread overview: 9+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2014-02-15 14:27 Sub-totals Cecil Westerhof
2014-02-15 15:19 ` Sub-totals Michael Brand
2014-02-15 17:14   ` Sub-totals Cecil Westerhof
2014-02-15 17:31     ` Sub-totals Cecil Westerhof
2014-02-16  9:06     ` Sub-totals Michael Brand
2014-02-19  5:00       ` Sub-totals Cecil Westerhof
2014-02-19 12:54         ` Sub-totals Nick Dokos
2014-02-19 19:39           ` Sub-totals Michael Brand
2014-02-20  7:55       ` Sub-totals Michael Brand

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).