Display completed tasks in a line chart - No Daily Notes

Whopperator · March 15, 2024, 10:44am

What I’m trying to do

Hello,

I “simply” want to display a line graph of all tasks from a certain tag that I completed within the last 7 days.
I want to display multiple lines in one graph.

Things I have tried

The query for my data would be this:

```dataview
TASK
WHERE completed
AND contains(tags, "#Post") 
AND contains(path, "ProjectOne")

Ive found a different format that supposedly returns the same data:

const tasks = dv.pages().where(p => p.completed && p.tags.includes("#Post") && p.path.includes("ProjectOne"));

Now Im not sure wether the last code snippet is already a working way of querying the data. It is not clear to me how I can easily check the values stored in “tasks”. But onward:

For a chart / line graph I need two dimensions. So for the y I would need to have these values:

0 = no completed tasks of a date
1 = one completed tasks with the same date
2= two completed tasks with the same date
etc

Dataview offers the option “Automatic Task completion tracking” which automatically adds the metadata completion:: to completed tasks. So this data should be usable. I just dont know how exactly.

As far as I understand the example below, a chart simply adds the numeric values and the corresponding labels without any connection of the two. This means fo the y dimension, the list needs to include the 0 values for the days where no tasks where completed, otherwise it would not sync up with the dates on the x dimension. This information can not be generated from the tasks query alone.

So we need to create a list of the dates of the last 7 days. These dates would be the “Labels” for the x dimension. For the y values, we need to query the tasks as above, and then compare the dates of the entries of both lists and form a third one which consists of the corresponding values for each of the 7 days from the first list.

Column 1	Column 2	Column 3
List of Days	List of Tasks	List of values for the y dimension
2024-03-08-Friday		0
2024-03-09-Saturday	Post1 completed::2024-03-09-Saturday, Post2 completed::2024-03-09-Saturday	2
2024-03-10-Sunday	Post3 completed::2024-03-10-Sunday	1
2024-03-11-Monday		0
2024-03-12-Tuesday		0
2024-03-13-Wednesday	Post4 completed::2024-03-13-Wednesday, Post5 completed::2024-03-13-Wednesday	2
2024-03-14-Thursday		0

At least thats what I figured. I could get this to work in python but I have no idea how to do that in javascript, let alone dataview. Chat GPT seems to have its troubles with dataview syntax, too.

I use a great snippet for my daily notes that tracks certain metadata that I fill out. This script works perfectly fine (I just need add a step to translate the time format I enter in the note (e.g. “0930”) to a format with 0-100 decimal steps (instead of 0-60) that the graph expects). Since I create a daily note every day, it doesnt have a method to include “no-entry” days. But it shows how the line chart is created:

```dataviewjs
const journalPath = '"DailyNotes"';
const chartColor = '#3e4f79';
const chartColor2 = '#A40E4C';

const dailyNotesPlugin = app.internalPlugins.plugins['daily-notes'];
const dateFormat = dailyNotesPlugin.instance.options.format;
var labels = [];
var alarm = [];
var gotup = [];

var sortByName = (a, b) => moment(a, dateFormat).format('YYYYMMDD') - moment(b, dateFormat).format('YYYYMMDD');
var latest_journals = dv.pages(journalPath)
    .sort(j => j.file.name, 'asc', sortByName);

for (var i=0; i < latest_journals.length; i++) {
    var j = latest_journals[i];
    if (j['Alarm'] !== undefined) {
        alarm.push(j['Alarm']);
        labels.push(j.file.name);
    }
}
for (var i=0; i < latest_journals.length; i++) {
    var j = latest_journals[i];
    if (j['Got-up-at'] !== undefined) {
        gotup.push(j['Got-up-at']);
        
    }
}

const limitedLabels = labels.slice(0, 14); 
const limitedAlarm = alarm.slice(0, 14);
const limitedGotup = gotup.slice(0, 14);


