Calculate Duration after Group By

[obsidian337]

What I’m trying to do

I have several book summaries which have ‘datestarted’ and ‘datefinished’ metadata In YAML like this.

datestarted: 2023-03-07
datefinished: 2023-03-15

I’d like to show the reading durations for each book.

Things I have tried

TABLE 
     rows.file.link as "Title", 
     rows.author as "Author", 
     rows.rating as "☆", 
     date(rows.datefinished)-date(rows.datestarted)  as "Duration"
FROM #book/finished 
GROUP BY dateformat(datefinished,"yyyy") as Year

Without Duration part, it works well.
But, I can’t figure out how to calculate the duration between datefinished and datestarted.

I found the following topic to calculate durations with no success.

How to Calculate Duration between Dates in Dataview

You can’t repeat the same field key in yaml. You can’t create “groups” in yaml (neither in inline fields). In inline fields you can do this: Name:: Jack blabla... Name:: Jill but the result is an array similar to this in yaml: Name: - Jack - Jill Each field is an array (multiple values) without direct relation with values in other fields. Name: - Jack - Jill Born: - 1854-07-24 - 1955-07-27 No direct relation with Jack and 1854-07-24 (only the first in the list).

Thank you in advance.

[holroy]

The trick is to move it out of the column definition part, and into a FLATTEN part, before the grouping. So try something like the following:

```dataview 
TABLE 
     rows.file.link as "Title", 
     rows.author as "Author", 
     rows.rating as "☆", 
    rows.dur as "Duration"
FROM #book/finished 
FLATTEN 
     date(datefinished)-date(datestarted)  as dur

GROUP BY dateformat(datefinished,"yyyy") as Year
```

Update: corrected placement of FLATTEN to be after FROM

[obsidian337]

Thank you for your help.

For me, it worked well after exchanging the sequences between FLATTEN and FROM.
Before exchange,
I’ve got an error - Dataview: Unrecognized query operation ‘tag’

[holroy]

Sorry, my bad. FLATTEN should always be after the FROM. That was a typo by me.