Dataview - How do I display the total number of tasks completed? And make a table with statistics by months and roles?

[DrCristian]

Problem #1:

---

I have this project page:

I have a table that displays projects and the number of tasks completed in them:

for (let group of dv.pages("#Project").groupBy(p => p.Role)) {
    dv.header(3, group.key);
    dv.table(["Project", "✅Tasks"],
        group.rows
            .sort(k => k.file.tasks.where(t => t.completed).length, 'desc')
            .map(k => [
						k.file.link, 
						k.file.tasks.where(t => t.completed).length,
						k.Status]))
}

I need to display the total number of completed tasks of a role:

---

Problem #2

And then I have this code, which I update every month:

dv.span(' 2022-05: Role1: ');dv.span(dv.pages("#Role1").file.tasks
	.where(t => t.text.includes("completion:: 2022-05")).length);
dv.span(' Role2: ');dv.span(dv.pages("#Role2").file.tasks
	.where(t => t.text.includes("completion:: 2022-05")).length);
dv.paragraph(" ")
dv.span(' 2022-06: Role1: ');dv.span(dv.pages("#Role1").file.tasks
	.where(t => t.text.includes("completion:: 2022-06")).length);
dv.span(' Role2: ');dv.span(dv.pages("#Role2").file.tasks
	.where(t => t.text.includes("completion:: 2022-06")).length);
dv.paragraph(" ")
dv.span(' 2022-07: Role1: ');dv.span(dv.pages("#Role1").file.tasks
	.where(t => t.text.includes("completion:: 2022-07")).length);
dv.span(' Role2: ');dv.span(dv.pages("#Role2").file.tasks
	.where(t => t.text.includes("completion:: 2022-07")).length);

I need to turn it into a table like this:

In the end, I should have a table by role and by month, where each cell shows the number of tasks completed for the month and for that role.

I hope that the gurus here will help to implement it in a more beautiful way.

[scholarInTraining]

Great questions and thank you for the picture and clear explanation!

@DrCristian

I need to display the total number of completed tasks of a role:

It sounds like you want to add one more row to your table for “Role1” that contains a Total, is that correct? You can use the reduce function on Arrays to do the summation, and then I think you can just use push to add it on to the rows you are already displaying. I do not know what you want in the status field for the total row, so I just put an empty string “”.

for (let group of dv.pages("#Project").groupBy(p => p.Role)) {
    dv.header(3, group.key);
    let myRows =  group.rows
            .sort(k => k.file.tasks.where(t => t.completed).length, 'desc')
            .map(k => [
						k.file.link, 
						k.file.tasks.where(t => t.completed).length,
						k.Status]);
    const totalRow = ["Total:", myRows.reduce((prev, row) => prev + row[1]), ""];
    myRows.push(totalRow);
    dv.table(["Project", "✅Tasks"], myRows);       
}

For Problem 2, I don’t have time to write out a full program but as something to get you started, if you define an array with your list of roles to query and another one with your list of months to query, you should be able to use array functions like map or forEach to replace your repeated function calls and eventually call dv.table. Good luck!

[justdoitcc]

Input

Summary

dictionary files

• filename : dic_20220205

---
Date: 2022-02-05
---

Role:: #Role1
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-03-07]
- [ ] To do someting 

---

• filename : dic_20220210

---
Date: 2022-02-10
---

Role:: #Role2 
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-04-03]
- [ ] To do someting 

---

• filename : dic_20220305

---
Date: 2022-03-05
---

Role:: #Role1
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-07-18]
- [x] To do someting [completion:: 2022-06-08]
- [x] To do someting [completion:: 2022-05-18]
- [x] To do someting [completion:: 2022-06-18]
- [x] To do someting [completion:: 2022-06-28]
- [ ] To do someting 

---

• filename : dic_20220310

---
Date: 2022-03-10
---

Role:: #Role2
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-07-04]
- [x] To do someting [completion:: 2022-07-14]
- [x] To do someting [completion:: 2022-06-04]
- [x] To do someting [completion:: 2022-05-04]
- [ ] To do someting 

---

• filename : dic_20220405

---
Date: 2022-04-05
---

