Dataview/Tracker Summary of multiple tags

[harveyd]

Hi All,

I’m experimenting with Dataview and Tracker to create a mood / habit tracker. I’m using frontmatter to log the data in the following format:

---
mood: 7
sleep: 8
food: 5
anxiety: 1
...
---

I’ve been able to use Trackers Summary feature:

searchType: frontmatter
searchTarget: sleep
folder: /Tracker/Daily
summary:
    template: "Minimum: {{min()}}\nMaximum: {{max()}}\nMedian: {{median()}}\nAverage value of sleep is: {{average()}}/10"
    style: "color:white;"

I’d like to take this to the next step and have a single table (or similar) that takes multiple data points (frontmatter) and then shows the min/max/avg etc for each. Frontmatter as the rows and min/max/avg etc as the columns.

I probably don’t want it for every bit of information within frontmatter, just the ones I specify.

Is this achievable with Dataview or Tracker?

Many thanks.

[holroy]

@harveyd

Is this achievable with Dataview or Tracker?

I’ve not a whole lot experience with Tracker, but I know it’s doable with Dataview, so try something like the following:

```dataview
TABLE WITHOUT ID
  tmp[0] as Title,
  tmp[1] as Values, 
  min(tmp[1]) as min, 
  max(tmp[1]) as max, 
  average(nonnull(tmp[1])) as avg
WHERE file.folder = this.file.folder
GROUP BY file.folder
FLATTEN list(
  ["Mood", rows.mood],
  ["My anxiety", rows.anxiety],
  ["Hungry", rows.food] ) as tmp
```

Which in my constructed examples produced this output:

The query showcases how you can add titles to your result set, how to list out the given array of values using the rows.attribute syntax (since we’ve grouped by file.folder). Another option for grouping, could simply be GROUP BY true if you want to check statistics on your entire vault.

Note how average() is not very keen on having null values, so there one needs to add the nonnull() around the response to get rid of any null values within your data.

Hopefully this should get you going, and build your final query of statistics.

[harveyd]

@holroy

```dataview
TABLE WITHOUT ID
  tmp[0] as Title,
  tmp[1] as Values, 
  min(tmp[1]) as min, 
  max(tmp[1]) as max, 
  average(nonnull(tmp[1])) as avg
WHERE file.folder = this.file.folder
GROUP BY file.folder
FLATTEN list(
  ["Mood", rows.mood],
  ["My anxiety", rows.anxiety],
  ["Hungry", rows.food] ) as tmp

Hi @holroy,

Thank you so much for taking the time to respond. This worked perfectly and have been able to utilise the logic in a number of places.

Thank you again

[harveyd]

@holroy I’ve moved the Day files in /Tracker/Daily in to month folders e.g.

This broke the above from working. I was specifically using:

TABLE WITHOUT ID
	tmp[0] as Health,
	min(tmp[1]) as min, 
	max(tmp[1]) as max, 
	round(average(nonnull(tmp[1])),1) as avg
WHERE file.folder = "Tracker/Daily"
GROUP BY file.folder
FLATTEN list(
	["Mood", rows.mood],
	["Anxiety", rows.anxiety],
	["Sleep", rows.sleep],
	["Food", rows.food] )
	as tmp 

I now get:

Many thanks

[harveyd]

As soon as I posted my last message, I think fixed it.

TABLE WITHOUT ID
	tmp[0] as Health,
	min(tmp[1]) as min, 
	max(tmp[1]) as max, 
	round(average(nonnull(tmp[1])),1) as avg
FROM "Tracker/Daily"
GROUP BY tmp
FLATTEN list(
	["Mood", rows.mood],
	["Anxiety", rows.anxiety],
	["Sleep", rows.sleep],
	["Food", rows.food] )
	as tmp 

This seems to work. Is this decent solution?

[holroy]

Hmm… GROUP BY tmp at that position will not group by any folder, but rather on a non existing value, which again would equate grouping on true or similar. This means that it’ll group together every entry, and do the statistics part on all the notes in this part of your vault. Aka not monthly, but every related note in the Tracker/Daily section of your vault.

If you’d like the monthly statistics I think you should keep the GROUP BY file.folder, and if you want for every related note, I personally think it’ll look better if you do GROUP BY true. That’ll show your intentions clearer, then using a side effect of tmp not being defined yet…

[harveyd]

Makes sense. Thank you for the clarification.