Dataview "Group By" Help

[phlepper]

I have daily notes (with a name format of “yyyyMMdd ccc” like “20240206 Tue”) within which some have a “Run Distance” field for the distance I ran that day. I would like to get a sum of the distance I ran each week for the last four weeks.

Things I have tried

I started with this query:

Table without ID durationformat(date(today) - date(file.name, "yyyyMMdd ccc"), "w") as "Week", sum(run-distance) as "Distance"
from "@Journal/Daily/2024"
where date(today) - date(file.name, "yyyyMMdd ccc") < dur(4 weeks)
where run-distance > 0

this gives the following results:

Week	Distance
3	7.32
3	6.29
2	6.32
2	1
2	6.31
1	1.25
1	6.31
1	7.3
0	1.05
0	6.31
0	1.25
0	6.29

So far, so good! I then added a group by (group by Week) which gave me an error:

Dataview: Every row during final data extraction failed with an error; first 3:

            - No implementation of 'date' found for arguments: null, string

So then I changed the query to move the calculation of week to the Group By line as I’ve seen in some examples on this forum:

Table without ID Week, sum(run-distance) as "Distance"
from "@Journal/Daily/2024"
where date(today) - date(file.name, "yyyyMMdd ccc") < dur(4 weeks)
where run-distance > 0
group by durationformat(date(today) - date(file.name, "yyyyMMdd ccc"), "w") as "Week"

This now gives this result:

Week	Distance
0	-
1	-
2	-
3	-

Which seems pretty close. But how do I get the Distance to be the actual sum versus the dash?

I’ve tried various combinations of Group By and Flatten for both Week and Distance as I’ve seen other examples on this forum, but honestly, most of those are much more complicated than mine (multiple group by, multiple flatten), so they haven’t helped me get closer to a solution.

Thanks for any feedback!

[holroy]

You’re indeed very close, but you do need to remember that when doing GROUP BY all the fields are moved into a list within the rows object. So you could get a listing of your run variable doing rows.run, and it would list all the run’s of that week. However, doing a sum(rows.run-rows.distance) wouldn’t be correct (and I’m not it’s even legal to write that…), so we need to do a little more trickery to your query.

Try the following and see if that gives your expected result:

```dataview
TABLE sum(rows.runLength) as "Distance"
FROM "@Journal/Daily/2024"
WHERE date(today) - date(file.name, "yyyyMMdd ccc") < dur(4 weeks)
FLATTEN run-distances as runLength
WHERE runLength > 0
GROUP BY durationformat(date(today) - date(file.name, "yyyyMMdd ccc"), "w") as "Week"
```

Here we first calculate the runLength for each entry, before we group the entries, and calculate the sum of those using sum(rows.runLength).

[phlepper]

Thanks! I didn’t realize the issue with Group By making ‘rows’.

And actually, the “sum(rows.distance)” works fine, so I didn’t have to do the extra work with runLength and flatten.

Table without ID Week, sum(rows.run-distance) as "Distance"
from "@Journal/Daily/2024"
where date(today) - date(file.name, "yyyyMMdd ccc") < dur(4 weeks)
where run-distance > 0
group by durationformat(date(today) - date(file.name, "yyyyMMdd ccc"), "w") as "Week"

Gives this:

Week	Distance
0	13.85
1	14.66
2	14.879999999999999
3	13.61

Which is what I wanted. So, thanks again!
phlepper

[holroy]

I was a little unsure about the whole run - distance thingy, as I didn’t understand fully how you’d listed your data in the first place. So a good thing you understood how to modify it.

And if for nothing else, using the FLATTEN shows how you’d do something similar if you wanted to pre-calculate something and then sum it up afterwards.