Timeline of events using Dataview, Flatten & Group By - stuck

[MatthewPetty]

I want to create a timeline of events in my life. I’ve done this before on my old website using HTML tables, but I wanted to transfer it to Obsidian.
I tried a couple of the timeline plugins, but they didn’t quite do exactly what I wanted, or were too complex in some way or another. So I went with a simple sorted table of items.

I have a set of notes tagged with lifeTimeline. These notes include fields for year, quarter, event type and so on.

tags:
  - LifeTimeline
timeline-event-type: Life Event
timeline-location: "[[Locations/Bedford]]"
timeline-start-date: 1972-06-10
timeline-year: 1972
timeline-quarter: 2

Then I have a dataview query which returns these notes sorted by year and quarter and includes a column for a short summary, and also an icon for the type of event (life event, education, accommodation and so on) derived using a FLATTEN command.

The dataview for this version works fine:

TABLE WITHOUT ID typeIcon as "T", file.link as "Event", (timeline-year + "q" + timeline-quarter) as Q, timeline-summary as "Summary"
FROM #LifeTimeline
FLATTEN {"Education": "📝","Work": "🛠️","Accomodation": "🏠", "Life Event": "❤️", "Pet": "🐱"}[timeline-event-type] as typeIcon
WHERE file.name != "template_LifeTimeline"
SORT timeline-year ASC, timeline-quarter ASC

The output:

What I want to do next is group by Year and perhaps Quarter. I’ve managed to add grouping, and display the Link for the notes by referring to rows, but the other information does not display:

TABLE rows.file.link, rows.file.timeline-summary, typeIcon
FROM #LifeTimeline 
FLATTEN {"Education": "📝","Work": "🛠️","Accomodation": "🏠", "Life Event": "❤️", "Pet": "🐱"}[rows.file.timeline-event-type] as typeIcon
WHERE rows.file.name != "template_LifeTimeline"
GROUP BY timeline-year AS Year

Output:

Problems with this:

1. Summary not displaying for each note.
2. typeIcon not displaying for each note.
3. template_LifeTimeline not being filtered out.
4. Possible to add another level of grouping for Quarter?

All help gratefully received!

[holroy]

With exception of the first line with TABLE ... stuff is available sequentially down the query. The first line is then processed at the end.

In your case this has the implications that in your WHERE clause it doesn’t know you’re about to do a group by, so you shouldn’t use rows. in front of the file name.

However, when we get to process the first line after the grouping you’ll need to add just rows. in front of the values you want to list. Not rows.file. like you did for the summary, and not nothing like you did for typeIcon.

So a better version of your query would look like:

```dataview 
TABLE rows.file.link, rows.timeline-summary, rows.typeIcon
FROM #LifeTimeline 
FLATTEN {"Education": "📝","Work": "🛠️","Accomodation": "🏠", "Life Event": "❤️", "Pet": "🐱"}[rows.file.timeline-event-type] as typeIcon
WHERE file.name != "template_LifeTimeline"
GROUP BY timeline-year AS Year
```

This should work a little better. But given some empty summaries you might discover that the grouped lines are misaligned. Bummer…

And regarding the quarterly grouping, you’re able to do multiple group by, but it’ll add a another layer of rows., so you could end up with rows.rows.typeIcon or similar monstrosities. It would be better to make the first grouping do it all. In other words to group directly by the year/quarter combination.