Dataview Grouped Count but from multiple value field

[mansto97]

What I’m trying to do

I build book database using dataview and metadata fields. In this schema I use field “date” which contains date or dates when I have read given book. It happens that some books have bean read more than once.

I try to build a neat table containing each year in 1st column and the number of books I’ve read during that year in 2nd column, possibly also further columns containing sum of pages read during the year and so on.

However, I’m unable to construct a query that will enable me to sum the books that have been read more than once.

For reference — the metadata field I use to write the date looks like this date:: 2022-02-23, 2023-06-23.

Things I have tried

I have looked through the topics with grouping problems, but the best solution I have been able to come with is this:

table length(rows) as count
from "path_to_my_folder"
where date != null
group by date.year as year

Such query results in multiple rows containing each pair of years found, e.g. 2013 and 2021, 2013 and 2022 and displaying counts of books that I’ve read both during these years.

If I change date.year to date[0].year obviously it picks only the first time I’ve read a book, but table looks way better.

Thanks for any help.

[holroy]

You need to split the list value into separate entries, so try doing:

```dataview 
table length(rows) as count
from "path_to_my_folder"
Flatten date as d
where d != null
group by d.year as year
```

That should fix the immediate issue, do however note that the field date usually populates the file.day field in combination with a date from the title of the note. How this is affected by you using multiple dates in that field, I’ve not tested.

It could be that for good measures you should consider change the field name to watchedDate or something else so as not cause any issues down the road.

[mansto97]

This is great and it works. I felt like it could have something to do with flatten but I don’t understand it yet. Thanks!