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::
Exercise::
In my weekly note, I have a table that looks like this
I want to create another column to count the total of habits with a . 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)
Somehow have = 1 and = 0 then get the sum
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 = and 0 = ? I thought I’ve seen people doing this somewhere but can’t seem to find that info now
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
```
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
```
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.
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
```
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
```
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,
])
);
```