const lineChart = {
    type: 'line',        
    data: {
        labels: limitedLabels,
        datasets: [{
            label: 'Alarm',
            data: limitedAlarm,            
            backgroundColor: [chartColor],
            borderColor: [chartColor],
            borderWidth: 1,
            fill: true,
            tension: 0.3,
            transparency: 0.15,             
        },{
            label: 'Got Up At',
            data: limitedGotup,
            backgroundColor: [chartColor2],
            borderColor: [chartColor2],
            borderWidth: 1,
            fill: true,
            tension: 0.3,
            transparency: 0.15, 
        }]
    }
}
window.renderChart(lineChart, this.container);

I hope that this script is a good starting point. It returns this graph, which helps me track my mornings of the previous 14 days (for the new line graph, I would set the fill to 0 and tension to 1).

I think charts are a great way to visualize progress or activity across the vault and I hope this is not more complicated than I think. Thank you for your time.

holroy · March 15, 2024, 6:39pm

I’m having trouble understanding from where you’re going to count your tasks and how you’ll get the date from these tasks, but the general approach I would take to solve your issue is the following:

Start out with a query limiting to the last week notes with the tasks you want to graph
Use something like FLATTEN filter(file.tasks, (t) => t.completed) as completedTasks
Now you should be able to do length(completedTasks) to get the number of completed tasks, and just the completedTasks.text to get the text of those tasks
If you need to gather tasks from multiple days, you could then find your date and do something like GROUP BY <your date expression>, and change calculation of tasks to something like sum(map(rows.completedTasks, (m) => length(m)))

Hope this helps you moving forward… It’s the best I can do with the sparse information you’ve given…

Whopperator · March 16, 2024, 10:10am

Hello, thank you for your reply. I will try and clarify things.

As I mentioned, dataview adds the completion date to completed tasks. As I read in this thread this information can be called by

TASK 
WHERE completion != null AND completion = date("YOUR DATE IN FORMAT YYYY-MM-DD")

also it does suggest the following query for pages with completed tasks of the last 7 days:

TABLE WITHOUT ID (filter(file.tasks, (t) => t.completion >= date(now) - dur(7 d)).text) AS " task completed this week"
WHERE file.tasks.completed AND file.tasks.annotated

However I think the main issue are the days where 0 tasks were completed. Since I would like the information to be displayed in a chart, if these days are not accounted for, the line graph would be misleading. The only way I can figure how these days could be included is by having a loop compare the list of tasks with the list of dates and count the instances.

So a script would need to generate a list of the last 7 days, I assume something like

function getLastSevenDays() {
    const dates = [];
    for (let i = 6; i >= 0; i--) {
        const date = new Date();
        date.setDate(date.getDate() - i);
        dates.push(date.toISOString().split('T')[0]); // Get date in yyyy-mm-dd format
    }
    return dates;
}

And then compare that list with the list of the tasks and count the instances:

// Function to compare List A with List B and create List C
function compareDates(listA, listB) {
    const listC = [];
    for (const dateA of listA) {
        let count = 0;
        for (const dateB of listB) {
            if (dateA === dateB) {
                count++;
            }
        }
        listC.push(count);
    }
    return listC;
}

I just have trouble putting it all together. I will look into your suggestion and see how far I can get. My bigges hinderance is that I dont really know how to debug these javascript functions step by step. Guess I have some reading to do.

holroy · March 24, 2024, 7:47pm

To reiterate your question, you want to create a line graph based upon data from journals, where each journal is named something like 2024-03-24-Samstag, and the criteria for the graph are:

The labels (and range) is to be the last seven days (aka x dimension)
One set of the y dimension are completed tasks on that day for a given tag
If possible, add multiple set for the y dimension for different tags

So in a super simplified world this could be presented in a table view as

date	Tag1	Tag 2
2024-03-20	0	8
2024-03-21	1	5
2024-03-22	2	0
2024-03-23	3	4

Since the completion date could be different from the daily note date containing that task, we can’t use that as the base for our queries. We can use the dates, but we do need to make the query only report the completed tasks related to our tags in the corresponding date range.

So to build the complete data set we’d need to do the following steps:

Build a dictionary with the correct date range
Build a task query listing completed tasks in our date range, limited to our tag set, grouped on the completion date, and then map that into an entry of the dictionary
After that it should be smooth sailing to present the data in a chart

