How to summarize completed tasks from daily notes by month in Dataview

Help
1

What I’m trying to do

I have daily notes in the “YYYY.MM.DD” format. Each of them contains tasks, including the task “Meditation.” How can I write code that will compile information from these notes into a table where the data will be grouped by months, and for each month, the number of completed tasks for the month will be shown? For example:
December 2022 | 22/31 | 70%
November 2022 | 0/30 | 0%

Things I have tried

but I can’t even get the date from the note title, this query produces an empty table

TABLE WITHOUT ID dateformat(file.day, "yyyy.MM.DD") as "Date"
FROM "1. Inbox"
2

First of all file.day will get its value from either a properly formatted date within the file title, or from the field date within the YAML/frontmatter/properties. With properly formatted date I’m referring to the yyyy-MM-dd format, as see date format.

So with a file name of “2023.12.24” and no date property, you’ll not get a value into file.day. You can however do something like date(file.name, "yyyy.MM.dd") to interpret that file name as a date. It would be nicer however to use a proper date in the date field, so that you don’t have to convert the file name all the time.

Regarding the second half of your request to get those values you’ll need to calculate the number of tasks in general and the number of completed tasks, before grouping together the results and presenting them.

Here is an example query to get you started, where it’s assuming that the file.day has been properly set. Since tasks can have all kind of status characters, besides the ordinary “ ” and “x”, I’m explicitly counting just those tasks. If you want to include cancelled tasks, or partially started tasks, or something else like that you’ll need to extend the filter functions appropriately.

```dataview
TABLE
  sum(rows.taskCount), sum(rows.completedCount)
WHERE file.tasks AND file.day
FLATTEN length(filter(file.tasks, (t) => t.status = " " OR t.status = "x")) as taskCount
FLATTEN length(filter(file.tasks, (t) => t.status = "x" )) as completedCount
WHERE taskCount > 0 
GROUP BY dateformat(file.day, "yyyy-MM")
SORT key
```

If you want the first column to be localised, you could exchange the first line in that query with something like:

TABLE WITHOUT ID dateformat(rows[0].file.day, "LLLL yyyy"),

Hope this helps, and gets you going. It’s actually kind of a complex query as it does a lot of data manipulation to get the result you’re asking for.

3

Thanks for the tips. I have 1500 daily notes, and many other plugins are already set up for this date format, so I don’t really want to change the format.

I tried in the above code just replacing “file.day” with “date(file.name, “yyyy.MM.dd”)” and it worked.

But I was left with a couple of issues:

  1. There is still a problem with the first line, if I change the

"TABLE WITHOUT ID dateformat(rows[0].file.day, "LLLL yyyyy")"
to

"TABLE WITHOUT ID dateformat(rows[0].date(file.name, "yyyy.MM.dd"), "LLLL yyyyy")""

it gives the error:

"Dataview: Every row during final data extraction failed with an error; first 3:
            - Cannot call type 'null' as a function
- Cannot call type 'null' as a function"

What am I doing wrong here?

  1. What is the correct way to add a condition here to collect only the necessary tasks. I need something like "WHERE contains(Tasks.text, “Meditation”), but I don’t understand where and how to insert it.

Tried something like this, but it still counts all the tasks inside, not just the right one:

TABLE
  sum(rows.taskCount), sum(rows.completedCount)
FROM "1. Inbox"
WHERE file.tasks AND date(file.name, "yyyy.MM.dd")
FLATTEN length(filter(file.tasks, (t) => t.status = " " OR t.status = "x")) as taskCount
FLATTEN length(filter(file.tasks, (t) => t.status = "x" )) as completedCount
FLATTEN file.tasks AS Tasks
WHERE taskCount > 0 
WHERE contains(Tasks.text,"Meditation")
GROUP BY dateformat(date(file.name, "yyyy.MM.dd"), "yyyy-MM")
SORT key DESC
4

Maybe something like:

TABLE WITHOUT ID 
  dateformat(date(rows[0].file.name, "yyyy.MM.dd"), "LLLL yyyy")

The rows[0].file.day uses the first entry in the rows list, so you’ll need to pick the file.name from the same entry.

With exception of the first line dictating what we finally present, the other lines are sequential, so you’ll need to have that contains requirement earlier in the query.

The following is untested, as I don’t have a similar setup currently.

```dataview
TABLE WITHOUT ID 
  dateformat(date(rows[0].file.name, "yyyy.MM.dd"), "LLLL yyyy"),
  sum(rows.taskCount), sum(rows.completedCount)
FROM "1. Inbox"
FLATTEN list(filter(file.tasks, (t) => contains(t.text, "Meditation"))) as meditationTasks
WHERE length(meditationTasks) > 0
FLATTEN length(filter( meditationTasks, (t) => t.status = " " OR t.status = "x" )) as taskCount
FLATTEN length(filter( meditationTasks, (t) => t.status = "x" )) as completedCount
GROUP BY dateformat(date(file.name, "yyyy.MM.dd"), "yyyy-MM")
SORT key DESC
```

What I’m aiming for with this query is first to filter out the meditationTasks as those containing the “Meditation” text, and then if we’ve got some of those, do the rest of the calculation on just those tasks. That way the numbers should be correct.

5

It’s working perfectly. Thank you very much!

6

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