Org-clock-report sort by time

Question

I've got some org-clock-tables created with the following

#+BEGIN: clocktable :maxlevel 2 :block thisweek :link t :formula % :scope agenda-with-archives :step day :fileskip0

| File           | Headline         | Time   |     % |
|----------------+------------------+--------+-------|
|                | ALL *Total time* | *5:04* | 100.0 |
|----------------+------------------+--------+-------|
| emacs.org      | *File time*      | *0:31* |  10.2 |
|                | Emacs            | 0:31   |  10.2 |
|----------------+------------------+--------+-------|
| project1.org   | *File time*      | *1:40* |  32.9 |
|                | Task1            | 1:40   |  32.9 |
|----------------+------------------+--------+-------|
| project2.org   | *File time*      | *2:40* |  52.6 |
|                | TaskA            | 2:40   |  52.6 |
|----------------+------------------+--------+-------|
| project3.org   | *File time*      | *0:13* |   4.3 |
|                | Task             | 0:13   |   4.3 |

I would like to have this list sorted by the last column to see which tasks I spend most of my time on in a given time period.

I've tried sorting that last column, but as each file is separated emacs doesn't sort it.

Is there a plugin or built-in command which already does this?

Answer 1

You can define your own :formatter for clocktables. Such a formatter can sort the table data to your likings and delegate further formatting to the formatter determined by org-clock-clocktable-formatter or to the default formatter org-clocktable-write-default.

The following example shows you how to define a sorting formatter my-org-clocktable-sorter and how to use it with the :formatter keyword for your clocktable.

I've tested it with some other org-files and get other results. In the example below I just edited the table by hand to show you what you should get.

If you want to try the example

• delete the old clocktable
• execute the source block by clicking C-c C-c on #+BEGIN_SRC
• re-generate the clocktable by clicking C-c C-c on clocktable

#+BEGIN_SRC emacs-lisp :results silent
(defun my-org-clocktable-sorter (ipos tables params)
  (setq tables (cl-sort tables (lambda (table1 table2) (> (nth 1 table1) (nth 1 table2)))))
  (funcall (or org-clock-clocktable-formatter 'org-clocktable-write-default) ipos tables params))
#+END_SRC

#+BEGIN: clocktable :maxlevel 2 :block thisweek :link t :formula % :scope agenda-with-archives :step day :fileskip 0 :formatter my-org-clocktable-sorter
| File           | Headline         | Time   |     % |
|----------------+------------------+--------+-------|
|                | ALL *Total time* | *5:04* | 100.0 |
|----------------+------------------+--------+-------|
| project2.org   | *File time*      | *2:40* |  52.6 |
|                | TaskA            | 2:40   |  52.6 |
|----------------+------------------+--------+-------|
| project1.org   | *File time*      | *1:40* |  32.9 |
|                | Task1            | 1:40   |  32.9 |
|----------------+------------------+--------+-------|
| emacs.org      | *File time*      | *0:31* |  10.2 |
|                | Emacs            | 0:31   |  10.2 |
|----------------+------------------+--------+-------|
| project3.org   | *File time*      | *0:13* |   4.3 |
|                | Task             | 0:13   |   4.3 |
#+END:

Answer 2

#+BEGIN: clocktable :scope subtree :maxlevel 2 :sort (3 . ?n)
#+END:

you can set the sort column and sort by number.

Answer 3

I assume that every project has its own file and you want to sort w.r.t. the project time.

You can apply a filter to your table and sort the filtered result.

In the following I give an example of a possible adaption of one of the filters at https://emacs.stackexchange.com/a/20137/2370 for your case.

Notes:

• The example does not sort the original table but creates a new one which you can sort. You have to decide whether it serves your purpose or not.
• I added the name myclock to the clocktable
• I filtered the rows with *File time* in the second column of your clocktable
• You can apply the source code block by clicking C-c C-c on #+BEGIN_SRC
• I already applied the sorting with org-table-sort-lines with sorting type N to the resulting filtered table.

#+BEGIN: clocktable :maxlevel 2 :block thisweek :link t :formula % :scope agenda-with-archives :step day :fileskip 0
#+NAME: myclock
| File           | Headline         | Time   |     % |
|----------------+------------------+--------+-------|
|                | ALL *Total time* | *5:04* | 100.0 |
|----------------+------------------+--------+-------|
| emacs.org      | *File time*      | *0:31* |  10.2 |
|                | Emacs            | 0:31   |  10.2 |
|----------------+------------------+--------+-------|
| project1.org   | *File time*      | *1:40* |  32.9 |
|                | Task1            | 1:40   |  32.9 |
|----------------+------------------+--------+-------|
| project2.org   | *File time*      | *2:40* |  52.6 |
|                | TaskA            | 2:40   |  52.6 |
|----------------+------------------+--------+-------|
| project3.org   | *File time*      | *0:13* |   4.3 |
|                | Task             | 0:13   |   4.3 |

#+NAME: table-filter
#+BEGIN_SRC emacs-lisp :var tbl=myclock val="*File time*" col=2
(cl-loop for row in tbl
         if (equal (nth (1- col) row) val)
         collect row into newtbl
         finally return newtbl)
#+END_SRC

#+RESULTS: table-filter
| project2.org | *File time* | *2:40* | 52.6 |
| project1.org | *File time* | *1:40* | 32.9 |
| emacs.org    | *File time* | *0:31* | 10.2 |
| project3.org | *File time* | *0:13* |  4.3 |