Weekly notes reviews - how to show old notes only

What I’m trying to do

I’m creating a note template for weekly notes where I want to see

  1. All the notes created in that week
  2. All the notes edited in that week

Things I have tried

My solution:

TABLE date, note-type, file.folder, file.mday
WHERE date(file.cday).year = date(this.file.cday).year
WHERE date(file.cday).weekyear = date(this.file.cday).weekyear
TABLE date, note-type, file.folder, file.mday
WHERE date(file.mday).year = date(this.file.cday).year
WHERE date(file.mday).weekyear = date(this.file.cday).weekyear

My help request:

Here’s the problem:
In the second table I’d like to see all the notes edited that week, but I’d also like to hide notes that were created that same week (I already have the 1st table for them).
How can I make it show older (than the note’s week) notes only?
I’ve tried adding many different “wheres”, these are 2 of my last attempts as example:

  • WHERE date(file.cday).year + date(file.cday).weekyear != date(this.file.cday).year + date(this.file.cday).weekyear

  • WHERE date(file.cday).weekyear != date(this.file.cday).weekyear

Both worked, but I think these solutions will give me problems in the future years.
Any ideas?

dataview first off, i think what you want to achieve almost similar to mine (this is disclaimer as i’m not so pro with dataview :sweat_smile:).

i think best if your reference date (in your case this.file.cday) to something more firm like below (because you might someday create those reference note in advanced)

  • this.file.day (where you have date key in your note) or
  • this.file.name (where your file name is a date format like 2022-05-23.md)

for your 2nd table, try this i guess

