DataviewJS: Sum of Frontmatter Variables, Book Database

I broke my book database. I’ll paste below a DataviewJS Query that someone on reddit helped me with a while ago. My frontmatter of my books look like this

---
Author: Arthur Conan Doyle
Alias: His Last Bow (Sherlock Holmes 8)
Year: 1917
Medium: Audiobook [or Book]   <----
Hours [or Pages]: 6.9 <-----
Rating: 7
DateStarted: 2022-04-30
DateFinished: 2022-05-03
Cover: https://i.gr-assets.com/images/S/compressed.photo.goodreads.com/books/1368588782l/754713.jpg
Tags: Series/SherlockHolmes, Mystery/Detective
Country: GB
---

where the two arrows are are the two variables that I would usually change depending on the medium, e.g. Book or Audiobook, and below that Pages or Hours, depending on if it’s an audiobook or a book with pages.

Trying to streamline my templates, I changed (global search & replace in Atom) all instances of both Hours: and Pages: to Length: so I would really only have to change the Medium, not always remember the Hours/Pages thing, as that occasionally messed up my metrics.

However, I believe this has caused two errors:
I. the DataviewJS query currently does not check for the Medium when calculating the pagesum and hoursum, this is done only via the respective variable of Pages or Hours. So I need some sort of .where check for that, but I can’t figure out how to implement it there.
II. I’m not sure, but maybe the fact that length is a thing in DataviewJS is causing an error because I named my variable Length (capital L). Could that be?

Ideally I’d like to find a solution that doesn’t make me go through all the notes manually and change everything back.

Any advice appreciated!

DataviewJS Query

let BookCount = (dv.pages('"Book Log/2022"').where(p => p.Medium == "Book" || p.Medium == "eBook").length);

let AudioCount = (dv.pages('"Book Log/2022"').where(p => p.Medium == "Audiobook" || p.Medium == "Podcast").length);

let pagesum = 0;                                     <--------------------
   for(let i = 0; i < dv.pages('"Book Log/2022"').length; i++) {
     if(dv.pages('"Book Log/2022"')[i].Pages) {
       pagesum += dv.pages('"Book Log/2022"')[i].Pages;
         }
   }

let hoursum = 0;                                       <------------------------
	for(let i = 0; i < dv.pages('"Book Log/2022"').length;i++) {  
     if(dv.pages('"Book Log/2022"')[i].Hours) {
       hoursum += dv.pages('"Book Log/2022"')[i].Hours;
         }
   }


let today = DateTime.now().toFormat("ooo");

dv.paragraph("# This year, I've read " + BookCount + " books and I've listened to " + AudioCount + " audiobooks (of which " + dv.pages('"Book Log/2022"').where(p => p.Medium == "Podcast").length + " were podcasts), for an overall " + (BookCount + AudioCount) + " books and audiobooks read.")

dv.paragraph("# I've read " + pagesum + " pages this year. And I've listened to " + (hoursum.toFixed(1)) + " hours.")

Where’s Length in the code?

As always, overly hasty with the questions…

I solved it, relevant part:

let pagesum = 0;
   for(let i = 0; i < dv.pages('"Book Log/2022"').where(p => p.Medium == "Book" || p.Medium == "eBook").length; i++) {
     if(dv.pages('"Book Log/2022"').where(p => p.Medium == "Book" || p.Medium == "eBook")[i].Length) {
       pagesum += dv.pages('"Book Log/2022"').where(p => p.Medium == "Book" || p.Medium == "eBook")[i].Length;
         }
   }

I had added the .where part at the wrong spot.

Out of curiosity, in case anyone stops by, Is it possible to create a category of these variables? So I could declare the Mediums Book, GraphicNovel, eBook, Article as being in Category 1, things I’ve read with my eyes, for example; and Category 2, Audiobook, Podcast, radio, etc.

Some way to implement that in my query? I ask, because if I add a type of medium, e.g. an academic book and would like to create a type for that, I need to add that in all those queries.

In the code there it wasn’t anywhere. The code was from the version where I was still using Pages/Hours. I solved that problem (see my other comment)

1 Like

Topic : 1/4

Summary
  • How to simplify nested choice operators via JavaScript Objects? (DQL10_Q1, DQL10_Q3, DQL20_Q1, DQL10, DQL20)
  • How to simplify multiple OR operators via JavaScript Objects? (DVJS01_Q1, DVJS01_Q2,DVJS10_Q1, DVJS10_Q2, DVJS10_Q3, DVJS01, DVJS10)
  • How to simplify the following output?(DQL10, DQL20, DVJS01, DVJS10)
```md
This year, I've read 2 books and I've listened to 2 audiobooks (of which 1 were podcasts), for an overall 4 books and audiobooks read.

I've read 0 pages this year. And I've listened to 0.0 hours.

The average rating for the overall 4 books and audiobooks is 4.5 out of 10.

Average book length: 0 pages

Average audiobook length: 0.0 hours

Average pages read per day: 0.0 pages
```

Test

Summary
  • dataview: v0.5.46

Input

Summary

dictionary files:

  • Location: “100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data”

folder: 03

  • filename : dic_19660311
---
Date: 1966-03-11
tags: [Favorite]
Alias: "Javascript: The Definitive Guide, 7/e"
Medium: "Book"
Author: "David Flanagan"
 
