Calculate Duration after Group By

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 as "Title", 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.

Thank you in advance.

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:

TABLE as "Title", as "Author", 
     rows.rating as "☆", 
    rows.dur as "Duration"
FROM #book/finished 
     date(datefinished)-date(datestarted)  as dur

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

Update: corrected placement of FLATTEN to be after FROM

1 Like

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’

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

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