I’m trying to calculate the total amount and average amount spent during the month on certain activities.

I’ve been following the instructions in this thread and this but without any success.

I’ve tried 2 different syntaxes:

TABLE sum(rows.minutes) as “ Tot”,
length(rows.minutes) as count,
sum(rows.minutes) / length(rows.minutes) as “ Avg”,
min(rows.minutes) as “ Min”,
max(rows.minutes) as “ Max”,
FROM “Archive /Journal /Days”
WHERE MONTH = [[2023-Jan]]

and

TABLE without ID
sum(rows[“minutes”]) as “ Tot”,
length(rows[“minutes”]) as count,
sum(rows[“minutes”]) / length(rows[“minutes”]) as “ Avg”,
FROM “Archive /Journal /Days”
WHERE MONTH = [[2023-Jan]]

Which both return the same type of error:

which doesn’t make sense to me since the syntax seems correct.

There is a syntax error in your query – commas aren’t allowed after the last item before FROM.

But maybe we could start with how your data is defined. It would be helpful if you could post some examples of your source data. I can see that your query is expecting a “minutes” field, but I’m not sure how you’re using it your journal pages.

For this post, I’m going to make an assumption that each of your journal pages you have a single minutes field in the YAML:

minutes: 10

or perhaps in the body text:

minutes:: 10

If that’s the case, then a query like this would give you the count, total, average, min, and max for these fields:

```dataview
TABLE WITHOUT ID Count, Total, Average, Minimum, Maximum
FROM "Inbox"
WHERE minutes
GROUP BY ""
FLATTEN length(rows.minutes) as Count
FLATTEN sum(rows.minutes) as Total
FLATTEN Total / Count as Average
FLATTEN min(rows.minutes) as Minimum
FLATTEN max(rows.minutes) as Maximum
```

(Of course, you should replace the FROM "Inbox" and WHERE minutes with clauses that make sense for your vault.)

thanks for looking into this and proposing a detailed solution. To confirm your assumption, yes I do collect this data as in-body text, in the form of minutes:: 10. However, the count, total, avg. etc. field don’t exist in the daily note where I capture “minutes”.

This might seem trivial, but I believe it’s worth clarifying since copy-pasting the query provided didn’t yield the expected result:

TABLE WITHOUT ID Count, Tot, Avg, Min, Max
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
GROUP BY ""
FLATTEN length(rows.minutes) as Count
FLATTEN sum(rows.minutes) as Tot
FLATTEN Tot / Count as Avg
FLATTEN min(rows.minutes) as Min
FLATTEN max(rows.minutes) as Max

The error you’re seeing is due to the fact that your FROM clause is including pages that don’t have a “minutes” field in them. I recommend adding a “WHERE minutes” to narrow the query to just pages with data.

Try this:

```dataview
TABLE WITHOUT ID Count, Tot, Avg, Min, Max
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
GROUP BY ""
FLATTEN length(rows.minutes) as Count
FLATTEN sum(rows.minutes) as Tot
FLATTEN Tot / Count as Avg
FLATTEN min(rows.minutes) as Min
FLATTEN max(rows.minutes) as Max
```

TABLE WITHOUT ID Count, Tot, Avg, Min, Max
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
GROUP BY ""
FLATTEN length(rows.minutes) as Count
FLATTEN sum(rows.minutes) as Tot
FLATTEN Tot / Count as Avg
FLATTEN min(rows.minutes) as Min
FLATTEN max(rows.minutes) as Max

My best guess from the error message is that the sum() operation in the following line is encountering a null value:

FLATTEN sum(rows.minutes) as Tot

This would be caused if one or more of the source pages had a null or missing value for minutes. But I don’t see how that’s possible, since the WHERE minutes clause should have filtered out any pages with a null or missing minutes field.

I’m feeling a bit stumped here – that same query works perfectly in my vault. The only changes I made were to substitute a different folder for the FROM clause and remove the WHERE MONTH... clause.

I suggest looking at the source data pages. Do any of them have unusual values for the minutes field?

EDIT: Also, are you running the latest version of the Dataview plugin? I’ve seen a few problems clear up on upgrade.

Hmm, I thought perhaps the highlight might be the issue, but that gives a different error. I’m not sure what we’re doing differently that it works for me but not for you.

It showcases whether you’ve got any faulty data. So you should be able to run the main script on “A” since that didn’t find any non numbers. What was the result whennrunning the main script on “A”?

On “B” however, you need to go through every file in the list and change those entries having the “*” as value for minutes, as thoseb files will trip up the main script. After cleaning up (and getting a “no result” there as well), then what do you get when doing the main script for “B”?

Good catch! I agree with @holroy , it looks like you have some pages where the minutes value isn’t a number, and that could be what’s causing the script to fail.