Group by year, group by month

Things I have tried

hello,

I want just a table that gives all the files in grouped order
year.-.-.-.-.-.-. month .-.-.-.-. file
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.
2020 .-.-.-.-.-.-January.-.-.-.- file1
.-.-.-.-.-.-.-.-.-.-March-.-.-.-.-.file4
2019.-.-.-.-.-.-.-February.-.-.-.file5
-.-.-.-.-.-.-.-.-.-.April-.-.-.-.-.-.-file9
-.-.-.-.-.-.-…-.-.-May.-.-.-.-.-.-.-file15
2017.-.-.-.-.-.-.-January.-.-.-.-.file23
etc.

my YAML goes:

TABLE rows.file.link
WHERE type="ABC"
Group by (year + ", " + dateformat(dat-beg,"MMMM")) AS YM
Sort YM desc

…[year] is a field in the dataview header

But I think you just have to catch the year and month first and then group or sort to reach the goal, but what do I know :wink:

however my head aches and smokes…

is that possible to reach anyway?

best regards
anh

Three notes:
1 - what you show us is a QUERY, not the yaml
2 - “[year] is a field in the dataview header” - this is an absolute mystery! What’s is a field in dataview header?
3 - you need to show us the used fields and the format you use (for example, if a valid date format, etc.)…

otherwise our answers are “shots in the dark”.

1 Like

well, try to get some light in the dark

---
type: ABC
year: 2017
dat-beg: 2017-09-29
dat-end: 2017-09-29
---

that’s what stands in my dataview
‘header’ was a mismatch of mine… sorry for that messing

regards
anh

This is the yaml frontmatter (an independent thing from the dataview plugin …):

---
type: ABC
year: 2017
dat-beg: 2017-09-29
dat-end: 2017-09-29
---

This is a dataview query:

TABLE WITHOUT ID YM AS "year | month", rows.file.link AS files
WHERE type = "ABC"
SORT dat-beg ASC
GROUP BY (year + " | " + dateformat(dat-beg,"LLLL")) AS YM
SORT rows.year DESC
1 Like

Thanks mnvwvnm,

This is the yaml frontmatter (an independent thing…

I will keep that in mind.
Well, we (you) come closer to the goal:


this is what spitted out.

SORT rows.year DESC

does its job but

SORT dat-beg ASC

seems it doesnt, and it seems its more complicated

regards
anh

dat-beg is present in all files, right?

1 Like

dat-beg is present in all files, right?

no, most but not in all files

But in all files

WHERE type = "ABC"

definitely.

But only files with a date in format “YYYY-MM-DD” will give us the month! And a date order. Without that (unless the creation date) we can’t order things by a date.
Starting with the initial query:

TABLE WITHOUT ID YM AS "year | month", rows.file.link AS files
WHERE type = "ABC"
SORT dat-beg ASC
GROUP BY (year + " | " + dateformat(dat-beg,"LLLL")) AS YM
SORT rows.year DESC

if you add WHERE dat-beg this assure that we dealing only with files with a specific date:

TABLE WITHOUT ID YM AS "year | month", rows.file.link AS files
WHERE type = "ABC"
WHERE dat-beg
SORT dat-beg ASC
GROUP BY (year + " | " + dateformat(dat-beg,"LLLL")) AS YM
SORT rows.year DESC

About your unordered results, another question: you follow the order of the commands in the query? Order matters.

1 Like

I 've adopted the code 1:1, in the right sort order
with

WHERE dat-beg

i got the same (unordered) results as above.
:man_shrugging:

oh, I see.
I don’t know why but I was thinking in only one file per month.
Try a little change:

TABLE WITHOUT ID YM AS "year | month", rows.file.link AS files
WHERE type = "ABC"
WHERE dat-beg
SORT dat-beg ASC
GROUP BY (year + " | " + dateformat(dat-beg,"LLLL")) AS YM
SORT (rows.year[0]) DESC
1 Like

it does a little change… but not quit right
_image_01

change last line for SORT (rows.year[0]) DESC, (rows.dat-beg[0]) ASC

1 Like

done, as you said,
… its going to be warmer,
its sort asc at year and asc at month though, but now it only has to be descending at year… that would be the point on the i

this is difficult because I’m starting from the point your files and the query are according with all the steps.
If you prefer, create some files with your basic metadata and send to me to test in my vault. with that we can compare same source: in your vault and in my vault.

(just see my test query)

1 Like

Thanks alot,
so as often, the problem sits right before the screen… :sweat_smile:
regards
anh

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