Role:: #Role1
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-07-19]
- [x] To do someting [completion:: 2022-06-19]
- [x] To do someting [completion:: 2022-05-19]
- [x] To do someting [completion:: 2022-06-29]
- [x] To do someting [completion:: 2022-06-09]
- [x] To do someting [completion:: 2022-07-19]
- [ ] To do someting 

---

• filename : dic_20220410

---
Date: 2022-04-10
---

Role:: #Role2
Status:: #Project/new

---
# Next Action:
- [x] To do someting [completion:: 2022-07-05]
- [x] To do someting [completion:: 2022-06-05]
- [x] To do someting [completion:: 2022-05-05]
- [x] To do someting [completion:: 2022-07-15]
- [x] To do someting [completion:: 2022-07-25]
- [x] To do someting [completion:: 2022-07-15]
- [x] To do someting [completion:: 2022-05-15]
- [x] To do someting [completion:: 2022-05-25]
- [ ] To do someting 

---

---

DVJS10_Tasks_groupBy_Role_Total_value

Summary

Main DVJS

Code Name	Data type	Purposes	Remark
DVJS10_Tasks_groupBy _Role_Total_value	a date or a list	1.To display projects and the number of tasks completed in them 2.To display the total number of completed tasks of a role	The value of i_Position_of_Total at Step M13.LP07 needs being modified manually for different web page scaling in obsidian preview mode.

Summary_code

Code DVJS10_Tasks_groupBy_Role_Total_value

````ad-info
title: DVJS10_Tasks_groupBy_Role_Total_value =>1.To display projects and the number of tasks completed in them 2.To display the total number of completed tasks of a role
collapse: close
icon:
color:

// M11. Define pages: gather all relevant pages
// #####################################################################
let pages = dv.pages(
    '#Project and "100_Project/01_dataviewjs/01_by_example/Q12_Tasks_Sum/Q12_test_data"'
);

// M13. GROUP BY page.Role:
// #####################################################################
for (let group of pages.groupBy((page) => page.Role)) {
    
    // M13.LP01 Output group.key: ["#Role1", "#Role2"]
    // ####################################################################
    dv.header(3, group.key);

    // M13.LP03 TABLE:
    // ####################################################################
    dv.table(
        ["Project", "✅Tasks"],
        group.rows
            .sort(
                (page) => page.file.tasks.where((task) => task.completed).length,
                "desc"
            )
            .map((page) => [
                page.file.link,
                page.file.tasks.where((task) => task.completed).length,
                page.Status,
            ])
    );

    // M13.LP05 Get iTotal: the number of tasks completed
    // ####################################################################
    let iTotal = group.rows.file.tasks.where((task) => task.completed).length;  

    // M13.LP07 Output iTotal:
    // ####################################################################
    let i_MaxLength_of_ProjectName = dv.func.max(
        group.rows.map((page) => page.file.name.length)
    );
    //dv.span(dv.func.padleft("Total:" + iTotal, 33));//=>33 (Try and Error)
    let i_Position_of_Total = i_MaxLength_of_ProjectName + 21; //=>21 (Try and Error)
    dv.span(dv.func.padleft("Total:" + iTotal, i_Position_of_Total));
    
}

````

---

Screenshots(DVJS10)

---

DVJS20_Tasks_groupBy_Role_Total_table

Summary

Main DVJS

Code Name	Data type	Purposes	Remark
DVJS20_Tasks_groupBy _Role_Total_table	a date or a list	To display a table by role and by month, where each cell shows the number of tasks completed for the month and for that role	It doesn’t need being updated manually any more.

Summary_code

Code DVJS20_Tasks_groupBy_Role_Total_table

````ad-info
title: DVJS20_Tasks_groupBy_Role_Total_table =>To display a table by role and by month, where each cell shows the number of tasks completed for the month and for that role
collapse: close
icon:
color:

// T02. Require :
// #####################################################################
// version : 2022-05-01 v1.00 Justdoitcc
function print(...aArgs) {
    let aText = [...aArgs];
    let sTextJoined = aText
        .join("")
        .replace(/[ ]/g, "&nbsp")
        .replace(/(\r\n|\r|\n)/g, "<br>");
    dv.el("span", sTextJoined);
    return true;
}


// M11. [Debug]:Define pages: gather all relevant pages
// #####################################################################
// let pages = dv.pages(
//     '#Project and "100_Project/01_dataviewjs/01_by_example/Q12_Tasks_Sum/Q12_test_data"'
// );


