Dataview(JS): Summing task metadata

What I’m trying to do

I have tasks sprinkled throughout project notes in my vault. I use inline Dataview fields to flag them to do today (assigned) and sometimes to append an estimate of effort required.

For example, Project A.md might contain:

* [ ] Task 1, a trivial task. [assigned:: 2022-12-29]
* [ ] Task 2, a more substantial task. [assigned:: 2022-12-29], [effort:: 15 minutes]
* [ ] Task 3, an involved task. [assigned:: 2022-12-29], [effort:: 2 hours]
* [ ] Task 4, for someday later.

And in Project B.md:

* [ ] Task 5. [assigned:: 2022-12-29], [effort:: 1 hour]

I’d like to create two queries in my daily note template:

  1. A query (ideally inline) which sums the effort values of every assigned task which has one, returning a total duration.
  2. A query which returns a list which, given the example notes above, would look like:
* [[Project A]]: 2 hours, 15 minutes
* [[Project B]]: 1 hour

Things I have tried

My preference would be to use DQL queries rather than DataviewJS, but I think the latter might be necessary.

Assuming project notes are tagged #project, this DataviewJS query returns a list of the effort values of assigned tasks:

const notes = dv.pages("#project");
const tasks = notes.file.tasks.where(t => !t.completed && t.assigned && t.effort);
dv.list(tasks.effort)

but being ignorant of Javascript, don’t know how to sum the array, and was only able to make this meager progress with the help of this vault of example Dataview queries.

In DQL side, you can achieve some results…

a query to tests

TABLE rows.T.text, rows.T.effort, sum(rows.T.effort)
FROM #Project
FLATTEN file.tasks AS T
WHERE !T.completed AND T.assigned AND T.effort
GROUP BY file.link

a list with total per file

LIST sum(rows.T.effort)
FROM #Project
FLATTEN file.tasks AS T
WHERE !T.completed AND T.assigned AND T.effort
GROUP BY file.link

the global total

LIST WITHOUT ID "**Global Effort**: " + sum(rows.T.effort)
FROM #Project
FLATTEN file.tasks AS T
WHERE !T.completed AND T.assigned AND T.effort
GROUP BY true

This one isn’t an inline query. To that you need to use dvjs. But I’m not versed in js to help you with the conversion of duration. For example, taking your example, you can “sum” in this way:

const notes = dv.pages("#project");
const tasks = notes.file.tasks.where(t => !t.completed && t.assigned && t.effort);
const total = tasks.effort.array().reduce((acc, val) => acc + val, 0)

console.log(total)

dv.span("**Global Effort**: " + total)

But the problem is: you get the duration in milliseconds… And I don’t know how to convert that to hours, minutes. (I guess it’s necessary a function with some math…)

2 Likes

Thank you very much. Since I wrote the OP I’ve effectively locked myself out of my laptop and just about everything else for the next few days, so I regret that I won’t be able to try these until I’m reunited with my hardware key.

I think the summing of durations can be handled by Luxon, though I’d be just as happy using numbers only to represent hours. Thanks again!

Thanks again, @mnvwvnm. Your DQL queries do everything I described, and my preference for keeping DataviewJS disabled trumps my cosmetic preference for the totals query being inline.

For the benefit of anyone visiting this thread later, replacing this line:

const total = tasks.effort.array().reduce((acc, val) => acc + val, 0)

with

const total = dv.luxon.Duration.fromMillis(tasks.effort.array().reduce((acc, val) => acc + val, 0)).shiftTo('hours', 'minutes').toHuman()

returns a total like “3 hours, 15 minutes,” but if the total is in round hours, something like “4 hours, 0 minutes.” Replacing shiftTo() with rescale() ought to fix this, I think, but causes an error (rescale is not a function).

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.