What I’m trying to do
I’m currently using periodic notes to write things about my week. I’m currently using one note per week where I compile different info.
For a long time I’ve been looking for ways to track and visualize daily activities. A few examples of what I want to track:
- Swim for 60 mins
- Run for 75 mins
- Watch [name of tv show] for 60 mins
- Play [name of video game] for 100 mins
At the end of the year I would like to visualize this information in different ways such as:
- How long I spent swimming
- A list of movies I watched
Things I have tried
I’ve tried to use inline data annotation in the notes but repeating key values is leading to unintended behavior.
So let’s say that in the one note I have the following
Monday:
activity:: Swim
activity-duration:: 60 minutes
Tuesday:
activity:: Swim
activity-duration:: 60 minutes
And in another note in the same folder:
Monday:
activity:: Swim
activity-duration:: 30 minutes
I then started to explore different queries.
List activities:
TABLE activity, activity-duration as "Duration"
FROM "Weekly Notes"
SORT file.name DESC
WHERE activity != null
Then I tried grouping and using sum:
TABLE activity, sum(rows.activity-duration) as "Duration", length(rows.activity) as count
FROM "Weekly Notes"
GROUP BY activity
SORT file.name DESC
WHERE activity != null
And then I explored with FLATTEN and GROUP BY but I started to get weird behavior.
Is there a way to accomplish such thing or any ideas where I can take a different approach to get this?
Thanks