// M21. [Debug]:Define a_Role1_pages_task_text: get completed task.text 
// #####################################################################
// let a_Role1_pages_task_text = 
//     pages("#Project and #Role1")
//     .file.tasks.where((task) => task.completed).text;



// for \#Role1: get completed task.completion
// M23. Define a_Role1_pages_task_completion: ["2022-03-07","2022-07-18"]
// #####################################################################
let a_Role1_pages_task_completion = dv
    .pages("#Project and #Role1")
    .file.tasks.where((task) => task.completed)
    .completion.map((e) => e.toFormat("yyyy-MM-dd"));


// M23.DB01 Debug Output: a_Role1_pages_task_completion:
// #####################################################################
// print("The following is the content of the `a_Role1_pages_task_completion`.\n");
//print(JSON.stringify(a_Role1_pages_task_completion, null, 2), "\n");

//The following is the content of the a_Role1_pages_task_completion.
// [
//     "2022-03-07",
//     "2022-07-18",
//     "2022-06-08",
//     "2022-05-18",
//     "2022-06-18",
//     "2022-06-28",
//     "2022-07-19",
//     "2022-06-19",
//     "2022-05-19",
//     "2022-06-29",
//     "2022-06-09",
//     "2022-07-19"
// ]


// for \#Role1: get completed task.completion
// M25. Define h_Role1_pages_task_completion_YYYYMM:{RolesMonth: Role1}
// #####################################################################
let h_Role1_pages_task_completion_YYYYMM = {};
for (let completion of a_Role1_pages_task_completion) {
    let s_YYYYMM = completion.replace(/-\d{2}$/, "");//=>"2022-06"
    if (s_YYYYMM in h_Role1_pages_task_completion_YYYYMM) {
        h_Role1_pages_task_completion_YYYYMM[s_YYYYMM]++;
    } else {
        h_Role1_pages_task_completion_YYYYMM[s_YYYYMM] = 1;
    }

}


// M25.DEB01 Debug Output: h_Role1_pages_task_completion_YYYYMM: 
// #####################################################################
// print("The following is the content of the `h_Role1_pages_task_completion_YYYYMM`.\n");
// print(JSON.stringify(h_Role1_pages_task_completion_YYYYMM, null, 2), "\n");

//The following is the content of the h_Role1_pages_task_completion_YYYYMM.
// {
//     "2022-07": 3,
//     "2022-06": 6,
//     "2022-05": 2,
//     "2022-03": 1
// }


// for \#Role2: get completed task.completion
// M33. Define a_Role2_pages_task_completion:  [ "2022-04-03","2022-07-04",]
// #####################################################################
let a_Role2_pages_task_completion = dv
    .pages("#Project and #Role2")
    .file.tasks.where((task) => task.completed)
    .completion.map((e) => e.toFormat("yyyy-MM-dd"));


// M33.DEB01 Debug Output: a_Role2_pages_task_completion:
// #####################################################################
//print("The following is the content of the `a_Role2_pages_task_completion`.\n");
//print(JSON.stringify(a_Role2_pages_task_completion, null, 2), "\n");

//The following is the content of the a_Role2_pages_task_completion.
// [
//     "2022-04-03",
//     "2022-07-04",
//     "2022-07-14",
//     "2022-06-04",
//     "2022-05-04",
//     "2022-07-05",
//     "2022-06-05",
//     "2022-05-05",
//     "2022-07-15",
//     "2022-07-25",
//     "2022-07-15",
//     "2022-05-15",
//     "2022-05-25"
// ]


// for \#Role2: get completed task.completion
// M35. Define h_Role2_pages_task_completion_YYYYMM: {RolesMonth: Role2}
// #####################################################################
let h_Role2_pages_task_completion_YYYYMM = {};
for (let completion of a_Role2_pages_task_completion) {
    let s_YYYYMM = completion.replace(/-\d{2}$/, "");//"2022-06-19"=>"2022-06"
    if (s_YYYYMM in h_Role2_pages_task_completion_YYYYMM) {
        h_Role2_pages_task_completion_YYYYMM[s_YYYYMM]++;
    } else {
        h_Role2_pages_task_completion_YYYYMM[s_YYYYMM] = 1;
    }

}

