In an orgmode clock report table, how do I sum a column that was computed with another formula?

Question

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?

Answer 1

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

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