Group by year, group by month

[mr_anh_ngoc]

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

however my head aches and smokes…

is that possible to reach anyway?

best regards
anh

[mnvwvnm]

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”.

[mr_anh_ngoc]

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

[mnvwvnm]

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

[mr_anh_ngoc]

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

[mnvwvnm]

dat-beg is present in all files, right?

[mr_anh_ngoc]

dat-beg is present in all files, right?

no, most but not in all files

But in all files

WHERE type = "ABC"

definitely.

[mnvwvnm]

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.

[mr_anh_ngoc]

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

WHERE dat-beg

i got the same (unordered) results as above.

[mnvwvnm]

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

[mr_anh_ngoc]

it does a little change… but not quit right

[mnvwvnm]

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

[mr_anh_ngoc]

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

[mnvwvnm]

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)

[mr_anh_ngoc]

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