How do I create a dataview table displaying the number of habits I've tracked in a week?

I don’t like check boxes and I don’t use tags for habit. In my daily note, I usually track my habit like this

  • Reading:: :white_check_mark:
  • Exercise:: :x:

In my weekly note, I have a table that looks like this

image

I want to create another column to count the total of habits with a :white_check_mark:. For instance, if on Monday I managed to do 3 habits out of 5, then the new column should display 3. How do I achieve this?

I think there are two ways for me to do this, but I don’t know how to create the dataview syntax with this (I’m not very good with computer)

  1. Somehow have :white_check_mark: = 1 and :x: = 0 then get the sum
  2. Instead of using emoji, I just put 1 for yes and 0 for no, then just add all the values together. But then the table will look rather ugly with just 1 or 0 , so if switch to value instead of emoji in my daily note, how do I make it display as 1 = :white_check_mark: and 0 = :x:? I thought I’ve seen people doing this somewhere but can’t seem to find that info now

Suppose that “on Monday” is corrected to “from Monday to Sunday per week”.

Topic : 1/2

Summary
  • How to create a dataview table displaying the number of habits I’ve tracked per week? (P.S. Somehow have :white_check_mark: = 1 and :x: = 0 then get the sum) (DQL10, DQL20)
  • How to write a DVJS with the FLATTEN ideas step by step? ( DVJS10)
  • How to simplify the DVJS10, which is written with the FLATTEN ideas step by step? ( DVJS10_A1_11_shorter)

Test

Summary
  • dataview: v0.5.55

Input

Summary

dictionary files:

  • Location: “100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data”

folder: 2022-40

  • filename : 05-Oct-2022
  • file.ctime: “2022-10-05T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-05
---
Reading :: ✅
Exercise :: ❌


folder: 2022-42

  • filename : 19-Oct-2022
  • file.ctime: “2022-10-19T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-19
---
Reading ::  ❌
Exercise ::  ✅


  • filename : 20-Oct-2022
  • file.ctime: “2022-10-20T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-20
---
Reading :: ✅
Exercise :: ❌


folder: 2022-43

  • filename : 24-Oct-2022
  • file.ctime: “2022-10-24T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-24
---
Reading :: ✅
Exercise :: ❌


  • filename : 25-Oct-2022
  • file.ctime: “2022-10-25T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-25
---
Reading :: ❌
Exercise :: ✅


  • filename : 26-Oct-2022
  • file.ctime: “2022-10-26T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-26
---
Reading :: ❌
Exercise :: ✅


folder: 2022-51_null_undefined

  • filename : 21-Dec-2022
  • file.ctime: “2022-12-21T19:30:50”
---
cDate: 2022-12-21
---
Reading :: 
Exercise :: 


  • filename : 22-Dec-2022
  • file.ctime: “2022-12-22T19:30:50”
---
cDate: 2022-12-22
---
Reading :: 


  • filename : 23-Dec-2022
  • file.ctime: “2022-12-23T19:30:50”
---
cDate: 2022-12-23
---

Exercise :: 


  • filename : 24-Dec-2022
  • file.ctime: “2022-12-24T19:30:50”
---
cDate: 2022-12-24
---


DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE :

NOTE: You always create daily notes on time. In other words, every file.ctime is correct.

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_filter_by_Reading_or_Exercise
_group_by_P_file_ctime
_and_TABLE
dateformat(rows.file.ctime, "yyyy-WW"):
1.yyyy = four- to six- digit year, pads to 4
2.WW = ISO week number, padded to 2


dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a string from “1”-“7”
(“Monday” is “1”, “Sunday” is “7”)
yes 1.To filter by Reading or Exercise
2.To sort by file.name in ascending order

3.To define a field variable P_file_ctime
4.To group by dateformat(P_file_ctime, "yyyy-WW") AS P_week
5.To sort by P_week in ascending order
6.To define a field variable P_target

7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_Total_QTY

8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday

9.To display the result as a table
P.S. You always create daily notes on time. In other words, every file.ctime is correct.

