Count occurrences of an item in front matter on a monthly and weekly basis

What I’m trying to do

Hi. I’m new to using Obsidian to anything other than writing notes but I’ve understood that it can do so much more.
In my daily journal template I’ve added a field called “trained” where I write “Yes” if I’ve worked out that day. It’s kept blank if I haven’t. Now I’m trying to create two tables:

  1. The first table should have the columns “Year”, “Month” and Count. The count should be the number of days with “Yes” in a given month and year.
  2. The second table should give the same information but for a given week or for a date range I specify (if that is easier to do). This table should be in my weekly journal.

The path to the daily journal files with the relevant front matter is Journal/XXXX/Daily, where XXXX is the year.

Things I have tried

I’ve tried to figure out how to write a query in dataview but with limited success. The one that got the furthest was the following, which at least gave me a count:


TABLE WITHOUT ID created as Date, trained as "Trained", length(rows) as Count

WHERE trained

GROUP BY trained

This version only gave me an empty table:

const results = {};

dv.pages('"Journal"')
  .where(p => p.trained === "Yes" && p.created)
  .forEach(page => {
    const year = page.created.toFormat("yyyy");
    const month = page.created.toFormat("MM");
    const key = year + "-" + month;

    if (!results[key]) {
      results[key] = { Year: year, Month: month, Count: 1 };
    } else {
      results[key].Count += 1;
    }
  });

const tableData = Object.values(results);
dv.table(["Year", "Month", "Count"], tableData.map(row => [row.Year, row.Month, row.Count]));

I can’t figure out how to get the count to group by the right date columns. After trying this site, Google and ChatGPT I’m trying my chances here. I know how to write SQL, which dataview seems to be fairly similar to but apparently not similar enough for me to figure it out…

Thanks in advance for any help or pointers.