Sum / average for Dataview tables

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.

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!

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!

1 Like

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!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.