Notes:

Summary

Q1: How to understand the meaning of the DQL10 in terms of the DVJS10?

Summary_Q1

A1_11:

Original Example: A1_11 (To be explained with simple Comments and corresponding DVJS10 statement)
```dataview
// T91. output pages: TABLE
// dv.table(
//     [
//         // P
//         "Week",
//         "Target",
//      
//         // Q
//         "Reading QTY",
//         "Exercise  QTY",
//         "Total QTY",
//      
//         // R
//         "Reading / Exercise dates",
//         "Target met",
//         "Day of the week",
//     ],
//     groups.map((group) => [
//         // P
//         group.P_week,
//         group.P_target,
//      
//         // Q
//         group.Q_Reading_QTY,
//         group.Q_Exercise_QTY,
//         group.Q_total_QTY,
//      
//         // R
//         group.R_dates_of_Reading_Exercise,
//         group.R_target_met,
//         group.R_fullname_of_isoWeekday,
//     ])
// );
// #####################################################################
TABLE WITHOUT ID
      // P
      P_week AS "Week",
      P_target AS "Target",
      
      // Q  
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",

      // R
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"


// T11. define pages: get pages from Sources
// let pages = dv.pages('"100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"');
// #####################################################################
FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"


// T13. filter by Reading or Exercise
// pages = pages.where((page) => page.Reading || page.Exercise);
// #####################################################################
WHERE Reading OR Exercise


// T15. sort by file.name
// pages = pages.sort((page) => page.file.name, "asc");
// #####################################################################
SORT file.name ASC


//   ####
//   #   #
//   #   #
//   ####
//   #
//   #
//   #
//   #


// T31. update pages: add a new field `P_file_ctime` into each page
// pages.forEach((page) => {
//     page.P_file_ctime = page.file.ctime;
// });
// #####################################################################
FLATTEN file.ctime AS P_file_ctime


// T33. define groups: groupBy the pages + add a new field `P_week` into each group
// ***************************************************************** //
// T33_PART01: groupBy the pages
// let groups = pages.groupBy((page) => dv.func.dateformat(page.P_file_ctime, "yyyy-WW"));
// ***************************************************************** //
// T33_PART02: add a new field `P_week` into each group
// groups.forEach((group) => {
//     group.P_week = group.key;
// });
// #####################################################################
GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week


// T35. sort groups: in ascending order
// groups = groups.sort((group) => group.P_week, "asc");
// #####################################################################
SORT P_week ASC


// T37. update groups: add a new field `P_target` into each group
// groups.forEach((group) => {
//     group.P_target = "3-4x week";
// });
// #####################################################################
FLATTEN "3-4x week" AS P_target



//    ###
//   #   #
//   #   #
//   #   #
//   #   #
//   # # #
//    ###
//       #


// T41. update groups: add a new field `Q_Reading_QTY` into each group
// groups.forEach((group) => {
//     group.Q_Reading_QTY = group.rows.Reading.filter((e) => (e === "✅")).length;
// });
// #####################################################################
FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY


// T43. update groups: add a new field `Q_Exercise_QTY` into each group
// groups.forEach((group) => {
//     group.Q_Exercise_QTY = group.rows.Exercise.filter((e) => (e === "✅")).length;
// });
// #####################################################################
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY


// T45. update groups: add a new field `Q_total_QTY` into each group
// groups.forEach((group) => {
//     group.Q_total_QTY = group.Q_Reading_QTY + group.Q_Exercise_QTY;
// });
// #####################################################################
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY



//   ####
//   #   #
//   #   #
//   ####
//   # #
//   #  #
//   #   #
//   #    #



// T51. update groups: add a new field `R_dates_of_Reading_Exercise` into each group
// groups.forEach((group) => {
//     group.R_dates_of_Reading_Exercise = group.rows.file.link.join();
// });
// #####################################################################
FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise


// T53. update groups: add a new field `R_target_met` into each group
// groups.forEach((group) => {
//     group.R_target_met = dv.func.choice(group.Q_total_QTY >= 3, "✅", "❌");
// });
// #####################################################################
FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met


// T55. define h_isoWeekday_Fullname_of + update groups: 
// add a new field `R_fullname_of_isoWeekday` into each group
// ***************************************************************** //
// T55_PART01: define h_isoWeekday_Fullname_of
// let h_isoWeekday_Fullname_of = {
//     "1": "Monday",
//     "2": "Tuesday",
//     "3": "Wednesday",
//     "4": "Thursday",
//     "5": "Friday",
//     "6": "Saturday",
//     "7": "Sunday",
// };
// ***************************************************************** // 
// T55_PART02: add a new field `R_fullname_of_isoWeekday` into each group
// groups.forEach((group) => {
//     group.R_fullname_of_isoWeekday = dv.func.choice(
//         group.Q_total_QTY < 3,
//         "",
//         dv.func.join(
//             dv.func.dateformat(group.rows.P_file_ctime, "c").map(
//                 (e) =>
//                     "✅" + h_isoWeekday_fullname_of[e]
//             )
//         )
//     );
// });
// #####################################################################
FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_file_ctime, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday 

```