WHERE
    date(file.mday).year = date(this.file.cday).year AND
    date(file.cday).year != date(this.file.cday).year AND
    date(file.mday).weekyear = date(this.file.cday).weekyear AND
    date(file.cday).weekyear != date(this.file.cday).weekyear

  • this will add the numbers (like today is year 2022 and week 20, resulting in 2042 instead of 202220 like u expect

Thanks for this advice, I’ll keep it in mind!


This basically adds to my “2nd attempt” the following line:
WHERE date(file.cday).year != date(this.file.cday).year

I had the same idea and I’ve tried it a few days ago. But it doesn’t work! :sob:
I think dataview sees it as a single rule (not something that work together with the weekyear part), and so hides all the notes created this year (at least it doesn’t show me a note from the previous week edited today) :pensive:
I need a solution that combines the weekyear and the year lines together as a single rule, otherwise I could add a value in my notes that shows the year and also the week (I was trying to avoid this though :confounded:).


Right, that’s why I said this solution will give me problems in the future years (because today is 2022+21 = 2043, next year there will be 2023+20 = 2043 :sweat_smile:)

aah, i should’ve test it first. looking back at the query, yeah definitely will filter out everything in that year. i tested this, it should fit for your 2nd table requirement

table date, note-type, file.folder, file.mday
where
	date(file.mday).year = date(this.file.cday).year AND
	date(file.mday).weekyear = date(this.file.cday).weekyear AND
	date(file.cday).weekyear != date(this.file.cday).weekyear
  • the 1st filter, limits all notes modified or modified+created within “this year”
  • the 2nd filter, further limits filter 1 results to all notes modified or modified+created within “this week”
  • the 3rd filter, limits the filter 2 results to only notes modified “this week” and thus “this year” by excluding those created “this week” (and by extension, modified+created “this week” as well)

This alone excludes results based on the weekyear. That week every year.
As we tested in our previous attemp, dataview apparently reads those lines as seperate single rules. That’s why I think that believing this will work together with the rule in the first line might be a mistake.


Working on a solution, I noticed another thing that might give us problems:
date(file.mday).year = date(this.file.cday).year
The problem here is that the last week of the year (52) often ends at the beginning of January (new year)
So I changed that as follows:

WHERE date(file.mday).day >= (date(this.file.cday).day - 350)
WHERE date(file.mday).day <= (date(this.file.cday).day + 350)


After many more tests I found an overcomplicated solution that apparently works (but I don’t have many old notes to test it properly at the moment).

I’d like to hear your thoughts about it:

WHERE all((date(file.mday).weekyear = date(this.file.cday).weekyear), (any((date(file.mday).day >= (date(this.file.cday).day - 350)), (date(file.mday).day <= (date(this.file.cday).day + 350)))))
WHERE any([(date(file.cday).weekyear != date(this.file.cday).weekyear), (date(file.cday).year != date(this.file.cday).year)])

How I think it should work:

1st “where” - Shows only notes edited this note’s week:
I used the ANY function to check if one of the followings is “true”:

date(file.mday).day >= (date(this.file.cday).day - 350)
date(file.mday).day <= (date(this.file.cday).day + 350)

^(year check)

I used the ALL function to check if both the previous (year check) and the following (week check) are “true”:
date(file.mday).weekyear = date(this.file.cday).weekyear
^(week check)

2nd “where” - Hides notes created this note’s week:
I used the ANY function again. Here it compares the (file.cday).weekyear to (this.file.cday).weekyear, and the (file.cday).year to (this.file.cday).year.
date(file.cday).weekyear != date(this.file.cday).weekyear
date(file.cday).year != date(this.file.cday).year

If one or both are “!=”, as it asks, it gives “true” as result and shows the note.
If both are “false” (no difference in creation year and weekyear) the end result is “false” and the note is hidden.

TABLE file.cday, file.mday
WHERE dateformat(file.mday, "yyyy-WW") = dateformat(this.file.mday, "yyyy-WW")
WHERE dateformat(file.cday, "yyyy-WW") != dateformat(this.file.cday, "yyyy-WW")

I changed just a detail in the first line:
WHERE dateformat(file.mday, "yyyy-WW") = dateformat(this.file.cday, "yyyy-WW") WHERE dateformat(file.cday, "yyyy-WW") != dateformat(this.file.cday, "yyyy-WW")
I wrote it in the weekly note and now dataview shows me all the notes edited on the same week of the weekly note review, but hides the new notes edited but also created on the same week.
That’s exactly what I was looking for!! :star_struck:


I didn’t know I could use the “dateformat”. Most of what I’ve learned about Dataview comes from https://blacksmithgu.github.io/obsidian-dataview/ . Could you suggest me another source to expand my knowledge about this amazing tool?
Thank you so much!

You’re right, I forgot to replace the mday by the cday (because I started with other query).

About documentation, besides the official documentation (incompleted) I don’t have other sources to recommend you.
You have this “first-steps” from @AB1908 : FAQ Guide for Obsidian Dataview – Random But Interesting
But my main source is the GitHub page where you can follow the news in each release (I think it was there I found for the first time the mention to the function dateformat()) and also the issues discussions.
I’m not a coder, programmer or similar. No code languages knowledge. Only by a long learning process exploring DQL (in constant changing, as the new beta version). Maybe one day I have time to starting with js side.

Thank you!!


Well I’m learning many interesting things from you (not only here but also on other help posts I’ve read) and I’m so glad you helped me!! :blush:

1 Like

So this is the result:
A template to create weekly review notes instantly!
Even the file name is auto-generated (this also prevents creating multiple weekly review notes by accident).

And this is the code:

<% tp.file.rename(tp.date.now("YYYY-[W]w", 0)) %># <% tp.date.now("[Week ]ww-yyyy", 1) %>

<< [[<% tp.date.now("YYYY-[W]w", -7) %>]] ⬅️ ==**[[<% tp.date.now("YYYY-[W]w", 0) %>]]**== ➡️ [[<% tp.date.now("YYYY-[W]w", +7) %>]] >>

<  <% tp.date.weekday("DD[.]MM", -7) %>-<% tp.date.weekday("DD[.]MM", -1) %> ⬅️ **<% tp.date.weekday("DD[.]MM", 0) %>-<% tp.date.weekday("DD[.]MM", 6) %>** ➡️ <% tp.date.weekday("DD[.]MM", 7) %>-<% tp.date.weekday("DD[.]MM", 13) %> >

---
### Created
```dataview
TABLE (dateformat(file.cday, "EEE, dd.MM")) AS "🗓️", note-type AS "📝", file.folder AS "📂", (dateformat(file.mday, "EEE, dd.MM")) AS "⏳"
WHERE dateformat(file.cday, "yyyy-WW") = dateformat(this.file.cday, "yyyy-WW")
SORT file.cday DESC
``
---
### Edited
```dataview
TABLE (dateformat(file.cday, "EEE, dd.MM")) AS "🗓️", note-type AS "📝", file.folder AS "📂", (dateformat(file.mday, "EEE, dd.MM")) AS "⏳"
WHERE dateformat(file.mday, "yyyy-WW") = dateformat(this.file.cday, "yyyy-WW")
WHERE dateformat(file.cday, "yyyy-WW") != dateformat(this.file.cday, "yyyy-WW")
SORT file.mday DESC

:star_struck:

2 Likes

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