5

Suppose I have some headlines that look like this.

* TODO Parent
** TODO Sub Head1
   CLOCK: [2015-02-09 Mon 07:00]--[2015-02-09 Mon 07:08] =>  0:08
*** TODO Sub Sub Head
    CLOCK: [2015-02-09 Mon 19:20]--[2015-02-09 Mon 19:24] =>  0:04
** TODO Sub Head2
   CLOCK: [2015-02-09 Mon 07:08]--[2015-02-09 Mon 07:11] =>  0:03

Now, if I have a clock table report defined like this.

#+BEGIN: clocktable :maxlevel 2 :scope file :formula "$4='(random)::@2$4='(vsum @3$4)"

I have a custom formula that computes a value. To illustrate my problem, I've used (random) as a way to populate a column. How do I then sum up the results?

| Headline           | Time   |      |                     |
|--------------------+--------+------+---------------------|
| *Total time*       | *0:15* |      |              #ERROR |
|--------------------+--------+------+---------------------|
| TODO Parent        | 0:15   |      |  796400794509121126 |
| \__ TODO Sub Head1 |        | 0:12 | 2086072072269781784 |
| \__ TODO Sub Head2 |        | 0:03 | 1733653844633948893 |

I've used the formula debugger C-f { and I can see that my vsum invocation is receiving the cell values as propertized strings. Is there a way to compute that sum and lay it down in that cell where the #ERROR resides?

2 Answers 2

7

Hoping to provide a little more value to this answer, here's some explanation (to the best of my knowledge) of how Org processes the table formulae.

  1. Org splits formulae on :: into simpler expressions.
  2. Org replaces the range expressions with the actual values extracted from the table columns.
  3. If the formula is a Calc expression, it calls calc-eval on it and then parses the result of calc-eval to give the final result.

Now, to figure out what Calc does with your formula you can do the following (the example should explain it better):

;; Assume Org substituted the range expression with actual values
;; and the destination with some variable it knows to get back
(calc-eval "x=vsum(1, 2, 3)")
"x = 6"
;; And here's how you can see what Calc will do to the formula,
;; the so-called AST (abstract syntax tree), which also tells you
;; what function you would need to call from ELisp to get the
;; same result as you would have by specifying the corresponding
;; formula.
(math-read-exprs "x=vsum(1, 2, 3)")
((calcFunc-eq (var x var-x) (calcFunc-vsum 1 2 3)))

So, in the end, you could have used a formula like this:

#+TBLFM: @3$4..@>$4='(random)::@2$4='(apply 'calcFunc-vsum (mapcar 'string-to-number '(@3$4..@>$4)))

And you could test it in the *scratch* buffer to see that, if you are running on a 32-bit system, you'd get an integer result, but on 64-bit system, the result might look something like: (bigpos 815607762 964955910 2) because Calc uses 32-bit arithmetic. And this would be invalid as the table value. So, you could either do:

#+TBLFM: @3$4..@>$4='(random)::@2$4='(math-format-value (apply 'calcFunc-vsum (mapcar 'string-to-number '(@3$4..@>$4))))

Which would take care of all kinds of data-structures that Calc understands (such as dates, errors, complex numbers). Or, in a simpler case, you could use Emacs-lisp mathematics:

#+TBLFM: @3$4..@>$4='(random)::@2$4='(reduce '+ (mapcar 'string-to-number (list @3$4..@>$4)))
2

The problem is that you have an error in your sum formula. You declared it as a lisp form using ='(vsum @3$4), but since vsum is a calc command the correct syntax in table formulas is just =vsum(@3$4).

4
  • I think OP wants to use vsum in ELisp, in which case something like this should do: #+TBLFM: @3$4..@>$4='(random)::@2$4='(reduce '+ (mapcar 'string-to-number (list @3$4..@>$4))).
    – wvxvw
    Feb 13, 2015 at 8:48
  • So this sums the column which is what I had wanted. I'd be happy to accept this as the answer. But just to be clear, shouldn't I be able to use vsum to do this too? (vsum @3$4..@>$4) doesn't seem to be it. Feb 13, 2015 at 9:20
  • @EricJohnson nope, vsum is not an ELisp function or macro. Org translates it into some other code, probably calc-summation or maybe just (apply '+ value-1 value-2 ... value-n).
    – wvxvw
    Feb 13, 2015 at 9:25
  • So with that - your comment is the answer. If you'd like the credit, post your original comment as the answer and then I can accept it. Feb 13, 2015 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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