Code DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE

Summary_code
title: DQL10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE  =>1.To filter by `Reading` or `Exercise` 2.To sort by `file.name` in ascending order 3.To define a field variable `P_file_ctime` 4.To group by `dateformat(P_file_ctime, "yyyy-WW")` AS `P_week` 5.To sort by `P_week` in ascending order 6.To define a field variable `P_target` 7.To define three field variables, such as `Q_Reading_QTY`, `Q_Exercise_QTY`, and `Q_Total_QTY` 8.To define three field variables, such as `R_dates_of_Reading_Exercise`, `R_target_met`, and `R_fullname_of_isoWeekday` 9.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      P_week AS "Week",
      P_target AS "Target",
      
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",
      
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"

FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"
WHERE Reading OR Exercise
SORT file.name ASC


FLATTEN file.ctime AS P_file_ctime
GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week
SORT P_week ASC
FLATTEN "3-4x week" AS P_target


FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY


FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise

FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met

FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_file_ctime, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday 

```

Screenshots(DQL10): on win7

Screenshots(DQL10): on win10 or android


DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE:

NOTE: Sometimes you create the daily notes later. In other words, not every file.ctime is correct.

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL20
_filter_by_Reading_or_Exercise
_group_by_P_isoDate_from_file_name
_and_TABLE
file.name:
1.“01-Jan-2022”
2.“01-Feb-2022”
12.“01-Dec-2022”


dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a string from “1”-“7”
(“Monday” is “1”, “Sunday” is “7”)
yes 1.To filter by Reading or Exercise
2.To sort by file.name in ascending order

3.To define a field variable P_isoDate_from_file_name
4.To group by dateformat(P_isoDate_from_file_name, "yyyy-WW") AS P_week
5.To sort by P_week in ascending order
6.To define a field variable P_target

7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_total_QTY

8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday

9.To display the result as a table
P.S. Sometimes you create the daily notes later. In other words, not every file.ctime is correct.

Code DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE

Summary_code
title: DQL20_filter_by_Reading_or_Exercise_group_by_P_isoDate_from_file_name_and_TABLE =>1.To filter by `Reading` or `Exercise` 2.To sort by `file.name` in ascending order 3.To define a field variable `P_isoDate_from_file_name` 4.To group by `dateformat(P_isoDate_from_file_name, "yyyy-WW")` AS `P_week` 5.To sort by `P_week` in ascending order 6.To define a field variable `P_target` 7.To define three field variables, such as `Q_Reading_QTY`, `Q_Exercise_QTY`, and `Q_total_QTY` 8.To define three field variables, such as `R_dates_of_Reading_Exercise`, `R_target_met`, and `R_fullname_of_isoWeekday` 9.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      P_week AS "Week",
      P_target AS "Target",
      
      Q_Reading_QTY AS "Reading QTY",
      Q_Exercise_QTY AS "Exercise  QTY",
      Q_total_QTY AS "Total QTY",
      
      R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
      R_target_met AS "Target met",
      R_fullname_of_isoWeekday AS "Day of the week"

FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"
WHERE Reading OR Exercise
SORT file.name ASC


FLATTEN split(file.name, "-")[2] AS yyyy
FLATTEN split(file.name, "-")[1] AS MMM
FLATTEN split(file.name, "-")[0] AS dd


FLATTEN 
{
    "Jan": "01",
    "Feb": "02",
    "Mar": "03",
    "Apr": "04",
    "May": "05",
    "Jun": "06",
    "Jul": "07",
    "Aug": "08",
    "Sep": "09",
    "Oct": "10",
    "Nov": "11",
    "Dec": "12"
}[MMM] AS MM


FLATTEN yyyy + "-" + MM + "-" + dd AS s_isoDate
FLATTEN date(s_isoDate) AS P_isoDate_from_file_name
WHERE P_isoDate_from_file_name

GROUP BY dateformat(P_isoDate_from_file_name, "yyyy-WW") AS P_week
SORT P_week ASC
FLATTEN "3-4x week" AS P_target



FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY
FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY
FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY



FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise

FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met

FLATTEN choice(Q_total_QTY < 3,
            "", 
            join(
                 map(dateformat(rows.P_isoDate_from_file_name, "c"), 
                     (e) => 
                         "✅" + 
                         {
                           "1": "Monday",
                           "2": "Tuesday",
                           "3": "Wednesday",
                           "4": "Thursday",
                           "5": "Friday",
                           "6": "Saturday",
                           "7": "Sunday"
                         }[e]                  
                )
            )
        ) AS R_fullname_of_isoWeekday


```