A debug query to display the counts

The query belows attempts to build a table out of completed task in the given date range listing matching tags, and the count of those tags. Try this and see if you can make it match your scenario after changing stuff in the first few lines:

```dataviewjs
// Define these according to your specifications
const myFolder = "ProjectOne" // Or whatever folder your tasks are in
const myDate = moment("2024-03-07")  // use moment() for today
const tag1name = "#tag1"
const tag2name = "#tag2"

// calculate some extra variables to use in queries 
const endDate = moment("2024-03-07")
const startDate = endDate.clone().add(-6, "days")

dv.paragraph("Date range: " + startDate.format("YYYY-MM-DD") + " — " + endDate.format("YYYY-MM-DD"))

const taskCandidates = dv.pages('"' + myFolder + '"')
  .file.tasks
  .where(t => t.completed)
  .where(t => t.completion >= startDate && t.completion <= endDate)
  .mutate(t => {
    t.tag1 = t.tags.includes(tag1name) ? 1 : 0
    t.tag2 = t.tags.includes(tag2name) ? 1 : 0
  })
  .groupBy(t => t.completion)
  .mutate(r => {
    r.tag1 = r.rows.tag1.array().reduce((a, c) => a+c, 0)
    r.tag2 = r.rows.tag2.array().reduce((a, c) => a+c, 0)
  })
  .map(t => [t.rows.text, t.rows.section,  t.rows.completion, t.rows.tags, t.tag1, t.tag2])
  
dv.table(["text", "file.link", "completion", "tags", "tag1count", "tag2count"], taskCandidates)
```

Example output from my test vault

For my test vault, after loads of tampering with setup files this resulted in this output:

Here we can see the clear distinction between the file.link where the task was kept, and the completion date in the next column, and then which tags the completed tasks had and a count for each tag.

The linechart data

After you’ve fiddled around with the query above till you get something resembling my output, you can do the same kind of modifications to this script:

```dataviewjs
// Define these according to your specifications
const myFolder = "ForumStuff/f78/f78650"
const myDate = moment("2024-03-07")  // use moment() for today
const tag1name = "#tag1"
const tag2name = "#tag2"

// calculate some extra variables to use in queries 
const endDate = moment("2024-03-07")
const startDate = endDate.clone().add(-6, "days")

dv.paragraph("Date range: " + startDate.format("YYYY-MM-DD") + " — " + endDate.format("YYYY-MM-DD"))

const lineChartData = {}
for (let i = startDate.clone(); i.isSameOrBefore(endDate); i.add(1, "day")) {
  lineChartData[i.format("YYYY-MM-DD")] = [0, 0]
}

const taskCandidates = dv.pages('"' + myFolder + '"')
  .file.tasks
  .where(t => t.completed)
  .where(t => t.completion >= startDate && t.completion <= endDate)
  .mutate(t => {
    t.tag1 = t.tags.includes(tag1name) ? 1 : 0
    t.tag2 = t.tags.includes(tag2name) ? 1 : 0
  })
  .groupBy(t => t.completion)
  .mutate(r => {
    r.tag1 = r.rows.tag1.array().reduce((a, c) => a+c, 0)
    r.tag2 = r.rows.tag2.array().reduce((a, c) => a+c, 0)
  })
  .where(r => r.tag1 || r.tag2)
  .forEach(r => lineChartData[r.key.toFormat("yyyy-MM-dd")] = [r.tag1, r.tag2])
  
dv.table(["key", "value"], Object.entries(lineChartData))


```

Example output from my test vault (again)

With the same files as above this displayed this table:

Note especially how the date 2024-03-06 has a double zero entry which it got from the initialization at the top of the query.

Using this data set the taskCandidates hopefully you’re able to present them into a linechart with multiple y-values. I’m going to call it a day now since it’s getting late, this took way too much time, and I’m still not sure if I’ve actually understood your requirements completely.

In any case, these scripts should hopefully show you some of the steps needed both to debug that you’ve got a proper data set to work with, and how to further manipulate that into something useful to chart afterwards.