3

In orgmode, I do have a table like the following to track my overall working time a day. It is not important for which tasks or projects or even which client I spent my time on, just if I was in office or worked from remote.

The table looks like this:

| Day | Start |   End | Home |   Sum |
|-----+-------+-------+------+-------|
|   1 | 09:00 | 17:00 | No   |  8.00 |
|   2 | 09:00 | 12:00 | No   |  3.00 |
|   2 | 13:00 | 20:00 | Yes  |  7.00 |
|-----+-------+-------+------+-------|
|     |       |       | 7.00 | 11.00 |
#+TBLFM: @2$5..@-1$5=$3-$2;t

Now I need a formula that calculates the fields @<$4 and @<$5 as sums, while in @<$4 the values of $5 are summed when in that row $4 is Yes and in @<$5 the values of $5 shall be summed when $3 of that row is not Yes.

Instead of Yes/No I can switch to any other kind of marker as well.

  • Do any of the answers work for you? – Melioratus Sep 16 '16 at 4:38
3

I guess there is 2 options .. calculate some helper columns and sum multiply those or create helper elisp function .. I'll start with the latter.

#+BEGIN_SRC elisp
  (defun my-sum-helper (a  b pred)
    (reduce #'+
            (cl-mapcar
              #'*
              (mapcar
               (lambda(x) (if (string= x pred) 1 0)) a)
              b)))
#+END_SRC

#+RESULTS:
: my-sum-helper


| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@>$4='(my-sum-helper '(@2$4..@4$4) '(@2$5..@4$5) "Yes");L::@>$5='(my-sum-helper '(@2$4..@4$4) '(@2$5..@4$5) "No");L

To use: evaluate the src block first then evaluate the formulas.

LIke I said you can also create an intermediate column wih 0 an 1 values with the formula: @2$6..@4$6='(if (string= "$4" "Yes") 1 0);L. This facilitates the use of calc:

| Day | Start |   End | Home |  Sum |   |
|-----+-------+-------+------+------+---|
|   1 | 09:00 | 17:00 | No   | 8.00 | 0 |
|   2 | 09:00 | 12:00 | No   | 3.00 | 0 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 | 1 |
|-----+-------+-------+------+------+---|
|     |       |       | 7.   |  11. |   |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@2$6..@4$6='(if (string= "$4" "Yes") 1 0);L::@>$4=vsum(@2$6..@4$6 * @2$5..@4$5);N::@>$5=vsum((1-@2$6..@4$6) * @2$5..@4$5);N

hope this helps.

  • The first solution with code fills both result cells with #ERROR on macOS 10.14.5 and Emacs 26.2 (9.0). – miguelmorin Jul 1 at 8:04
2

Create table function, i.e. #+TBLFM:, and use the built-in org-lookup-all function with string= predicate and apply elisp function.

Note: Use the Literal mode L or the cell address references may misbehave.

I've wondered how to do calculations against filtered subset of an org-mode table data for years and never figured out how until today!

The Answer

| Day | Start |   End | Home |  Sum |
|-----+-------+-------+------+------|
|   1 | 09:00 | 17:00 | No   | 8.00 |
|   2 | 09:00 | 12:00 | No   | 3.00 |
|   2 | 13:00 | 20:00 | Yes  | 7.00 |
|-----+-------+-------+------+------|
|     |       |       | 7.0  | 11.0 |
#+TBLFM: @2$5..@-1$5=$3-$2;t::@>$4='(apply '+ (org-lookup-all "Yes" '(@I$4..@II$4) '(@I$5..@II$5) 'string= ));L::@>$5='(apply '+ (org-lookup-all "No" '(@I$4..@II$4) '(@I$5..@II$5) 'string= ));L

The Tricky Bits

  • Filtering the table with org-lookup-all function

    (org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
    
    • VAL is the filter value, e.g. Yes or No.
    • S-LIST is the search list, e.g. ("No" "No" "Yes").
    • R-LIST is the returned list, e.g. (8.00 3.00 7.00)
    • PREDICATE is not optional in our answer because we're comparing strings, e.g. string=

    • Returns a filtered list, e.g. (8.0 3.0)

  • Using apply elisp function instead of the vsum calc function.

    Couldn't get vsum to work - blah.

Thank you for asking your question!

This code was tested using
GNU Emacs 24.4.1 (x86_64-apple-darwin14.0.0, NS apple-appkit-1343.14)
Org-Mode Version: 8.3.5

1

An alternative would be to use the orgtbl-aggregate available on Melpa.

 #+name: yourtable
 | Day | Start |   End | Home |   Sum |
 |-----+-------+-------+------+-------|
 |   1 | 09:00 | 17:00 | No   |  8.00 |
 |   2 | 09:00 | 12:00 | No   |  3.00 |
 |   2 | 13:00 | 20:00 | Yes  |  7.00 |

 #+BEGIN: aggregate :table "yourtable" :cols "Home sum(Sum)"
 | Home | sum(Sum) |
 |------+----------|
 | No   |      11. |
 | Yes  |       7. |
 #+END:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.