Is there a good tutorial on how to do averages and sums in Dataview?

[lousytrybrian]

Things I have tried

I’m giving Obsidian another go from Roam and I feel like Dataview is an untapped gold mine for me.

I want to add some front matter like Phone Pickups, Screen Time, and maybe my focus level for the day (scale of 1-10) and then create a table that can show each day and do either an average for the month and/or sum the total phone pickups etc.

I’ve not been able to find a good resource about sums and averages in Dataview yet. I’m still trying to wrap my head around Dataview and I think I get how to do tables and lists, but not sure how to do Sums or Averages yet.

What I’m trying to do

See the part above.

My front matter will look something like this:

Phone Pickups: 85
Focus Level: 7

And I have no idea how to average these by the week or month or year.

Thanks for any help or direction to a good blog post or resource!

[dugdun]

HI @lousytrybrian you can use the sum() and length() functions to get what you want.

Something like the following will get you monthly averages:

```dataview
TABLE
		sum(rows.PhonePickups) as total
	,	length(rows.PhonePickups) as count
	,	sum(rows.PhonePickups) / length(rows.PhonePickups) as avg
	,	min(rows.Date) as min
	,	max(rows.Date) as max

FROM
		"DV"
GROUP BY
		date(Date).year + "-" + date(Date).month as date
WHERE
		rows.date
```

This will return data that looks like the following:

My frontmatter for each document in the grouping looks like the following:

---
PhonePickups: 1
FocusLevel: 7
Date: 2022-01-01
---

You can get weekly averages by using date(Date).weekyear in place of date(Date).weekmonth in the above example, but it’s not quite right for those dates at the beginning of the year at are part of the last week in the previous year.

Play around with the above to see if you can get what you’re looking for.

[mnvwvnm]

That’s a clever way to group the pages.
You can also use round(sum(rows.PhonePickups) / length(rows.PhonePickups), 2) as avg to round the number and specify the number of decimal places.