Summary_code
title: DVJS10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE => 0.To define a variable like `h_medium_category_of` 1.To add four new dynamical fields including `P_Category`, `Q_PagesOrHours`, `R_Unit` and `S_NumberOfDigits` into each `page` by using the `FLATTEN` operator 2.To group by `P_Category` 3.To add two new dynamical fields including `G_Medium_Counts` and `G_Medium_Lengths` into each `group` 4.To define aoa_rows 5.To do some simple statistics: 5.1 To count `file.link` by each group 5.2 To sum up `Pages`(or `Hours`) by each group 5.3 To get the average `Pages`(or `Hours`) by each group 5.4 To get the average `Pages`(or `Hours`) read(or listened) per day by each group 5.5 To count the overall `file.link` 5.6 To get the overall average `Rating` 6.To display the result as a table
collapse: open
icon:
color:
```dataviewjs
// M01. define page.P_Category: Medium Category
// page.P_Category="Reading" or "Listening"
// Each page.Medium is dynamically categorized by page.P_Category.
// #####################################################################
// let page.P_Category = page.P_Category;
// M09. define h_medium_category_of:
// {"Book": "Reading", "Audiobook": "Listening"}
// #####################################################################
let h_medium_category_of = {
Book: "Reading",
GraphicNovel: "Reading",
eBook: "Reading",
Article: "Reading",
Audiobook: "Listening",
Podcast: "Listening",
radio: "Listening",
};
// M11. define pages: gather all relevant pages
// WHERE_CASE_Reading or WHERE_CASE_Listening:
// where page.P_Category === "Reading" || page.P_Category === "Listening"
// For each Medium
// #####################################################################
let pages = dv
.pages(
'"100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"'
)
.where((page) => page.Status !== null)
.where((page) => page.Medium !== null)
.where(
(page) =>
// No modification required
h_medium_category_of[page.Medium]
);
// M13. define s_today:
// #####################################################################
// let s_today = "today";
let s_today = "2022-12-30";
// M15. define h_medium_PQRS_of:
// four new fields to be added into each page
// a new field P_Category : Medium Category
// a new field Q_PagesOrHours : Medium Length
// a new field R_Unit : Medium Unit
// a new field S_NumberOfDigits: Number of Digits
// #####################################################################
let h_medium_PQRS_of = {
Reading: {
P_Category: "Reading",
Q_PagesOrHours: "", // page.Pages
R_Unit: " pages",
S_NumberOfDigits: 0,
},
Listening: {
P_Category: "Listening",
Q_PagesOrHours: "", // page.Hours
R_Unit: " hours",
S_NumberOfDigits: 1,
},
};
// M17. update pages: add four new fields into each page
// It is like using the `FLATTEN` operator in the DQL10 or DQL20.
// #####################################################################
pages.forEach((page) => {
// M17.FR01 define P_Category_of_page: "Reading" or "Listening"
// #################################################################
// let P_Category_of_page = "Reading";
let P_Category_of_page = h_medium_category_of[page.Medium];
// M17.FR10 To add a new field P_Category: Alias="Medium Category"
// It is the same as the T01 in the DQL10.
// #################################################################
// page.P_Category = "Reading";
// page.P_Category = h_medium_PQRS_of[P_Category_of_page];
page.P_Category = P_Category_of_page;
// M17.FR20 To add a new field Q_PagesOrHours: Alias="Medium Length"
// It is the same as the T03 in the DQL10.
// #################################################################
// page.Q_PagesOrHours = page.Pages;
// Dataview v0.4.6 hasn't supported it.
// page.Q_PagesOrHours = dv.evaluate(
// h_medium_PQRS_of[P_Category_of_page]["Q_PagesOrHours"]
// )["value"];
if (P_Category_of_page === "Reading") {
// To add a new field Q_PagesOrHours: Medium Length
page.Q_PagesOrHours = page.Pages;
} else if (P_Category_of_page === "Listening") {
// To add a new field Q_PagesOrHours: Medium Length
page.Q_PagesOrHours = page.Hours;
} else {
// To do nothing
}
// M17.FR30 To add a new field R_Unit: Alias="Medium Unit"
// It is the same as the T05 in the DQL10.
// #################################################################
// page.R_Unit = " pages";
page.R_Unit = h_medium_PQRS_of[P_Category_of_page]["R_Unit"];
// M17.FR40 To add a new field S_NumberOfDigits: Alias="Number of Digits"
// It is the same as the T07 in the DQL10.
// #################################################################
// page.S_NumberOfDigits = 0;
page.S_NumberOfDigits =
h_medium_PQRS_of[P_Category_of_page]["S_NumberOfDigits"];
// M17.FR90 Debug output:
// ##################################################################
// dv.span("<br>Here is the output for debugging:");
// dv.span("(" + page.file.name + ")<br>");
// dv.span("P_Category=" + page.P_Category + "<br>");
// dv.span("Q_PagesOrHours=" + page.Q_PagesOrHours + "<br>");
// dv.span("R_Unit=" + page.R_Unit + "<br>");
// dv.span("S_NumberOfDigits=" + page.S_NumberOfDigits + "<br>");
// Here is the output for debugging:(dic_19660311)
// P_Category=Reading
// Q_PagesOrHours=681
// R_Unit= pages
// S_NumberOfDigits=0
// Here is the output for debugging:(dic_19660511)
// P_Category=Listening
// Q_PagesOrHours=11
// R_Unit= hours
// S_NumberOfDigits=1
// Here is the output for debugging:(dic_19660611)
// P_Category=Reading
// Q_PagesOrHours=888
// R_Unit= pages
// S_NumberOfDigits=0
// Here is the output for debugging:(dic_19660411)
// P_Category=Listening
// Q_PagesOrHours=1200
// R_Unit= hours
// S_NumberOfDigits=1
});
// M23. define today_ooo:
// To require the M13
// toFormat("ooo"): ordinal (day of year), padded to 3
// #####################################################################
// let today_ooo = dv.date("2022-12-30").toFormat("ooo"); //=>364
// let today_ooo = DateTime.now().toFormat("ooo");
let today_ooo = dv.date(s_today).toFormat("ooo");
// M31. define groups:
// To group by h_medium_category_of[page.Medium] AS G1 (G1=group.rows)
// #####################################################################
let groups = pages
.groupBy((page) => h_medium_category_of[page.Medium])
.sort((group) => group.key, "desc");
// M47. update groups: add two new fields into each group
// It is like using the `FLATTEN` operator in the DQL10.
// To add a new field G_Medium_Counts : "Medium Counts" by Each group
// To add a new field G_Medium_Lengths: "Medium Lengths" by Eachr goup
// #####################################################################
groups.forEach((group) => {
// let P_Category_of_group = "Reading";
// let P_Category_of_group = h_medium_category_of[group.key];
// M47.FR10 update groups: add a new field into each group
// To add a new field G_Medium_Counts : "Medium Counts" by Each group
// #####################################################################
// It is the same as the T21 in the DQL10.
// FLATTEN length(rows.file.link) AS G_Medium_Counts
group.G_Medium_Counts = group.rows.length;
// M47.FR20 update groups: add a new field into each group
// To add a new field G_Medium_Lengths: "Medium Lengths" by Eachr goup
// #####################################################################
// It is the same as the T23 in the DQL10.
// FLATTEN sum(rows.Q_PagesOrHours) AS G_Medium_Lengths
group.G_Medium_Lengths = dv.func.default(
dv.func.sum(group.rows.Q_PagesOrHours),
0
);
// M47.FR90 Debug output:
// #####################################################################
// dv.span("<br>Here is the output for debugging:" + "<br>");
// dv.span("group.key=" + group.key + "<br>");
// dv.span("group.G_Medium_Counts=" + group.G_Medium_Counts + "<br>");
// dv.span("group.G_Medium_Lengths=" + group.G_Medium_Lengths + "<br>");
// Here is the output for debugging:
// group.key=Reading
// group.G_Medium_Counts=2
// group.G_Medium_Lengths=1569
// Here is the output for debugging:
// group.key=Listening
// group.G_Medium_Counts=2
// group.G_Medium_Lengths=1211
});
// M51. define aoa_rows: aoa = an Array of Arrays
// Warning: 6 columns
// [
// "Categories",
// "Medium Counts",
// "Medium Lengths",
// "Average Lengths",
// "Average Lengths read/listened per day",
// "Medium Ratings"
// ]
// #####################################################################
let aoa_rows = groups.map((group) => [
// M51.TB10 To get the P_Category: "Reading" or Listening
// Headers: "Categories"
// #####################################################################
// "Reading" : "Reading"
// "Listening": "Listening"
// CASE_A: Without adding four new dynamical fields into each page
// CASE_B: With adding four new dynamical fields into each page
group.key,
// M51.TB20 To get the overall Medium Counts by each group:
// Headers: "Medium Counts"
// #####################################################################
// "Reading" : BookCount
// "Listening": AudioCount
// group.rows.length,
// CASE_C: With adding two new dynamical fields into each group
group.G_Medium_Counts,
// M51.TB30 To get the Medium Lengths by each group:
// Headers: "Medium Lengths"
// #####################################################################
// "Reading" : pagesum
// "Listening": hoursum
// CASE_A: Without adding four new dynamical fields into each page
// group.key === "Reading"
// ? dv.func.default(dv.func.sum(group.rows.Pages), 0)
// : dv.func.default(dv.func.sum(group.rows.Hours), 0),
// CASE_B: With adding four new dynamical fields into each page
// dv.func.default(dv.func.sum(group.rows.Q_PagesOrHours), 0),
// CASE_C: With adding two new dynamical fields into each group
group.G_Medium_Lengths,
// M51.TB40 To get the Average Lengths by each group:
// Headers: "Average Lengths"
// #####################################################################
// "Reading" : (pagesum / BookCount).toFixed() + " pages"
// "Listening": (hoursum / AudioCount).toFixed(1) + " hours"
// CASE_A: Without adding four new dynamical fields into each page
// group.key === "Reading"
// ? (
// dv.func.default(dv.func.sum(group.rows.Pages), 0) /
// group.rows.length
// ).toFixed(0) + " pages"
// : (
// dv.func.default(dv.func.sum(group.rows.Hours), 0) /
// group.rows.length
// ).toFixed(1) + " hours",
// CASE_B: With adding four new dynamical fields into each page
// (
// dv.func.default(dv.func.sum(group.rows.Q_PagesOrHours), 0) / group.rows.length
// ).toFixed(group.rows.S_NumberOfDigits[0]) + group.rows.R_Unit[0],
// CASE_C: With adding two new dynamical fields into each group
(group.G_Medium_Lengths / group.G_Medium_Counts).toFixed(
group.rows.S_NumberOfDigits[0]
) + group.rows.R_Unit[0],
// M51.TB50
// To get the average Pages(or Hours) read(or listened) per day by each group
// Headers: "Average Lengths read/listened per day"
// #####################################################################
// "Reading" : (pagesum / today).toFixed(1) + " pages"
// "Listening": (hoursum / today).toFixed(1) + " hours"
// CASE_A: Without adding four new dynamical fields into each page
// group.key === "Reading"
// ? (
// dv.func.default(dv.func.sum(group.rows.Pages), 0) / today_ooo
// ).toFixed(1) + " pages"
// : (
// dv.func.default(dv.func.sum(group.rows.Hours), 0) / today_ooo
// ).toFixed(1) + " hours",
// CASE_B: With adding four new dynamical fields into each page
// (dv.func.default(dv.func.sum(group.rows.Q_PagesOrHours), 0) / today_ooo).toFixed(1) +
// group.rows.R_Unit[0],
// CASE_C: With adding two new dynamical fields into each group
(group.G_Medium_Lengths / today_ooo).toFixed(1) + group.rows.R_Unit[0],
// M51.TB60 To get the overall average Rating
// Headers: "Medium Ratings"
// #####################################################################
dv.func.default(dv.func.sum(group.rows.Rating), 0)
]);
// M60.Headers of aoa_rows:
// Warning: 6 columns
// #####################################################################
// [
// "Categories",
// "Medium Counts",
// "Medium Lengths",
// "Average Lengths",
// "Average Lengths read/listened per day",
// "Medium Ratings"
// ]
// M61.update aoa_rows: push the_last_row_Total
// Warning: 6 columns
// #####################################################################
// The method can be used ONLY once.
// aoa_rows["values"][aoa_rows.length] = [
// "**Total**",
// groups.rows.length,
// "",
// "",
// "",
// "",
// ];
aoa_rows["values"].push([
"**Total**",
groups.rows.length,
"",
"",
"",
"",
]);
// M63.update aoa_rows: push the_last_row_Average
// Warning: 6 columns
// #####################################################################
aoa_rows["values"].push([
"**Average**",
"",
"",
"",
"",
(
dv.func.default(dv.func.sum(groups.rows.Rating), 0) / groups.rows.length
).toFixed(1) + " (out of 10)",
]);
// M71. Output aoh_rows as a table:
// #####################################################################
dv.header(3, "M71.Reading Report on " + s_today);
dv.table(
[
"Categories",
"Medium Counts",
"Medium Lengths",
"Average Lengths",
"Average Lengths read/listened per day",
"Medium Ratings",
],
aoa_rows
);
// M81. hide the file count in the `the_nth_of_table` dataview table :
// #####################################################################
let the_nth_of_table = 1;
this.container.querySelectorAll(
".table-view-table tr:first-of-type th:first-of-type > span.small-text"
)[the_nth_of_table - 1].style.visibility = "hidden";
```