// M35.DEB01 Debug Output: h_Role2_pages_task_completion_YYYYMM: 
// #####################################################################
// print("The following is the content of the `h_Role2_pages_task_completion_YYYYMM`.\n");
// print(JSON.stringify(h_Role2_pages_task_completion_YYYYMM, null, 2), "\n");

//The following is the content of the h_Role2_pages_task_completion_YYYYMM.
// {
//     "2022-07": 6,
//     "2022-06": 2,
//     "2022-05": 4,
//     "2022-04": 1
// }


// M41. Define aRolesMonth_unique: get unique => sort
// #####################################################################
let aRolesMonth = [
    ...Object.keys(h_Role1_pages_task_completion_YYYYMM),
    ...Object.keys(h_Role2_pages_task_completion_YYYYMM),
];//get each RolesMonth for \#Role1 or \#Role2
let aRolesMonth_unique = [...new Set(aRolesMonth)];//get unique RolesMonth
aRolesMonth_unique.sort();//=>2022-03,2022-04,2022-05,2022-06,2022-07


// M51. Define aoa_RolesMonth_Role1_Role2 : array of arrays
// #####################################################################
// [
//     ["2022-03", 1, 0],
//     ["2022-04", 0, 1],
//     ["2022-05", 2, 4],
//     ["2022-06", 6, 2],
//     ["2022-07", 3, 6],
// ]

let aoa_RolesMonth_Role1_Role2 = [];
for (let sRolesMonth of aRolesMonth_unique) {
    let s_MM = sRolesMonth.replace(/^\d{4}-/, ""); //"2022-06"=>"06"
    let i_Role1 = dv.func.default(
        h_Role1_pages_task_completion_YYYYMM[sRolesMonth],
        0
    ); //=>6
    let i_Role2 = dv.func.default(
        h_Role2_pages_task_completion_YYYYMM[sRolesMonth],
        0
    ); //=>2

    aoa_RolesMonth_Role1_Role2.push([s_MM, i_Role1, i_Role2]);
}

// M51.DEB01 Debug Output: aoa_RolesMonth_Role1_Role2
// #####################################################################
//print("The following is the content of the `aoa_RolesMonth_Role1_Role2`.\n");
//print(JSON.stringify(aoa_RolesMonth_Role1_Role2, null), "\n");

//The following is the content of the aoa_RolesMonth_Role1_Role2.
//["03",1,null],["04",null,1],["05",2,4],["06",6,2],["07",3,6] //=>without using dv.func.default()
//["03",1,0],["04",0,1],["05",2,4],["06",6,2],["07",3,6] //=>with using dv.func.default()


// M61. print a table of aoa_RolesMonth_Role1_Role2
// #####################################################################
dv.header(5, "M61. Print a table of the `aoa_RolesMonth_Role1_Role2`");
// dv.table(
//     ["Roles/Month", "Role1", "Role2"],
//     [
//         ["2022-03", 1, 0],
//         ["2022-04", 0, 1],
//         ["2022-05", 2, 4],
//         ["2022-06", 6, 2],
//         ["2022-07", 3, 6],
//     ]
// );
dv.table(["Roles/Month", "Role1", "Role2"], aoa_RolesMonth_Role1_Role2);

````

---

Screenshots(DVJS20)

---

DVJS30_Tasks_groupBy_Role_Total_table_fullname_of_month

Summary

Main DVJS

Code Name	Data type	Purposes	Remark
DVJS30_Tasks_groupBy_Role _Total_table_fullname_of_month	a date or a list	1.To display a table by role and by month, where each cell shows the number of tasks completed for the month and for that role 2.To use the fullname_of_month	1.It doesn’t need being updated manually any more. 2.The comments which are out of use are removed.

Summary_code

Code DVJS30_Tasks_groupBy_Role_Total_table_fullname_of_month

````ad-info
title: DVJS30_Tasks_groupBy_Role_Total_table_fullname_of_month =>1.To display a table by role and by month, where each cell shows the number of tasks completed for the month and for that role 2.To use the fullname_of_month
collapse: close
icon:
color:

// for \#Role1: get completed task.completion
// M23. Define a_Role1_pages_task_completion: ["2022-03-07","2022-07-18"]
// #####################################################################
let a_Role1_pages_task_completion = dv
    .pages("#Project and #Role1")
    .file.tasks.where((task) => task.completed)
    .completion.map((e) => e.toFormat("yyyy-MM-dd"));


// for \#Role1: get completed task.completion
// M25. Define h_Role1_pages_task_completion_YYYYMM:{RolesMonth: Role1}
// #####################################################################
let h_Role1_pages_task_completion_YYYYMM = {};
for (let completion of a_Role1_pages_task_completion) {
    let s_YYYYMM = completion.replace(/-\d{2}$/, "");//=>"2022-06"
    if (s_YYYYMM in h_Role1_pages_task_completion_YYYYMM) {
        h_Role1_pages_task_completion_YYYYMM[s_YYYYMM]++;
    } else {
        h_Role1_pages_task_completion_YYYYMM[s_YYYYMM] = 1;
    }

}


// for \#Role2: get completed task.completion
// M33. Define a_Role2_pages_task_completion:  [ "2022-04-03","2022-07-04",]
// #####################################################################
let a_Role2_pages_task_completion = dv
    .pages("#Project and #Role2")
    .file.tasks.where((task) => task.completed)
    .completion.map((e) => e.toFormat("yyyy-MM-dd"));


// for \#Role2: get completed task.completion
// M35. Define h_Role2_pages_task_completion_YYYYMM: {RolesMonth: Role2}
// #####################################################################
let h_Role2_pages_task_completion_YYYYMM = {};
for (let completion of a_Role2_pages_task_completion) {
    let s_YYYYMM = completion.replace(/-\d{2}$/, "");//"2022-06-19"=>"2022-06"
    if (s_YYYYMM in h_Role2_pages_task_completion_YYYYMM) {
        h_Role2_pages_task_completion_YYYYMM[s_YYYYMM]++;
    } else {
        h_Role2_pages_task_completion_YYYYMM[s_YYYYMM] = 1;
    }

}


// M41. Define aRolesMonth_unique: get unique => sort
// #####################################################################
let aRolesMonth = [
    ...Object.keys(h_Role1_pages_task_completion_YYYYMM),
    ...Object.keys(h_Role2_pages_task_completion_YYYYMM),
];//get each RolesMonth for \#Role1 or \#Role2
let aRolesMonth_unique = [...new Set(aRolesMonth)];//get unique RolesMonth
aRolesMonth_unique.sort();//=>2022-03,2022-04,2022-05,2022-06,2022-07


// M51. Define aoa_RolesMonth_Role1_Role2 : array of arrays
// #####################################################################
// [
//     ["2022-03", 1, 0],
//     ["2022-04", 0, 1],
//     ["2022-05", 2, 4],
//     ["2022-06", 6, 2],
//     ["2022-07", 3, 6],
// ]

let aoa_RolesMonth_Role1_Role2 = [];
for (let sRolesMonth of aRolesMonth_unique) {
    let s_MM = sRolesMonth.replace(/^\d{4}-/, ""); //"2022-06"=>"06"
    let s_fullname_of_month = {
        "01": "January",
        "02": "February",
        "03": "March",
        "04": "April",
        "05": "May",
        "06": "June",
        "07": "July",
        "08": "August",
        "09": "September",
        "10": "October",
        "11": "November",
        "12": "December",
    }[s_MM];
    
    let i_Role1 = dv.func.default(
        h_Role1_pages_task_completion_YYYYMM[sRolesMonth],
        0
    ); //=>6
    let i_Role2 = dv.func.default(
        h_Role2_pages_task_completion_YYYYMM[sRolesMonth],
        0
    ); //=>2

    aoa_RolesMonth_Role1_Role2.push([s_fullname_of_month, i_Role1, i_Role2]);
}

// M61. print a table of aoa_RolesMonth_Role1_Role2
// #####################################################################
dv.header(5, "M61. Print a table of the `aoa_RolesMonth_Role1_Role2`");
// dv.table(
//     ["Roles/Month", "Role1", "Role2"],
//     [
//         ["2022-03", 1, 0],
//         ["2022-04", 0, 1],
//         ["2022-05", 2, 4],
//         ["2022-06", 6, 2],
//         ["2022-07", 3, 6],
//     ]
// );
dv.table(["Roles/Month", "Role1", "Role2"], aoa_RolesMonth_Role1_Role2);

````

---

Screenshots(DVJS30)

---