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

Help
,
2

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:

image
image1309×929 27.4 KB

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: