Sum / average for Dataview tables

[cosmogrammar]

Hello all, looking for some help adding/averaging entries in a Dataview table. I have implemented something very similar to this Minimalist Habit Tracker and would like to see the totals/averages for each habit over a given time period.

In my daily note, I track habits with the following in-line entries:

- get-up-time:: 07:55
- deep-work-hours:: 2.6 
- exercise:: 1
- steps:: 10098
- money-spent::  18

Things I have tried

Following the guidance from this thread, I’ve tried to implement the following:

TABLE WITHOUT ID
	sum.rows(get-up-time) / length.rows(get-up-time) AS "⏰",
	sum.rows(deep-work-hours) / length.rows(deep-work-hours) AS "🧠",
	sum.rows(exercise) AS "🏋️",
	sum.rows(steps) / length.rows(steps) as "👟", 
	sum.rows(money-spent) as "💸"
FROM "1.0 Admin/1.1 Daily Notes"
GROUP BY week
WHERE week = "Week 31 - 2022"

However, it throws the error message “-Cannot call type ‘null’ as a function”. Note that “week” is YAML that gets defined as “Week {{date:WW}} - {{date:YYYY}}” in template for the daily note. So, I’m trying to group notes from a given week and perform the above operations.

A couple other notes:

• Even if I could get this working, I suspect that average get-up-time will be calculated incorrectly due to the time format. Any tips on how to do this correctly?
• I’m fine to calculate averages as a separate table, however, it would also be cool if the averages/totals were appended to the bottom of a normal habit tracking table.

[scholarInTraining]

I am not sure this will solve everything, but you have some things a little backwards!

1. I think you want the WHERE week ... line before the GROUP BY week.
2. The punctuation for all of your sum or length bits needs to be sum(rows["get-up-time"]) or length(rows.steps). sum and length are functions and the arguments to functions go inside parentheses. Your field names inside rows can be accesssed via the . like rows.exercise (might not work if there is a - in the field name) or [ ] like rows["deep-work-hours"] (should always work).

See if fixing those gives you a different outcome / error message!

@cosmogrammar

• Even if I could get this working, I suspect that average get-up-time will be calculated incorrectly due to the time format. Any tips on how to do this correctly?
• I’m fine to calculate averages as a separate table, however, it would also be cool if the averages/totals were appended to the bottom of a normal habit tracking table.

I am not sure how smart dataview’s sum is! It may give you an error about your times not being numbers, or it might work. Once you know specifically what happens, it will be easier to strategize.

I think you might need some more advanced programming in dataviewjs to do the “append to bottom” bit. Maybe someone else will have a more specific pointer.

Good luck!

[cosmogrammar]

Thank you! I was able to use this advice to get the averages/totals working for everything but the time. I don’t think calculating “average time” is possible without some other workaround, as Dataview interprets “08:00” as a string. Maybe something to look at it in the future!