Suggestions on how to achieve a dynamic weekly averages line graph with a level of coding that a non-programmer could maintain

What I’m trying to do

Trying to create a dynamic line graph of weekly averages of yaml data from daily notes.

Things I have tried

I’m a non-programmer with no knowledge of js.
The yaml data pulling from looks like this in daily notes:


activity: 3
sleep: 3

//plus 8 more properties

I’ve been able to create a table with this info in dataview by using the following:

TABLE WITHOUT ID week, activity, sleep, exercise-intolerance, fatigue, brain-fog, infection, body-odor, pain-high, pain-low, muscle-stiffness

FROM "Daily Notes/2024" 
WHERE sleep >= 0
GROUP BY dateformat(file.ctime, "yyyy-WW") AS week
FLATTEN length(rows.sleep) as Count 
FLATTEN sum(rows.sleep) as Tot 
FLATTEN Tot / Count as sleep 

FLATTEN length(rows.activity) as Count 
FLATTEN sum(rows.activity) as Tot 
FLATTEN Tot / Count as activity 

…//Flatten repeated for the other 8 properties

This gives a nice table of averages, and I would like this information displayed in a line graph.
I’ve tried using this dataview table as input for Tracker and that doesn’t appear to be a current feature. I have been able to copy paste the table and get it to work as an input, but I would prefer a dynamic option that didn’t require manually updating.
I’ve looked at Obsidian Charts and it looked like the doc describes it requiring dataviewjs for interoperability. I tried following this blog post for using dataviewjs and I was not able execute it on my own when I tried modifying it for my needs.
Thought of maybe using weekly notes to store the yaml averages, and not sure how to go about that with inline queries. Looking at the doc, it looks like it would also require js…
So I was wondering if there are any suggestions from you wonderful people on how to achieve a dynamic weekly averages line graph with a level of coding that a non-programmer could maintain?
(This is also my first post, pointing out formatting or brevity suggestions if/where they occur is appreciated. Thanks!)

I’ve done a similar graph related to a gym workout, where there was multiple scales and stuff related to the workouts. Please do check that out as well, as the simplified version below here.

The gist of the tracking is to build an ordinary query which lists all the values we want to graph, and then to adapt the surrounding code to pick the correct columns for presentation, and the right axis if you’ve got varying scales.

Here is my simplified version for just two data sets (and in a bar format):

## The base data
```dataview
TABLE sleep, activity
FROM "Daily Notes/2024" 

GROUP BY dateformat(file.day, "yyyy-'W'WW") as week 

FLATTEN sum(nonnull(rows.sleep))/length(nonnull(rows.sleep)) as sleep
FLATTEN sum(nonnull(rows.activity))/length(nonnull(rows.activity)) as activity 
```

## The bar chart
```dataviewjs
const result = await dv.tryQuery(`
  TABLE activity, sleep 
  FROM "Daily Notes/2024" 
  GROUP BY dateformat(file.day, "yyyy-'W'WW") as week 

  FLATTEN sum(nonnull(rows.sleep))/length(nonnull(rows.sleep)) as sleep
  FLATTEN sum(nonnull(rows.activity))/length(nonnull(rows.activity)) as activity 
`)

const myHeaders = result.headers
const myLabels = result.values.map(d => d[0])
const myData = result.values[0].map((_, colIndex) => result.values.map(row => row[colIndex]))

const chartData = {
  type: 'bar',
  data: {
    labels: myLabels,
    datasets: [{
      label: myHeaders[1],
      data: myData[1],
      backgroundColor: 'rgba(255, 99, 132, 0.2)',
      borderColor: 'rgba(255, 99, 132, 0.9)',
      borderWidth: 1,
      yAxisID: 'left-y-axis'
    }, {
      label: myHeaders[2],
      data: myData[2],
      backgroundColor: 'rgba(99, 255, 132, 0.2)',
      borderColor: 'rgba(99, 255, 132, 0.9)',
      borderWidth: 1,
      yAxisID: 'right-y-axis'
    },
    ]
  },
  options: {
    plugins: {
      legend: {
        position: 'bottom'
      }
    },
    scales: {
      'left-y-axis': {
        title: {
          display: true,
          text: "Percentage"
        },
        type: 'linear',
        position: 'left'
      },
      'right-y-axis': {
        title: {
          display: true,
          text: "Hours"
        },
        type: 'linear',
        position: 'right'
      }
    }
  }
}

window.renderChart(chartData, this.container)
```

Which produces this output in my test scenario:

The javascript might seem overwhelming, but it’s not as hard to modify as it might seem at first. Most of the stuff is to be left as is for most of the time. If you want to add another data set you need to do the following:

  • Change the query so it has the extra data set. If you add it at the end you’re making life simpler for yourself…
  • Locate the datasets: area in the script, and copy one of these blocks after the others:
 {
      label: myHeaders[2],
      data: myData[2],
      backgroundColor: 'rgba(99, 255, 132, 0.2)',
      borderColor: 'rgba(99, 255, 132, 0.9)',
      borderWidth: 1,
      yAxisID: 'right-y-axis'
    },
  • Change the [2] on the first two lines to match the column number of your query (remember it starts at 0 for the first column)
  • Change the colors to something you like
  • Change the yAxisId to either right-y-axis or left-y-axis, depending on which range your new data set is in.

Watch your new and improved bar chart in reading mode or live preview

Some comments on the query

I’ve changed the query slightly into:

```dataview
  TABLE activity, sleep 
  FROM "Daily Notes/2024" 
  GROUP BY dateformat(file.day, "yyyy-'W'WW") as week 

  FLATTEN sum(nonnull(rows.sleep))/length(nonnull(rows.sleep)) as sleep
  FLATTEN sum(nonnull(rows.activity))/length(nonnull(rows.activity)) as activity 
```

Changes I’ve done:

  • I change the dateformat ever so slightly to become “2024-W01”, so as not to confuse a date week string with the month variant of “2024-01”
  • I’ve also used file.day which uses a date from either within the file name of your date note, or from a date property. If this doesn’t work in your case, feel free to change it back to file.cday
  • You used FLATTEN ... as Count (and Tot) multiple times, which I feel is kind of sketchy since you redefine the value over and over again. To make it a little cleaner I just calculated the value in one go
  • I’ve used nonnull(rows.propertyName) to allow for the propertyName not to exist (or not have value) in your daily notes. I explicitly tested against this, and without the nonnull( ... ) the query excluded the entire week from my result
  • When we do GROUP BY it changes the first column from the file.link into the expression we’re grouping on. So no need to do the TABLE WITHOUT ID week, when we can simply do GROUP BY ... as week

I’ll try later on to see how hard it is to transform this bar chart of multiple categories into a line chart, but I’m running a little out of time for today’s answering… :smiley:

It was extremely hard and taxing to change it into a line chart. You need to change the line with type: 'bar' into type: 'line':smiley:

And now it looks like: