# 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)) {
group.rows
.sort(k => k.file.tasks.where(t => t.completed).length, 'desc')
.map(k => [
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);
.where(t => t.text.includes("completion:: 2022-05")).length);
dv.paragraph(" ")
.where(t => t.text.includes("completion:: 2022-06")).length);
.where(t => t.text.includes("completion:: 2022-06")).length);
dv.paragraph(" ")
.where(t => t.text.includes("completion:: 2022-07")).length);
.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.

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

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)) {
let myRows =  group.rows
.sort(k => k.file.tasks.where(t => t.completed).length, 'desc')
.map(k => [
k.Status]);
const totalRow = ["Total:", myRows.reduce((prev, row) => prev + row[1]), ""];
myRows.push(totalRow);
}
``````

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!

## 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
``````

Summary

### Main DVJS

Code Name Data type Purposes Remark
_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

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(
);

// M13. GROUP BY page.Role:
// #####################################################################
for (let group of pages.groupBy((page) => page.Role)) {

// M13.LP01 Output group.key: ["#Role1", "#Role2"]
// ####################################################################

// M13.LP03 TABLE:
// ####################################################################
dv.table(
group.rows
.sort(
"desc"
)
.map((page) => [
page.Status,
])
);

// M13.LP05 Get iTotal: the number of tasks completed
// ####################################################################

// 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)

}

``````

````

Summary

### Main DVJS

Code Name Data type Purposes Remark
_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

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(
// );

// #####################################################################
//     pages("#Project and #Role1")

// for \#Role1: get completed task.completion
// #####################################################################
.pages("#Project and #Role1")
.completion.map((e) => e.toFormat("yyyy-MM-dd"));

// #####################################################################
// print("The following is the content of the `a_Role1_pages_task_completion`.\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
// #####################################################################
for (let completion of a_Role1_pages_task_completion) {
let s_YYYYMM = completion.replace(/-\d{2}\$/, "");//=>"2022-06"
} else {
}

}

// #####################################################################
// print("The following is the content of the `h_Role1_pages_task_completion_YYYYMM`.\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",]
// #####################################################################
.pages("#Project and #Role2")
.completion.map((e) => e.toFormat("yyyy-MM-dd"));

// #####################################################################
//print("The following is the content of the `a_Role2_pages_task_completion`.\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}
// #####################################################################
for (let completion of a_Role2_pages_task_completion) {
let s_YYYYMM = completion.replace(/-\d{2}\$/, "");//"2022-06-19"=>"2022-06"
} else {
}

}

// #####################################################################
// print("The following is the content of the `h_Role2_pages_task_completion_YYYYMM`.\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 = [
];//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(
0
); //=>6
let i_Role2 = dv.func.default(
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);

``````

````

Summary

### Main DVJS

Code Name Data type Purposes Remark
_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

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
// #####################################################################
.pages("#Project and #Role1")
.completion.map((e) => e.toFormat("yyyy-MM-dd"));

// for \#Role1: get completed task.completion
// #####################################################################
for (let completion of a_Role1_pages_task_completion) {
let s_YYYYMM = completion.replace(/-\d{2}\$/, "");//=>"2022-06"
} else {
}

}

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

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

}

// M41. Define aRolesMonth_unique: get unique => sort
// #####################################################################
let aRolesMonth = [
];//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(
0
); //=>6
let i_Role2 = dv.func.default(
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)

2 Likes

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