Screenshots(DQL20): on win7

Screenshots(DQL20): on win10 or android


Conclusion

Summary

CASE01: Every file.ctime is correct.

  • Use the DQL10 if you always create daily notes on time. (In other words, every file.ctime is correct.)

CASE02: Every file.name is correct in the form, such as “01-Jan-2022”.

  • Use the DQL20 if sometimes you create the daily notes later. (In other words, not every file.ctime is correct.)

CASE03: Every cDate is correct.

NOTE:
Suppose that every file.name is correct in the different forms, such as “01-Jan-2022”, “01_Jan_2022”, “Jan 1, 2022”, “Jan/1/2022”, “January 1, 2022”, “31-03-2022”, “31_03_2022”, “03 31, 2022”, and so on.

  • If sometimes you create the daily notes later and every cDate is correct, use the DQL10 with modifying file.ctime to date(cDate).
  • P.S. The field cDate is defined in daily notes in the example.

folder: 2022-40

  • filename : 05-Oct-2022
  • file.ctime: “2022-10-05T19:30:50”
---
# The creation date `cDate` of each daily note is correct.
cDate: 2022-10-05
---
Reading :: ✅
Exercise :: ❌


Original Expression:

```dataview
FLATTEN file.ctime AS P_file_ctime
```

New Expression:

```dataview
FLATTEN date(cDate) AS P_file_ctime
```

Reference

Summary

Emojis

The main DQL

The Regular Expression

JavaScript Objects

dateformat(file.ctime, “yyyy-WW”) or dateformat(rows.file.ctime, “c”)

Implicit Fields: file.ctime, file.name, or file.day


1 Like

Suppose that “on Monday” is corrected to “from Monday to Sunday per week”.

Topic : 2/2

Summary
  • How to create a dataview table displaying the number of habits I’ve tracked per week? (P.S. Somehow have :white_check_mark: = 1 and :x: = 0 then get the sum) (DQL10, DQL20)
  • How to write a DVJS with the FLATTEN ideas step by step? ( DVJS10)
  • How to simplify the DVJS10, which is written with the FLATTEN ideas step by step? ( DVJS10_A1_11_shorter)

DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE : with the FLATTEN ideas step by step

NOTE: the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10
_filter_by_Reading_or_Exercise
_group_by_P_file_ctime
_and_TABLE
dateformat(rows.file.ctime, "yyyy-WW"):
1.yyyy = four- to six- digit year, pads to 4
2.WW = ISO week number, padded to 2


dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a string from “1”-“7”
(“Monday” is “1”, “Sunday” is “7”)
yes 1.To filter by Reading or Exercise
2.To sort by file.name in ascending order

3.To define a field variable P_file_ctime
4.To group by dateformat(P_file_ctime, "yyyy-WW") AS P_week
5.To sort by P_week in ascending order
6.To define a field variable P_target

7.To define three field variables, such as Q_Reading_QTY, Q_Exercise_QTY, and Q_Total_QTY

8.To define three field variables, such as R_dates_of_Reading_Exercise, R_target_met, and R_fullname_of_isoWeekday

9.To display the result as a table
NOTE: the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter

P.S. You always create daily notes on time. In other words, every file.ctime is correct.

Notes:

Summary

Q1: How to simplify the DVJS10, which is written with the FLATTEN ideas step by step?

Summary_Q1

A1_11:

Another Example: Code DVJS10_A1_11_shorter
Summary_A1_11
title: DVJS10_A1_11_shorter  =>the DQL10 = the DVJS10 = the DVJS10_A1_11_shorter
collapse: close
icon: 
color: 
```dataviewjs
// M010. define pages: gather all relevant pages
// M010 = T11 + T13 + T15
// 
// T11.define pages: get pages from Sources
// T13.filter by Reading or Exercise
// T15.sort by file.name
// 
// #####################################################################
let pages = dv
    // T11
    .pages('"100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"')
    // T13
    .where((page) => page.Reading || page.Exercise)
    // T15
    .sort((page) => page.file.name, "asc");



//   ####
//   #   #
//   #   #
//   ####
//   #
//   #
//   #
//   #


// M020.update pages: add a new field `P_file_ctime` into each page
// M020 = T31 
// 
// T31.update pages: add a new field `P_file_ctime` into each page
// #####################################################################
pages.forEach((page) => {
    page.P_file_ctime = page.file.ctime;
});


// M030. define groups + update groups:
// M030 = T33 + T35 + T37
// 
// T33.define groups: groupBy the pages + add a new field `P_week` into each group
// T35.sort groups: in ascending order
// T37.update groups: add a new field `P_target` into each group
//
// #####################################################################
let groups = pages
    // T33_PART01: groupBy the pages
    .groupBy((page) => dv.func.dateformat(page.P_file_ctime, "yyyy-WW"))
    // T35
    .sort((group) => group.key, "asc");


groups.forEach((group) => {
    // T33_PART02: add a new field `P_week` into each group
    group.P_week = group.key
    // T37
    group.P_target = "3-4x week";

});



//    ###
//   #   #
//   #   #
//   #   #
//   #   #
//   # # #
//    ###
//       #


// M040. update groups: 
// M040 = T41 + T43 + T45
// 
// T41.add a new field `Q_Reading_QTY` into each group
// T43.add a new field `Q_Exercise_QTY` into each group
// T45.add a new field `Q_total_QTY` into each group
//
// #####################################################################
groups.forEach((group) => {
    // T41
    group.Q_Reading_QTY = group.rows.Reading.filter((e) => (e === "✅")).length;
    // T43
    group.Q_Exercise_QTY = group.rows.Exercise.filter((e) => (e === "✅")).length;
    // T45
    group.Q_total_QTY = group.Q_Reading_QTY + group.Q_Exercise_QTY;
});



//   ####
//   #   #
//   #   #
//   ####
//   # #
//   #  #
//   #   #
//   #    #


// M050. define groups:
// M050 = T51 + T53 + T55
//
// T51.add a new field `R_dates_of_Reading_Exercise` into each group
// T53.add a new field `R_target_met` into each group
// T55.define h_isoWeekday_Fullname_of +  
//     add a new field `R_fullname_of_isoWeekday` into each group
//
// #####################################################################
// T55_PART01: define h_isoWeekday_Fullname_of
let h_isoWeekday_Fullname_of = {
    "1": "Monday",
    "2": "Tuesday",
    "3": "Wednesday",
    "4": "Thursday",
    "5": "Friday",
    "6": "Saturday",
    "7": "Sunday",
};


groups.forEach((group) => {
    // T51
    group.R_dates_of_Reading_Exercise = group.rows.file.link.join();
    // T53
    group.R_target_met = dv.func.choice(group.Q_total_QTY >= 3, "✅", "❌");
    // T55_PART02: add a new field `R_fullname_of_isoWeekday` into each group
    group.R_fullname_of_isoWeekday = dv.func.choice(
        group.Q_total_QTY < 3,
        "",
        dv.func.join(
            dv.func.dateformat(group.rows.P_file_ctime, "c").map(
                (e) =>
                    "✅" + h_isoWeekday_Fullname_of[e]
            )
        )
    );    
    
});


// M090. output pages: TABLE
// M090 = T91
// 
// T91.output pages: TABLE
// #####################################################################
dv.table(
    [
        // P
        "Week",
        "Target",
        
        // Q
        "Reading QTY",
        "Exercise  QTY",
        "Total QTY",
        
        // R
        "Reading / Exercise dates",
        "Target met",
        "Day of the week",
    ],
    groups.map((group) => [
        // P
        group.P_week,
        group.P_target,
        
        // Q
        group.Q_Reading_QTY,
        group.Q_Exercise_QTY,
        group.Q_total_QTY,
        
        // R
        group.R_dates_of_Reading_Exercise,
        group.R_target_met,
        group.R_fullname_of_isoWeekday,
    ])
);



```

