Dataview Monthly summary group by week

[DevinBaillie]

What I’m trying to do

I have daily notes: 2023-10-04, weekly notes: 2023-W40, and monthly notes: 2023-10. In my monthly note, I pull all the highlights from my daily notes for review. I’m wondering whether there’s any way to group the entries by week? Even better if the group can have a link to the weekly note.

TABLE WITHOUT ID
file.link AS "Date",
highlight AS "Highlight"
FROM "Journal/1. Daily"
WHERE dateformat(file.day, "yyyy") = substring(string(this.file.name),0,4)
AND dateformat(file.day, "MM") = substring(string(this.file.name),5,7)
AND highlight != null
SORT file.day

Things I have tried

I tried adding:

GROUP BY dateformat(file.day, "WW")

but that breaks the query completely, I get one row for each week in the month, but they are all blank. Any ideas?

Thanks in advance for any help.

[holroy]

Whenever you add a GROUP BY statement, all the fields are gathered into groups like rows.fields. In your case you’d need to change to the column definitions to something like rows.highlight as Highlight, and possibly rows.file.link as Date. Not entirely sure on how your data looks in your setup.

If you change your grouping expression to match the weekly format, you should be able to change to the ordinary TABLE (and not TABLE WITHOUT ID) and get the link to you weekly note. As it stands you’ll either way get a small issue next year when you enter the same week as we’re currently in…

[DevinBaillie]

Thanks, that got me closer to what I wanted, I think.

I tried:

TABLE
rows.file.link AS "Date",
rows.highlight AS "Highlight"
FROM "Journal/1. Daily"
WHERE dateformat(file.day, "yyyy") = substring(string(this.file.name),0,4)
AND dateformat(file.day, "MM") = substring(string(this.file.name),5,7)
AND highlight != null
GROUP BY link(dateformat(file.day, "yyyy'-W'WW"))
SORT file.day

Which kinda works with a couple of issues:
The heading of the first column is “dateformat(file.day, “yyyy’-W’WW”)”
The highlights are no longer in line with the dates. The date column is just a list of the daily notes in that week, and the highlights column is a list of highlights from that week. If one day has a lot of highlights, and another has very few, then they don’t line up.

The first issue, I tried to fix with:

TABLE WITHOUT ID
link(dateformat(file.day, "yyyy'-W'WW")) AS "Week",
rows.file.link AS "Date",
rows.highlight AS "Highlight"
FROM "Journal/1. Daily"
WHERE dateformat(file.day, "yyyy") = substring(string(this.file.name),0,4)
AND dateformat(file.day, "MM") = substring(string(this.file.name),5,7)
AND highlight != null
GROUP BY link(dateformat(file.day, "yyyy'-W'WW"))
SORT file.day

but that just leaves the first column empty.

Any thoughts on how to fix these issues?

[holroy]

The country name could be fixed by adding “as Date” at the end of the group by statement like you did in one of the earlier versions.

And the lists of entries with varying lengths are yet another side effect of the grouping, as I tried explaining in the previous response. If you don’t want them grouped together you need to not do the GROUP BY.

It might sound like you’d rather want the no grouping, and rather a sort by date in general it seems.