Help with using Dataview to get task count

[anant479]

Hi, I am trying to create a Task dashboard to count tasks that I do daily.

For example - I have a task “Read Book” that is in my daily template. And every day I mark the task as done if I complete it.

I would like to have a dashboard that shows how many times I completed the task “Read Book”

I started out by trying this - which shows all my tasks as single line items, I needed help on adding a count of the tasks as a column.

Thoughts? Is there an easier way to do this using sum / table?

Thanks for the help!

table tasks.text
flatten file.tasks as tasks
where tasks.completed
group by tasks.text

What I’m trying to do

Create a dashboard that shows each daily task by line and the count for a period by column

[mnvwvnm]

Two basic points to clarify:

• you’re not clear in define the wanted table (to check if that is possible or not):
  • a table with ALL daily notes and ALL “Read Book” tasks (completed or uncompleted)?
  • a table with only the daily notes that have “Read Book” tasks completed?
  • a table for all your daily notes (a “monster table” after some months…) or for a specific period?
• the second point is related with the “sum” of the completed “Read Book” tasks:
  • in regular DQL queries you can’t add a sum at the bottom of the column
  • that is only possible with dataviewjs + js in a complicated way
  • but you can get the value (the sum) in a separated query/table

[anant479]

Hi - thank you for the response.

To Clarify

• The wanted table in its final state should show:

1. First column - a list of all the tasks in the specified folder (Diary/Daily)
2. Second column - the count of how many notes have the matching task.text (from the first column) marked as complete
3. Using the query I can change the time frame to create different dashboards that will show the completion metrics for a week, month or year etc.

I was using Read Book as an example since if I can figure out how to show the “Completion” count of all tasks matching “Read Book” for a period then I should be able to figure out how to replicate for a larger list.

Right now I am able to show a list of all notes that have “Read Book” completed but to your second point, I cannot figure out how to show the sum

Col1 ---------------- Col2
Read Book 32 [ THIS WOULD SHOW THE COMPLETION COUNT]
Task 2 12
Task 3 8
etc

Regarding your second point - it sounds like you are saying showing the completion count in the format I’ve shown above is not easily possible without using dataview + js (too complicated for me right now)

Could you elaborate or point me to resources on how I could “but you can get the value (the sum) in a separated query/table”

Thanks again for your help

[mnvwvnm]

@anant479

Regarding your second point - it sounds like you are saying showing the completion count in the format I’ve shown above is not easily possible without using dataview + js (too complicated for me right now)

My declaration was related with a total at the bottom of a column (as a sum of the values in rows above). That’s why I said “in regular DQL queries you can’t add a sum at the bottom of the column”. But your case is different: you want a column with a total per row (related with the group in first column).

I’m still not sure about your goal: if you want to see all tasks text but only the total for the completed; if you want to see only the task text for the completed (and the sum); etc… because you said:

@anant479

• First column - a list of all the tasks in the specified folder (Diary/Daily)
• Second column - the count of how many notes have the matching task.text (from the first column) marked as complete

So, play with this query:

TABLE WITHOUT ID
	key as Tasks,
	length(rows) AS Total,
	rows.file.link AS "Files for total",
	length(filter(rows.tasks, (r) => r.completed)) AS Completed,
	filter(rows, (r) => r.tasks.completed).file.link AS "Files for completed"
FROM "your/folder/path"
FLATTEN file.tasks as tasks
GROUP BY tasks.text
SORT length(rows) DESC

[anant479]

Thank you, this has been very helpful. The documentation on Dataview seems scanty, I have been trying to find documentation on how to use the filter and flattenn function. The table it’s creating has the right numbers but I’m trying to figure out how to get rid of the “Files For Total” and “Files for completed” columns since they add too many lines.

[mnvwvnm]

@anant479

… but I’m trying to figure out how to get rid of the “Files For Total” and “Files for completed” columns since they add too many lines.

… just removing the unwanted column declarations!

TABLE WITHOUT ID
	key as Tasks,
	length(rows) AS Total,
	length(filter(rows.tasks, (r) => r.completed)) AS Completed
FROM "your/folder/path"
FLATTEN file.tasks as tasks
GROUP BY tasks.text
SORT length(rows) DESC

[anant479]

I tried that but gave me an error… BUT after you suggested it again I tried and realized I had left an extra comma at the end of the line.

YOU ARE THE BEST.