Country: "US, GB"
DateStarted: "2020-06-23"
DateFinished: "2020-07-25"
Status: "done"

Year: 2020
Rating: 7
Pages: 681
Hours: 
Cover: https://m.media-amazon.com/images/W/WEBP\_402378-T2/images/I/919ZMTHDPBL.\_AC\_SX75\_CR,0,0,75,75\_.jpg
---



# Javascript: The Definitive Guide, 7/e
- What :: [Javascript: The Definitive Guide, 7/e](https://www.amazon.com/-/es/David-Flanagan/dp/1491952024/ref=sr_1_1?__mk_es_US=%C3%85M%C3%85%C5%BD%C3%95%C3%91&crid=32QHTGRP393RS&keywords=Javascript&qid=1672211738&s=books&sprefix=javascript%2Cstripbooks-intl-ship%2C286&sr=1-1)
- Authors :: 
- Keywords :: 
- Summaries :: 
- Comments :: 
- Downloaded :: 




folder: 04

  • filename : dic_19660411
---
Date: 1966-04-11
tags: [Favorite]
Alias: "UNIX Shells by Example, 4/e"
Medium: "Audiobook"
Author: "Ellie Quigley"

Country: "IT"
DateStarted: "2004-09-01"
DateFinished: "2005-12-25"
Status: "done"

Year: 2004
Rating: 9
Pages: 
Hours: 1200
Cover: https://m.media-amazon.com/images/W/WEBP\_402378-T2/images/I/91vUmLEjbHL.\_AC\_SX75\_CR,0,0,75,75\_.jpg
---


# UNIX Shells by Example, 4/e
- What :: [UNIX Shells by Example, 4/e](https://www.amazon.com/-/es/Ellie-Quigley/dp/013147572X/ref=sr_1_1?__mk_es_US=%C3%85M%C3%85%C5%BD%C3%95%C3%91&crid=IHF5F5CXXTV0&keywords=UNIX+Shells+by+Example&qid=1672214391&s=books&sprefix=unix+shells+by+example%2Cstripbooks-intl-ship%2C250&sr=1-1)
- Authors :: 
- Keywords :: 
- Summaries :: 
- Comments :: 
- Downloaded :: 




folder: 05

  • filename : dic_19660511
---
Date: 1966-05-11
tags: [Favorite]
Alias: "P_Podcast"
Medium: "Podcast"
Author: "YYY"

Country: "DE"
DateStarted: "2022-12-16"
DateFinished: "2022-12-17"
Status: "done"

Year: 2000
Rating: 3
Pages: 
Hours: 11
Cover: https://media.wnyc.org/i/200/200/c/70/2022/12/NullandVoid_1600x1200.png
---



# Null and Void
- What :: [Null and Void](https://radiolab.org/episodes/null-and-void-2212)
- Authors :: 
- Keywords :: 
- Summaries :: 
- Comments :: 
- Downloaded :: 




folder: 06

  • filename : dic_19660611
---
Date: 1966-06-11
tags: [Favorite]
Alias: "Perl by Example, 5/e"
Medium: "eBook"
Author: "Ellie Quigley"

Country: "FR"
DateStarted: "2014-12-17"
DateFinished: "2015-12-21"
Status: "done"

Year: 2014
Rating: 10
Pages: 888
Hours: 
Cover: https://m.media-amazon.com/images/W/WEBP\_402378-T2/images/I/41aGGHu7Q-L.\_AC\_SX75\_CR,0,0,75,75\_.jpg
---



# Perl by Example, 5/e
- What :: [Perl by Example, 5/e](https://www.amazon.com/-/es/Ellie-Quigley/dp/0133760812/ref=sr_1_1?__mk_es_US=%C3%85M%C3%85%C5%BD%C3%95%C3%91&crid=207AKU6YA3671&keywords=perl+by+example&qid=1672212277&s=books&sprefix=perl+by+exampl%2Cstripbooks-intl-ship%2C432&sr=1-1)
- Authors :: 
- Keywords :: 
- Summaries :: 
- Comments :: 
- Downloaded :: 




folder: 08_excluded

  • filename : dic_19750801
---
Date: 1966-06-11
tags: [Favorite]
Alias: "Perl by Example, 5/e"
Medium: "eBook"
Author: "Ellie Quigley"

Country: "FR"
DateStarted: "2014-12-17"
DateFinished: "2015-12-21"
Status: "done"

Year: 2014
Rating: 10
Pages: 888
Hours: 
Cover: https://m.media-amazon.com/images/W/WEBP\_402378-T2/images/I/41aGGHu7Q-L.\_AC\_SX75\_CR,0,0,75,75\_.jpg
---



# Perl by Example, 5/e
- What :: [Perl by Example, 5/e](https://www.amazon.com/-/es/Ellie-Quigley/dp/0133760812/ref=sr_1_1?__mk_es_US=%C3%85M%C3%85%C5%BD%C3%95%C3%91&crid=207AKU6YA3671&keywords=perl+by+example&qid=1672212277&s=books&sprefix=perl+by+exampl%2Cstripbooks-intl-ship%2C432&sr=1-1)
- Authors :: 
- Keywords :: 
- Summaries :: 
- Comments :: 
- Downloaded :: 




DQL10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_sum_Pages_or_Hours
_groupBy_P_Category
via_JavaScript_Objects
and_TABLE
(A dynamical page field)
P_Category:
1.“Reading”
2.“Listening”

(A page field)
Medium:
1.“Book”
2.“GraphicNovel”
3.“eBook”
4.“Article”

5.“Audiobook”
6.“Podcast”
7.“radio”
yes
(once)
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 add two new dynamical fields including G_Medium_Average_Lengths and today_ooo into each group

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

6.To display the result as a table

Notes

Summary

Q1: What does the following code mean(WHERE P_Category)?

Summary_Q1
Original Example: Q1 (To be modified)

Notes:
1.The P_Category field doesn’t exist in a note.
2.It is created by using the FLATTEN operator.
3.In other words, each Medium is dynamically categorized by P_Category.

Purposes:
1.To simplify multiple ‘OR’ operators via JavaScript Objects
2.For best performance
3.For easier reading
4.For easier modification
5.For easier debugging
Tips: The value of the P_Category is “Reading”, “Listening” or undefined. Therefore, the value except undefined is truthy.

```dataview

WHERE Status != null
WHERE Medium != null


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"
    
}[Medium] AS P_Category


// The multiple 'OR' operators are simplified via JavaScript Objects.
WHERE P_Category



```

A1:

Another Example: A1_11

Purposes: To use multiple ‘OR’ operators

```dataview

WHERE Status != null
WHERE Medium != null



WHERE                           
      Medium = "Book"          // using multiple 'OR' operators
   OR Medium = "GraphicNovel"  // using multiple 'OR' operators
   OR Medium = "eBook"         // using multiple 'OR' operators
   OR Medium = "Article"       // using multiple 'OR' operators
   
   OR Medium = "Audiobook"     // using multiple 'OR' operators
   OR Medium = "Podcast"       // using multiple 'OR' operators
   OR Medium = "radio"         // using multiple 'OR' operators


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


```

Q2: How to modify the DQL10 when “typeP”, “typeQ” or “typeR” (page.P_Category: “Reading”, “Listening”, “Listening”)(units: “p”, “h”, “h”) is added to a Medium field content?

Summary_Q2
Original Example: Q2 (To be modified)
```dataview

WHERE Status != null
WHERE Medium != null


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


WHERE P_Category 


```

A2:

Another Example: A2_21
```dataview

WHERE Status != null
WHERE Medium != null


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    "typeP":  "Reading",       // Added by Justdoitcc 2022-12-30
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening",
    "typeQ":  "Listening",     // Added by Justdoitcc 2022-12-30
    "typeR":  "Listening"      // Added by Justdoitcc 2022-12-30
}[Medium] AS P_Category


WHERE P_Category

```

Q3: What does the following code mean(FLATTEN ... AS S_NumberOfDigits)?

Summary_Q3
Original Example: Code DQL10_Q3 (To be explained)

The original DQL with a new field like S_NumberOfDigits doesn’t contain a choice expression.
Purposes:
1.To simplify multiple choice functions via JavaScript Objects
2.For best performance
3.For easier reading
4.For easier modification
5.For easier debugging

```dataview
TABLE WITHOUT ID
      
      // Not to use a `choice` expression
      round(G_Medium_Average_Lengths, rows.S_NumberOfDigits[0]) + rows.R_Unit[0]
      AS "Average Lengths"



FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC


// T07. define page.S_NumberOfDigits: Alias="Number of Digits"
// P_Category="Reading"    => 0
// P_Category="Listening"  => 1
// To be used in the step:To get the Average Lengths by each group
// #####################################################################
// let page.S_NumberOfDigits = 0;
FLATTEN {
    "Reading": 0,
    "Listening": 1
}[S_NumberOfDigits] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


```

A3:

Another Example: Code DQL10_A3_31

The DQL10_A3_31 without a new field like S_NumberOfDigits contains a choice expression.

```dataview
TABLE WITHOUT ID

      // To use a `choice` expression
      choice(GP_Category = "Reading", 
          round(G_Medium_Average_Lengths, 0) + rows.R_Unit[0], 
          round(G_Medium_Average_Lengths, 1) + rows.R_Unit[0]
      ) 
      AS "Average Lengths"

FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC


// without FLATTEN ... AS S_NumberOfDigits




GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


```

Code DQL10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE

Summary_code
title: DQL10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE =>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 add two new dynamical fields including `G_Medium_Average_Lengths` and `today_ooo` into each `group` 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 6.To display the result as a table
collapse: open
icon: 
color: 
```dataview
TABLE WITHOUT ID

      GP_Category AS "Categories",  
      
      rows.file.link AS "Each Link",
      rows.Medium AS "Each Medium",
      
      rows.Q_PagesOrHours AS "Each Length",
      rows.R_Unit AS "Each Unit",
   
      G_Medium_Counts AS "Medium Counts",  
      G_Medium_Lengths AS "Medium Lengths",
    
    
      round(G_Medium_Average_Lengths, rows.S_NumberOfDigits[0]) + rows.R_Unit[0]
      AS "Average Lengths",
      
      
      round(G_Medium_Lengths / today_ooo , 1) + rows.R_Unit[0] AS "Average Lengths read/listened per day"

  
FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC

FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


WHERE P_Category


FLATTEN {
    "Reading": Pages,
    "Listening": Hours
}[P_Category] AS Q_PagesOrHours


FLATTEN {
    "Reading": " pages",
    "Listening": " hours"
}[P_Category] AS R_Unit


FLATTEN {
    "Reading": 0,
    "Listening": 1
}[P_Category] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


FLATTEN length(rows.file.link) AS G_Medium_Counts
FLATTEN sum(rows.Q_PagesOrHours) AS G_Medium_Lengths
FLATTEN G_Medium_Lengths/G_Medium_Counts AS G_Medium_Average_Lengths


FLATTEN number(dateformat(date("2022-12-30"),"ooo")) AS today_ooo

```

Screenshots(DQL10): today=“2022-12-30”


DQL20_sum_Pages_or_Hours_groupBy_P_Category_groupBy_true_via_JavaScript_Objects_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_sum_Pages_or_Hours
_groupBy_P_Category
_groupBy_true
_via_JavaScript_Objects
_and_TABLE
(A dynamical page field)
P_Category:
1.“Reading”
2.“Listening”

(A page field)
Medium:
1.“Book”
2.“GraphicNovel”
3.“eBook”
4.“Article”

5.“Audiobook”
6.“Podcast”
7.“radio”
yes
(twice)
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 add two new dynamical fields including G_Medium_Average_Lengths and today_ooo into each group

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
1.The DQL20 is based on the DQL10.
1.1 added: 5.5 To count the overall file.link
1.2 added: 5.6 To get the overall average Rating

Notes:
1.The P_Category field doesn’t exist in a note.
2.It is created by using the FLATTEN operator.
3.In other words, each page.Medium is dynamically categorized by page.P_Category.

Notes

Summary

Q1: What does the following code mean(FLATTEN ... AS S_NumberOfDigits)? ?

Summary_Q1
Original Example: Code DQL20_Q1 (To be explained)

The original DQL with a new field like S_NumberOfDigits doesn’t contain a choice expression.
Purposes:
1.To simplify multiple choice functions via JavaScript Objects
2.For best performance
3.For easier reading
4.For easier modification
5.For easier debugging

```dataview
TABLE WITHOUT ID
      
      // Not to use a `choice` expression
      map(rows, (rows) =>       
          round(rows.G_Medium_Average_Lengths, rows.rows.S_NumberOfDigits[0]) + rows.rows.R_Unit[0]
      )
      AS "Average Lengths"



FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC


// with FLATTEN ... AS S_NumberOfDigits
FLATTEN {
    "Reading": 0,
    "Listening": 1
}[S_NumberOfDigits] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC




GROUP BY TRUE

```

A1:

Another Example: Code DQL20_A1_11

The A1_11 DQL without a new field like S_NumberOfDigits contains a choice expression.

```dataview
TABLE WITHOUT ID

      // To use a `choice` expression
      map(rows, (rows) =>       
          choice(rows.GP_Category = "Reading", 
              round(rows.G_Medium_Average_Lengths, 0) + rows.rows.R_Unit[0], 
              round(rows.G_Medium_Average_Lengths, 1) + rows.rows.R_Unit[0]
         )   
      )
      AS "Average Lengths"

FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC

// without FLATTEN ... AS S_NumberOfDigits




GROUP BY P_Category AS GP_Category
SORT GP_Category DESC



GROUP BY TRUE

```

Code DQL20_sum_Pages_or_Hours_groupBy_P_Category_groupBy_true_via_JavaScript_Objects_and_TABLE

Summary_code
title: DQL20_sum_Pages_or_Hours_groupBy_P_Category_groupBy_true_via_JavaScript_Objects_and_TABLE =>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 add two new dynamical fields including `G_Medium_Average_Lengths` and `today_ooo` into each `group` 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: 
```dataview
TABLE WITHOUT ID

      rows.GP_Category AS "Categories",
      
      rows.rows.file.link AS "Each Link",
      rows.rows.Medium AS "Each Medium",
      
      rows.rows.Q_PagesOrHours AS "Each Length",
      rows.rows.R_Unit AS "Each Unit",     
   
      rows.G_Medium_Counts AS "Medium Counts",  
      rows.G_Medium_Lengths AS "Medium Lengths",      
      
      
      map(rows, (rows) =>       
          round(rows.G_Medium_Average_Lengths, rows.rows.S_NumberOfDigits[0]) + rows.rows.R_Unit[0]
      )
      AS "Average Lengths",

     
      map(rows, (rows) =>     
          round(rows.G_Medium_Lengths / today_ooo , 1) + rows.rows.R_Unit[0] 
      )
      AS "Average Lengths read/listen per day",     


      G2_OverallCounts AS "Overall Count",
      
      round(G2_OverallRatings / G2_OverallCounts , 1) AS "Average Rating(out of 10)"
      
      
FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC

FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


WHERE P_Category


FLATTEN {
    "Reading": Pages,
    "Listening": Hours
}[P_Category] AS Q_PagesOrHours


FLATTEN {
    "Reading": " pages",
    "Listening": " hours"
}[P_Category] AS R_Unit


FLATTEN {
    "Reading": 0,
    "Listening": 1
}[P_Category] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


FLATTEN length(rows.file.link) AS G_Medium_Counts
FLATTEN sum(rows.Q_PagesOrHours) AS G_Medium_Lengths
FLATTEN G_Medium_Lengths/G_Medium_Counts AS G_Medium_Average_Lengths

GROUP BY TRUE


FLATTEN sum(map(rows.G_Medium_Counts, (e) => sum(default(e, 0)))) AS G2_OverallCounts
FLATTEN sum(map(rows.rows.Rating, (e) => sum(default(e, 0)))) AS G2_OverallRatings


FLATTEN number(dateformat(date("2022-12-30"),"ooo")) AS today_ooo

```

Screenshots(DQL20): today=“2022-12-30”


Reference

Summary

Related Resources

Summary

Sum of Frontmatter Variables, Book Database

Is it possible to transclude code blocks somehow?

FLATTEN


1 Like

Topic : 2/4

Summary
  • How to simplify nested choice operators via JavaScript Objects? (DQL10_Q1, DQL10_Q3, DQL20_Q1, DQL10, DQL20)
  • How to simplify multiple OR operators via JavaScript Objects? (DVJS01_Q1, DVJS01_Q2,DVJS10_Q1, DVJS10_Q2, DVJS10_Q3, DVJS01, DVJS10)
  • How to simplify the following output?(DQL10, DQL20, DVJS01, DVJS10)
```md
This year, I've read 2 books and I've listened to 2 audiobooks (of which 1 were podcasts), for an overall 4 books and audiobooks read.

I've read 0 pages this year. And I've listened to 0.0 hours.

The average rating for the overall 4 books and audiobooks is 4.5 out of 10.

Average book length: 0 pages

Average audiobook length: 0.0 hours

Average pages read per day: 0.0 pages
```

DVJS01_simplify_multiple_or_operators_via_JavaScript_Objects

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS01
simplify_multiple_or_operators
via_JavaScript_Objects
(A dynamical page field)
P_Category:
1.“Reading”
2.“Listening”

(A page field)
Medium:
1.“Book”
2.“GraphicNovel”
3.“eBook”
4.“Article”

5.“Audiobook”
6.“Podcast”
7.“radio”
yes

Tips:To group by P_Category
without using the groupBy function
1.To define a variable like h_medium_category_of
1.1 h_medium_category_of[“Book”] = “Reading” =>The category of a “Book” is “Reading”.
1.2 page.P_Category = h_medium_category_of[“Book”] =>page.P_Category = “Reading”
1.3 h_medium_category_of[“Audiobook”] = “Listening”=>The category of a “Audiobook” is “Listening”.
1.4 page.P_Category = h_medium_category_of[“Audiobook”] =>page.P_Category = “Listening”

2.To count the file.link according to “Reading” or “Listening”
3.To sum up the Pages(or Hours) according to “Reading” or “Listening”

4.To display the result as a table
Notes:
1.The P_Category field doesn’t exist in a note.
2.Suppoese that a hidden field like P_Category exists in each note.
3.In other words, each page.Medium is dynamically categorized by page.P_Category.

Notes

Summary

Q1: How to modify the DVJS01 when “typeP”, “typeQ” or “typeR” (page.P_Category: “Reading”, “Listening”, “Listening”) is added to a Medium field content? (M09)

Summary_Q1
Original Example: Q1 (To be modified)
```dataviewjs
// M09. define h_medium_category_of:
// #####################################################################
let h_medium_category_of = {
    Book: "Reading",
    GraphicNovel: "Reading",
    eBook: "Reading",
    Article: "Reading",

    Audiobook: "Listening",
    Podcast: "Listening",
    radio: "Listening",
    
};

```

A1:

Another Example: A1_11
```dataviewjs
// M09. define h_medium_category_of:
// #####################################################################
let h_medium_category_of = {
    Book: "Reading",
    GraphicNovel: "Reading",
    eBook: "Reading",
    Article: "Reading",
    typeP:  "Reading",       // Added by Justdoitcc 2022-12-30
    
    Audiobook: "Listening",
    Podcast: "Listening",
    radio: "Listening",
    typeQ:  "Listening",     // Added by Justdoitcc 2022-12-30
    typeR:  "Listening",     // Added by Justdoitcc 2022-12-30

};

```

Q2: How to simplify the DVJS01 where the steps like the M21~M27 deal with what the steps like the M31~M37 do?

Summary_Q2
Original Example: Q2 (To be modified)
```dataviewjs
// M09. define h_medium_category_of:
// #####################################################################
let h_medium_category_of = {
    Book: "Reading",
    GraphicNovel: "Reading",
    eBook: "Reading",
    Article: "Reading",

    Audiobook: "Listening",
    Podcast: "Listening",
    radio: "Listening",
    
};


// M21. define pages_by_reading:
// WHERE_CASE_Reading: P_Category="Reading"
// #####################################################################


// M25. define BookCount: 
// #####################################################################


// M27. define pagesum: To sum each Pages field 
// #####################################################################


// M31. define pages_by_listening: 
// WHERE_CASE_Listening: P_Category="Listening"
// #####################################################################


// M35. define AudioCount: 
// #####################################################################


// M37. define hoursum: To sum each Hours field 
// #####################################################################


// M51. TABLE : 
// #####################################################################


```

A2:

Another Example: A2_21

Tips:
1.To use the groupBy function
2.To group by h_medium_category_of[page.Medium]
3.The DVJS10 is an example.


Code DVJS01_simplify_multiple_or_operators_via_JavaScript_Objects

Summary_code
title: DVJS01_simplify_multiple_or_operators_via_JavaScript_Objects => 0.page.P_Category="Reading" or "Listening" 1.To define a variable like `h_medium_category_of` 1.1 h_medium_category_of["Book"] = "Reading" =>The category of a "Book" is "Reading". 1.2 page.P_Category = h_medium_category_of["Book"] =>page.P_Category = "Reading" 1.3 h_medium_category_of["Audiobook"] = "Listening"=>The category of a "Audiobook" is "Listening". 1.4 page.P_Category = h_medium_category_of["Audiobook"] =>page.P_Category = "Listening" 2.To count the file.link according to "Reading" or "Listening" 3.To sum up the `Pages`(or `Hours`) according to "Reading" or "Listening" 4.To display the result as a table
collapse: open
icon: 
color: 
```dataviewjs
// M09. define h_medium_category_of:
// #####################################################################
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]
    );


// M21. define pages_by_reading:
// WHERE_CASE_Reading: P_Category==="Reading"
// where page.P_Category === "Reading"
// #####################################################################
let pages_by_reading = pages
    // .where((page) => page.Medium)
    .where(
        (page) =>
            // No modification required            
            h_medium_category_of[page.Medium] === "Reading"
    );


// M25. define BookCount: 
// #####################################################################
let BookCount = pages_by_reading.length;


// M27. define pagesum: To sum each Pages field 
// #####################################################################
let pagesum = dv.func.default(dv.func.sum(pages.Pages), 0);


// M31. define pages_by_listening: 
// WHERE_CASE_Listening: P_Category==="Listening"
// where page.P_Category === "Listening"
// #####################################################################
let pages_by_listening = pages
    // .where((page) => page.Medium)
    .where(
        (page) =>
            // No modification required
            h_medium_category_of[page.Medium] === "Listening"
    );


// M35. define AudioCount: 
// #####################################################################
let AudioCount = pages_by_listening.length;


// M37. define hoursum: To sum each Hours field 
// #####################################################################
let hoursum = dv.func.default(dv.func.sum(pages.Hours), 0);


// M71. define s_today: 
// #####################################################################
// let s_today = "today";
let s_today = "2022-12-30";


// M51. TABLE : 
// #####################################################################
dv.header(3, "M51.Reading Report on " + s_today);
dv.table(
    ["Categories", "Medium Counts", "Medium Lengths", "Average Lengths"],
    [
        [
            "Reading",
            BookCount,
            pagesum,
            (pagesum / BookCount).toFixed() + " pages",
        ],
        [
            "Listening",
            AudioCount,
            hoursum,
            (hoursum / AudioCount).toFixed(1) + " hours",
        ],
    ]
);


// M61. define Grade: 
// For each Medium
// #####################################################################
let Grade = dv.func.default(dv.func.sum(pages.Rating), 0);


// M63. define AvgRating:
// For each Medium
// #####################################################################
let AvgRating = Grade / pages.length;


// M65. define AvgRating:
// For each Medium
// #####################################################################
let sAvgRating =
    "### M67.The average rating for the overall " +
    pages.length +
    " books and audiobooks is " +
    AvgRating.toFixed(1) +
    " out of 10.";


// M67. output sAvgRating: 
// #####################################################################
dv.span(sAvgRating);


// M71. define today_ooo: 
// 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");


// M73. output pagesum / today: 
// #####################################################################
dv.span(
    "### M73.Average pages read per day: " + (pagesum / today_ooo).toFixed(1) + " pages"
);

```

Screenshots(DVJS01)


Topic : 3/4

Summary
  • How to simplify nested choice operators via JavaScript Objects? (DQL10_Q1, DQL10_Q3, DQL20_Q1, DQL10, DQL20)
  • How to simplify multiple OR operators via JavaScript Objects? (DVJS01_Q1, DVJS01_Q2,DVJS10_Q1, DVJS10_Q2, DVJS10_Q3, DVJS01, DVJS10)
  • How to simplify the following output?(DQL10, DQL20, DVJS01, DVJS10)
```md
This year, I've read 2 books and I've listened to 2 audiobooks (of which 1 were podcasts), for an overall 4 books and audiobooks read.

I've read 0 pages this year. And I've listened to 0.0 hours.

The average rating for the overall 4 books and audiobooks is 4.5 out of 10.

Average book length: 0 pages

Average audiobook length: 0.0 hours

Average pages read per day: 0.0 pages
```

DVJS10_Notes

Summary

Q1: How to add four new dynamical fields including P_Category, Q_PagesOrHours, R_Unit and S_NumberOfDigits into each page via DVJS? (M19)

Summary_Q1
Original Example: Q1 (To be modified)

Methods: To use the if…else if… statement in the step M19.

```dataviewjs
// M09. define h_medium_category_of: {medium: P_Category}
// #####################################################################
let h_medium_category_of = {
    Book: "Reading",
    GraphicNovel: "Reading",
    eBook: "Reading",
    Article: "Reading",

    Audiobook: "Listening",
    Podcast: "Listening",
    radio: "Listening",
    
};


// M19. update pages: add four new fields
// page.P_Category="Reading" or "Listening"
// Adding four new fields is like using the `FLATTEN` operator in DQL.
// add a new field P_Category      :The same as the T01 in the DQL10 (Alias="Medium Category")
// add a new field Q_PagesOrHours  :The same as the T03 in the DQL10 (Alias="Medium Length")
// add a new field R_Unit          :The same as the T05 in the DQL10 (Alias="Medium Unit")
// add a new field S_NumberOfDigits:The same as the T07 in the DQL10 (Alias="Number of Digits")
// #####################################################################
pages.forEach((page) => {
    let P_Category_of_page = h_medium_category_of[page.Medium];
    if (P_Category_of_page === "Reading") {
        
        // M19.FI10R To add a new field P_Category: Medium Category
        // #############################################################
        page.P_Category = "Reading";
        

        // M19.FI20R To add a new field Q_PagesOrHours: Medium Length
        // #############################################################
        page.Q_PagesOrHours = page.Pages;
        

        // M19.FI30R To add a new field R_Unit: Medium Unit
        // #############################################################
        page.R_Unit = " pages";

        
        // M19.FI40R To add a new field S_NumberOfDigits: Number of Digits
        // #############################################################
        page.S_NumberOfDigits = 0;
        
    } else if (P_Category_of_page === "Listening") {
        
        // M19.FI10L To add a new field P_Category: Medium Category
        // #############################################################
        page.P_Category = "Listening";
        

        // M19.FI20L To add a new field Q_PagesOrHours: Medium Length
        // #############################################################
        page.Q_PagesOrHours = page.Hours;
        

        // M19.FI30L To add a new field R_Unit: Medium Unit
        // #############################################################
        page.R_Unit = " hours";
        

        // M19.FI40L To add a new field S_NumberOfDigits: Number of Digits
        // #############################################################
        page.S_NumberOfDigits = 1;
        
    } else {
        // To do nothing
    }
    
    
    
    // M19.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
    
});



```

A1_11: M15 + M17 = M19

Another Example: A1_11

Methods: To use the h_medium_PQRS_of with and if…else if… statement in the step M15 and M17 instead of the M19.
Purposes:
1.To simplify if…else if… statement via JavaScript Objects
2.For best performance
3.For easier reading
4.For easier modification
5.For easier debugging
6.It is very useful when page.P_Category is to be like “Reading”, “Listening”, “catP”, “catQ” or"catR".
Tips: The value of the page.P_Category is “Reading”, “Listening” or undefined.

```dataviewjs
// M09. define h_medium_category_of: {medium: P_Category}
// #####################################################################
let h_medium_category_of = {
    Book: "Reading",
    GraphicNovel: "Reading",
    eBook: "Reading",
    Article: "Reading",

    Audiobook: "Listening",
    Podcast: "Listening",
    radio: "Listening",
    
};


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

});




```

Q2: How to add four new dynamical fields including P_Category, Q_PagesOrHours, R_Unit and S_NumberOfDigits into each page via DQL? (T01+T03+T05+T07)

Summary_Q2

A2_21: T01 + T03 + T05 + T07 = M19

Another Example: Code DQL021_A2_21

Methods: To define four field variables by using the FLATTEN operator.

```dataview
TABLE WITHOUT ID

      rows.file.link AS "File",
      rows.Medium AS "Medium",

      rows.P_Category AS "P_Category",
      rows.Q_PagesOrHours AS "Q_PagesOrHours",
      rows.R_Unit AS "R_Unit",
      rows.S_NumberOfDigits AS "S_NumberOfDigits"

FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


WHERE P_Category


// T03. define page.Q_PagesOrHours: Alias="Medium Length"
// P_Category="Reading"    => Pages
// P_Category="Listening"  => Hours
// #####################################################################
// let page.Q_PagesOrHours = Pages; 
FLATTEN {
    "Reading": Pages,
    "Listening": Hours
}[P_Category] AS Q_PagesOrHours


// T05. define page.R_Unit: Alias="Medium Unit"
// P_Category="Reading"    => " pages"
// P_Category="Listening"  => " hours"
// #####################################################################
// let page.R_Unit = " pages";
FLATTEN {
    "Reading": " pages",
    "Listening": " hours"
}[P_Category] AS R_Unit


// T07. define page.S_NumberOfDigits: Alias="Number of Digits"
// P_Category="Reading"    => 0
// P_Category="Listening"  => 1
// To be used in the step:To get the Average Lengths by each group
// #####################################################################
// let page.S_NumberOfDigits = 0;
FLATTEN {
    "Reading": 0,
    "Listening": 1
}[P_Category] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


```

Screenshots(DQL021_A2_21)


Q3: How to add two new dynamical fields including G_Medium_Counts and G_Medium_Lengths into each group via DVJS? (M47)

Summary_Q3
Original Example: Q3 (To be explained)
```dataviewjs
// 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 

});


```

A3: T21 + T23 = M47

Another Example: Code DQL031_A3_31
```dataview
TABLE WITHOUT ID

      rows.file.link AS "File",
      rows.Medium AS "Medium",

      rows.P_Category AS "P_Category",
      rows.Q_PagesOrHours AS "Q_PagesOrHours",
      rows.R_Unit AS "R_Unit",
      rows.S_NumberOfDigits AS "S_NumberOfDigits"

      G_Medium_Counts AS "G_Medium_Counts",
      G_Medium_Lengths AS "G_Medium_Lengths"  

FROM "100_Project/01_dataviewjs/01_by_example/Q80_MediumCat/Q80_test_data"

WHERE Status != null
WHERE Medium != null
SORT file.name ASC


// T01. define page.P_Category: Alias="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; 
FLATTEN {
    "Book":  "Reading",
    "GraphicNovel":  "Reading",
    "eBook":  "Reading",
    "Article":  "Reading",
    
    "Audiobook":  "Listening",
    "Podcast":  "Listening",
    "radio":  "Listening"    
}[Medium] AS P_Category


WHERE P_Category


// T03. define page.Q_PagesOrHours: Alias="Medium Length"
// P_Category="Reading"    => Pages
// P_Category="Listening"  => Hours
// #####################################################################
// let page.Q_PagesOrHours = Pages; 
FLATTEN {
    "Reading": Pages,
    "Listening": Hours
}[P_Category] AS Q_PagesOrHours


// T05. define page.R_Unit: Alias="Medium Unit"
// P_Category="Reading"    => " pages"
// P_Category="Listening"  => " hours"
// #####################################################################
// let page.R_Unit = " pages";
FLATTEN {
    "Reading": " pages",
    "Listening": " hours"
}[P_Category] AS R_Unit


// T07. define page.S_NumberOfDigits: Alias="Number of Digits"
// P_Category="Reading"    => 0
// P_Category="Listening"  => 1
// To be used in the step:To get the Average Lengths by each group
// #####################################################################
// let page.S_NumberOfDigits = 0;
FLATTEN {
    "Reading": 0,
    "Listening": 1
}[P_Category] AS S_NumberOfDigits


GROUP BY P_Category AS GP_Category
SORT GP_Category DESC


// T21. update groups: add a new field into each group
// To add a new field G_Medium_Counts : Alias="Medium Counts" by Each group
// #####################################################################
FLATTEN length(rows.file.link) AS G_Medium_Counts


// T23. update groups: add a new field into each group
// To add a new field G_Medium_Lengths: Alias="Medium Lengths" by Eachr goup
// #####################################################################
FLATTEN sum(rows.Q_PagesOrHours) AS G_Medium_Lengths



```

Screenshots(DQL031_A3_31)


Q4: How to modify the following code via DVJS when I can neither add four new dynamical fields including P_Category, Q_PagesOrHours, R_Unit and S_NumberOfDigits into each page nor add two new dynamical fields including G_Medium_Counts and G_Medium_Lengths into each group? (M51)

Summary_Q4
Original Example: Q4 (To be modified)

Methods: To use six new dynamical fields in the step M51.
Purposes:
1.To simplify if…else if… statement via JavaScript Objects
2.For best performance
3.For easier reading
4.For easier modification
5.For easier debugging
6.It is very useful when page.P_Category is to be like “Reading”, “Listening”, “catP”, “catQ” or"catR".
Tips: The value of the page.P_Category is “Reading”, “Listening” or undefined.

```dataviewjs
// 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)
          
]);


```

A4_41:

Another Example: A4_41

Methods: Not to use six new dynamical fields in the step M51.
Tips:
1.The duplicate code like dv.func.default(dv.func.sum(group.rows.Pages), 0) appears three times.
2.The duplicate code like dv.func.default(dv.func.sum(group.rows.Hours), 0) appears three times.

```dataviewjs

// 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"
    group.key,


    // M51.TB20 To get the overall Medium Counts by each group:
    // Headers: "Medium Counts"
    // #####################################################################
    // "Reading"  : BookCount
    // "Listening": AudioCount
    // CASE_A: Without adding four new dynamical fields into each page
    // CASE_B: With adding four new dynamical fields into each page
    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)
          
]);


```

Thank you. It is very helpful.

Topic : 4/4

Summary
  • How to simplify nested choice operators via JavaScript Objects? (DQL10_Q1, DQL10_Q3, DQL20_Q1, DQL10, DQL20)
  • How to simplify multiple OR operators via JavaScript Objects? (DVJS01_Q1, DVJS01_Q2,DVJS10_Q1, DVJS10_Q2, DVJS10_Q3, DVJS01, DVJS10)
  • How to simplify the following output?(DQL10, DQL20, DVJS01, DVJS10)
```md
This year, I've read 2 books and I've listened to 2 audiobooks (of which 1 were podcasts), for an overall 4 books and audiobooks read.

I've read 0 pages this year. And I've listened to 0.0 hours.

The average rating for the overall 4 books and audiobooks is 4.5 out of 10.

Average book length: 0 pages

Average audiobook length: 0.0 hours

Average pages read per day: 0.0 pages
```

DVJS10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10
_sum_Pages_or_Hours
_groupBy_P_Category
_via_JavaScript_Objects
_and_TABLE
(A dynamical page field)
P_Category:
1.“Reading”
2.“Listening”

(A page field)
Medium:
1.“Book”
2.“GraphicNovel”
3.“eBook”
4.“Article”

5.“Audiobook”
6.“Podcast”
7.“radio”
yes
(once)
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
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
1.The DVJS10 is based on the DQL10.

Notes:
1.The P_Category field doesn’t exist in a note.
2.Suppoese that a hidden field like P_Category exists in each note.
3.In other words, each page.Medium is dynamically categorized by page.P_Category.

Code DVJS10_sum_Pages_or_Hours_groupBy_P_Category_via_JavaScript_Objects_and_TABLE

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";


```

Screenshots(DVJS10): today=“2022-12-30”


1 Like

Hey, sorry for the late reply. I really appreciate the thorough answer, but I’m going to be honest, I don’t understand most of it, so I’m going to have to go through it slowly, to really figure it out. But I really appreciate it!

1 Like

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