Dataview Query Help - dynamic date query for incomplete tasks

Hi Everyone,

What I’m trying to do

I am trying to write a dataview query that will show me all incomplete tasks in my weekly notes.

I have a week plan note where I plan for the week and then a week review note where I review that week.

I would like to add a query that shows me all incomplete tasks in the weekly plan note created 1 week before the weekly review note, if that makes sense.

So it has to be dynamic

FROM #weekplan AND -"Extra/Templates"
WHERE date(today) - file.cday <= dur(7 days) AND !complete AND !checked
SORT file.mtime desc

I am just checking really to see if this is correct as I can’t create a week-old note right now.

But ideally, this should show me all incomplete tasks that are a week old (and only a week old (not within a range of a week) from the current day)

I have seen some examples where people specify the date such as:

WHERE date <= date(2022-10-27) and date >= date(2022-10-20)

but I want it to be dynamic and not have to manually input the date each time. I also do not want to list a range where it shows me all tasks created that week but instead only specifically created a week ago in that specific note.

I want to add that when I tried this query it output the incomplete tasks from the correct note, however, this is an issue as it was created today…and not a week ago. I am not sure why this is happening.

I would also like to add, in my frontmatter for my weekly-plan notes I have a field labelled “created:” where I use a templater command to fill in the week automatically upon creation of a new weekly note. e.g:

created: <%"YYYY-[W]ww") %>

which outputs: 2024-W01

would it make more sense to incorporate this into the dataview query such as:

FROM #weekplan AND -"Extra/Templates"
WHERE contains(created, date(today) - file.cday <= dur(7 days)) AND !complete AND !checked
SORT file.mtime desc

I apologize for my long (and possibly jumbled up) post.

any help would be much appreciated :slight_smile:

I’ve not read your post thoroughly, but I do see that I’d suggest to add a date property to your weekly note. That way you’re able to use that property, aka to discover which week you’re currently in for that note. This would also allow you to easily build last weeks weekly note so that you’re able to test your queries.

In general, I don’t rely on the file.cday for anything, and always set the date to the intended creation date. This populates the variable with that date, allowing me for a reliable date to be used within my queries. (One could also opt for a properly formatted date in the file title, but I’ve gone for always setting it in the properties).

Once that is set, you’re free to use dateformat() to get your week, and compare it to the week of your daily notes or whatever you want to compare it towards.

Hope this helps you get a step further. (And I now see that you’re setting created to be the week number. That’s good, but I would also set the date with a full date, just to be on the safe side)

Hi Holroy,

Thank you for your reply :slight_smile:

I am trying to add a templater command into my frontmatter so I have the full date available to use, too:

<% moment(tp.file.title,'YYYY-[W]ww').startOf('week').add(0, 'days').format("YYYY-MM-DD") %>

However, this currently is not working (not sure why) but when it does hopefully I can continue with the dataview query.

Also how would the dateformat() variable work? do I add it in the WHERE section, so it looks something like this:

WHERE dateformat("YYYY-[W]ww")

Kind regards

I think using a query is a good option, so if you do it related to today the query would look like:

FROM #weekplan and -"Extra/Templates"
  dateformat(, "yyyy-'W'W") =
  dateformat(date(today) - dur(7days), "yyyy-'W'W")

The query above assumes inserting of a proper date into the property of each file or within the note title. See excerpt from documentation below: – Date – Only available if the file has a date inside its file name (of form yyyy-mm-dd or yyyymmdd), or has a Date field/inline field.

However, if you insert that into an older weekly note, it’ll still report for last week (not the week before that note). To achieve that, insert some date of the week into your weekly note, and use that date instead of the today. You could also insert a previous week property into the template, and pre-calculate it when creating the weekly note. (But it’ll look a little confusing, I think, to see last weeks week number in the properties… )

Regarding the templater command, are you sure that tp.file.title is set when you execute that part of the template? If so, you could most likely use the following to set the date for the weekstart:

<% moment(tp.file.title, "YYYY-[W]ww").format("YYYY-MM-DD") %>

This do depend on the tp.file.title to be something like 2024-W01. If you’re not sure if it’s set already, try adding <% tp.file.title %> somewhere in your template. If this is the template running when you’re creating the note, then it’s most likely not set already, or is possibly “Untitled” or something similar.

Hi Holroy,

I really appreciate your help!

I am a bit of a noob when it comes to these things and find it quite technical sometimes, so please bear with me while I try to fully understand your reply and implement it.

Kind regards

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