Screenshots(DVJS10_A1_11):


Code DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE

Summary_code
title: DVJS10_filter_by_Reading_or_Exercise_group_by_P_file_ctime_and_TABLE  =>the DQL10 = the DVJS10
collapse: close
icon: 
color: 
```dataviewjs
// T11. define pages: gather all relevant pages
let pages = dv.pages('"100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"');
// #####################################################################
// FROM "100_Project/02_dataview/Q26_ReadingExercise/Q26_test_data"


// T13. filter by Reading or Exercise
pages = pages.where((page) => page.Reading || page.Exercise);
// #####################################################################
// WHERE Reading OR Exercise


// T15. sort by file.name
pages = pages.sort((page) => page.file.name, "asc");
// #####################################################################
// SORT file.name ASC



//   ####
//   #   #
//   #   #
//   ####
//   #
//   #
//   #
//   #


// T31. update pages: add a new field `P_file_ctime` into each page
pages.forEach((page) => {
    page.P_file_ctime = page.file.ctime;
});
// #####################################################################
// FLATTEN file.ctime AS P_file_ctime


// T33. define groups: 
// groupBy the pages + add a new field `P_week` into each group
// ***************************************************************** //
// T33_PART01: groupBy the pages
let groups = pages.groupBy((page) => dv.func.dateformat(page.P_file_ctime, "yyyy-WW"));
// ***************************************************************** //
// T33_PART02: add a new field `P_week` into each group
groups.forEach((group) => {
    group.P_week = group.key;
});
// #####################################################################
// GROUP BY dateformat(P_file_ctime, "yyyy-WW") AS P_week


// T35. sort groups: in ascending order
groups = groups.sort((group) => group.P_week, "asc");
// #####################################################################
// SORT P_week ASC


// T37. update groups: add a new field `P_target` into each group
groups.forEach((group) => {
    group.P_target = "3-4x week";
});
// #####################################################################
// FLATTEN "3-4x week" AS P_target



//    ###
//   #   #
//   #   #
//   #   #
//   #   #
//   # # #
//    ###
//       #


// T41. update groups: add a new field `Q_Reading_QTY` into each group
groups.forEach((group) => {
    group.Q_Reading_QTY = group.rows.Reading.filter((e) => (e === "✅")).length;
});
// #####################################################################
// FLATTEN length(filter(rows.Reading, (e) => e = "✅")) AS Q_Reading_QTY


// T43. update groups: add a new field `Q_Exercise_QTY` into each group
groups.forEach((group) => {
    group.Q_Exercise_QTY = group.rows.Exercise.filter((e) => (e === "✅")).length;
});
// #####################################################################
// FLATTEN length(filter(rows.Exercise, (e) => e = "✅")) AS Q_Exercise_QTY


// T45. update groups: add a new field `Q_total_QTY` into each group
groups.forEach((group) => {
    group.Q_total_QTY = group.Q_Reading_QTY + group.Q_Exercise_QTY;
});
// #####################################################################
// FLATTEN Q_Reading_QTY + Q_Exercise_QTY AS Q_total_QTY



//   ####
//   #   #
//   #   #
//   ####
//   # #
//   #  #
//   #   #
//   #    #



// T51. update groups: add a new field `R_dates_of_Reading_Exercise` into each group
groups.forEach((group) => {
    group.R_dates_of_Reading_Exercise = group.rows.file.link.join();
});
// #####################################################################
// FLATTEN join(rows.file.link) AS R_dates_of_Reading_Exercise


// T53. update groups: add a new field `R_target_met` into each group
groups.forEach((group) => {
    group.R_target_met = dv.func.choice(group.Q_total_QTY >= 3, "✅", "❌");
});
// #####################################################################
// FLATTEN choice(Q_total_QTY >= 3, "✅", "❌") AS R_target_met


// T55. define h_isoWeekday_Fullname_of + update groups: 
// add a new field `R_fullname_of_isoWeekday` into each group
// ***************************************************************** // 
// T55_PART01: define h_isoWeekday_Fullname_of
let h_isoWeekday_Fullname_of = {
    "1": "Monday",
    "2": "Tuesday",
    "3": "Wednesday",
    "4": "Thursday",
    "5": "Friday",
    "6": "Saturday",
    "7": "Sunday",
};
// ***************************************************************** //
// T55_PART02: add a new field `R_fullname_of_isoWeekday` into each group
groups.forEach((group) => {
    group.R_fullname_of_isoWeekday = dv.func.choice(
        group.Q_total_QTY < 3,
        "",
        dv.func.join(
            dv.func.dateformat(group.rows.P_file_ctime, "c").map(
                (e) =>
                    "✅" + h_isoWeekday_Fullname_of[e]
            )
        )
    );
});
// #####################################################################
// FLATTEN choice(Q_total_QTY < 3,
//             "", 
//             join(
//                  map(dateformat(rows.P_file_ctime, "c"), 
//                      (e) => 
//                          "✅" + 
//                          {
//                            "1": "Monday",
//                            "2": "Tuesday",
//                            "3": "Wednesday",
//                            "4": "Thursday",
//                            "5": "Friday",
//                            "6": "Saturday",
//                            "7": "Sunday"
//                          }[e]                  
//                 )
//             )
//         ) AS R_fullname_of_isoWeekday 


// T91. output pages: TABLE
dv.table(
    [
        // P
        "Week",
        "Target",
        
        // Q
        "Reading QTY",
        "Exercise  QTY",
        "Total QTY",
        
        // R
        "Reading / Exercise dates",
        "Target met",
        "Day of the week",
    ],
    groups.map((group) => [
        // P
        group.P_week,
        group.P_target,
        
        // Q
        group.Q_Reading_QTY,
        group.Q_Exercise_QTY,
        group.Q_total_QTY,
        
        // R
        group.R_dates_of_Reading_Exercise,
        group.R_target_met,
        group.R_fullname_of_isoWeekday,
    ])
);
// #####################################################################
// TABLE WITHOUT ID
//       P_week AS "Week",
//       P_target AS "Target",
      
//       Q_Reading_QTY AS "Reading QTY",
//       Q_Exercise_QTY AS "Exercise  QTY",
//       Q_total_QTY AS "Total QTY",
      
//       R_dates_of_Reading_Exercise AS "Reading / Exercise dates",
//       R_target_met AS "Target met",
//       R_fullname_of_isoWeekday AS "Day of the week"


```

Screenshots(DVJS10):


1 Like

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