Tracking daily questions in weekly notes with inline fields dataview

Hello everyone!

I track daily questions with inline fields. Syntax as following:

Happy:: 8 ; 2024-01-31

8 is rating from 1 to 10 and ; is separator before the date which belongs to the daily question.

I have following dataview query which shows me value and the date separated as two value.

```dataview
TABLE
split(Happy, " ; ")[0] AS "Happy Rating",
split(Happy, " ; ")[1] AS "Date"
FROM #weeklynotes
WHERE contains(Happy, "")
flatten Happy
SORT split(Happy, " ; ")[1] desc

This is great, but I want to sum rating values and group it by for example the month. Using January as example, the query takes first value before ; and sum up it together and divides by 31. Also, uses the date after ; as date object for grouping the values.

Is it possible to achieve by using my inline field syntax?

Thank you for help!

There are room for improvements here, and I think it should be doable what you’re asking for. Let us start with the improvements:

```dataview
TABLE h.rating as "Happy Rating", h.date as "Date"
FROM #weeklynotes
WHERE Happy
FLATTEN map(flat(list(Happy)), (m) => split(m, " ; ")) as rec
FLATTEN object(
  "rating", rec[0],
  "date", date(rec[1]) ) as h
SORT h.date desc
```

Does this show the same output as your original query?

Regarding the second part. It’s hard to make the query consider which month has how many days, and do you really want to use that to calculate a monthly happy rating?

Wouldn’t it better to average the happy rating for each day, and then average it for all entries in that month? It would for sure be easier to do at least.

Assuming my previous query works (untested as it is), lets continue with averaging the days:

```dataview
TABLE average(rows.h.rating) as "Happy Rating"
FROM #weeklynotes
WHERE Happy
FLATTEN map(flat(list(Happy)), (m) => split(m, " ; ")) as rec
FLATTEN object(
  "rating", rec[0],
  "date", date(rec[1]) ) as h
GROUP BY h.date 
```

Which could be further extended into something like the following for monthly ratings:

```dataview
TABLE average(rows.dailyRating) as "Happy Rating", length(rows) as "Day count"
FROM #weeklynotes
WHERE Happy
FLATTEN map(flat(list(Happy)), (m) => split(m, " ; ")) as rec
FLATTEN object(
  "rating", rec[0],
  "date", date(rec[1]) ) as h
GROUP BY h.date 
FLATTEN average(h.rating) as dailyRating
GROUP BY dateformat(key, "YYYY-MM") as Month
```

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