Table with values grouped by week

Things I have tried

Hi,

I’m trying to create a view of my workouts per week.

I’ve managed to create a table of the workouts grouped by week, with some high-level information:

TABLE
  "3-4x week" as target,
  length(rows.workout) as "number of workouts",
  join(rows.file.link) as "workout dates",
  choice(length(rows.workout) >= 3, "✅", "❌") as "target met"
FROM "journals"
WHERE workout
GROUP BY dateformat(file.ctime, "yyyy-WW") AS week
SORT week ASC
week target number of workouts workout dates target met
2022-40 3-4x week 1 2022_10_05 :x:
2022-42 3-4x week 2 2022_10_19, 2022_10_20 :x:

What I’m trying to do

Now I would like to add additional columns with the days from Monday to Sunday and mark with a :white_check_mark: the days I worked out.

I thought of doing so by adding e.g.
choice(any(dateformat(rows.file.ctime, "c"), (x) => x = 3), "✅", "-") as "Wed"

That however doesn’t seem to work, as the “any” function always returns “false”.

Thank you in advance!

Leave here some notes examples with your metadata structure to explore a solution.
(but to clarify: in each row (by week) you want to see a list with the days with some workout? but you excluded files where there’s no workout!)

Topic

Summary
  • How to transform the ISO weekday into Monday to Sunday and the mark with a :white_check_mark: ?

Test

Summary
  • dataview: v0.5.46

Input

Summary

dictionary files:

  • Location: “100_Project/02_dataview/Q19_Workout/Q19_test_data”

folder: 2022-40

  • filename : 2022_10_05
  • file.ctime: “2022-10-05T19:30:50”
---
workout: 60 minutes
---



folder: 2022-42

  • filename : 2022_10_19
  • file.ctime: “2022-10-19T19:30:50”
---
workout: 60 minutes
---



  • filename : 2022_10_20
  • file.ctime: “2022-10-20T19:30:50”
---
workout: 60 minutes
---



folder: 2022-43

  • filename : 2022_10_24
  • file.ctime: “2022-10-24T19:30:50”
---
workout: 90 minutes
---



  • filename : 2022_10_25
  • file.ctime: “2022-10-25T19:30:50”
---
workout: 90 minutes
---



  • filename : 2022_10_26
  • file.ctime: “2022-10-26T19:30:50”
---
workout: 90 minutes
---



DQL10_filter_by_workout_group_by_week_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_filter_by_workout
_group_by_week
_and_TABLE
dateformat(rows.file.ctime, "c"):
ISO weekday (of the week),
a number from 1-7
(Monday is 1, Sunday is 7)
yes 1.To filter by workout
2.To group by week
3.To sort by week in ascending order
4.To display the result as a table

Notes

Summary

Inline DQL exercises:

  • “c”: day of the week, a number from 1-7 (Monday is 1, Sunday is 7)
```md
F_weekday_1= `=dateformat(date("2022-07-18"), "c")`//=>1
F_weekday_2= `=dateformat(date("2022-07-19"), "c")`//=>2
F_weekday_3= `=dateformat(date("2022-07-20"), "c")`//=>3
F_weekday_4= `=dateformat(date("2022-07-21"), "c")`//=>4
F_weekday_5= `=dateformat(date("2022-07-22"), "c")`//=>5
F_weekday_6= `=dateformat(date("2022-07-23"), "c")`//=>6
F_weekday_7= `=dateformat(date("2022-07-24"), "c")`//=>7
```

```md
fullname_of_ISO_weekday=`=
{
    "1": "Monday",
    "2": "Tuesday",
    "3": "Wednesday",
    "4": "Thursday",
    "5": "Friday",
    "6": "Saturday",
    "7": "Sunday"
}["7"]`
//==>"Sunday"
```

The same codes:

  • To get the fullname of the ISO weekday that is a number from 1-7 (Monday is 1, Sunday is 7)
Original Example10
```dataview
{
    "1": "Monday",
    "2": "Tuesday",
    "3": "Wednesday",
    "4": "Thursday",
    "5": "Friday",
    "6": "Saturday",
    "7": "Sunday"
}[e] AS "fullname_of_ISO_weekday"
```
Another Example11: non-formatted
```dataview
{"1":"Monday","2":"Tuesday","3":"Wednesday","4":"Thursday","5":"Friday","6":"Saturday","7":"Sunday"}[e]  AS "fullname_of_ISO_weekday"
```
Another Example12
```dataview
object("1","Monday","2","Tuesday","3","Wednesday","4","Thursday","5","Friday","6","Saturday","7","Sunday")[e]  AS "fullname_of_ISO_weekday"
```

Code DQL10_filter_by_workout_group_by_week_and_TABLE

Summary_code
title: DQL10_filter_by_workout_group_by_week_and_TABLE =>1.To filter by `workout` 2.To group by `week` 3.To sort by `week` in ascending order 4.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      week AS "week",
      "3-4x week" AS target,
      length(rows.workout) AS "number of workouts",
      join(rows.file.link) AS "workout dates",
      choice(length(rows.workout) >= 3, "✅", "❌") AS "target met",
      
      choice(length(rows.workout) < 3,
          "-", 
          join(
              map(dateformat(rows.file.ctime, "c"), 
                  (e) => 
                      "✅" + 
                      {
                          "1": "Monday",
                          "2": "Tuesday",
                          "3": "Wednesday",
                          "4": "Thursday",
                          "5": "Friday",
                          "6": "Saturday",
                          "7": "Sunday"
                      }[e]                  
              )
          )
      ) 
      AS "Wed"

FROM "100_Project/02_dataview/Q19_Workout/Q19_test_data"
WHERE workout != null

GROUP BY dateformat(file.ctime, "yyyy-WW") AS week
SORT week ASC

```

Screenshots(DQL10):


Reference

Summary

2 Likes

Thanks a lot for your help! It helped me better understand the logic underneath.
The solution by @justdoitcc will be great for a different version of the table I’m implementing :blush:

I’ve figured out that the result of the function dateformat(rows.file.ctime, "c") is a string and not a number as I was expecting.
Therefore the any() function I had in mind (returns true if ANY of the values in the array are truth) needed to be adjusted.

Here the final solution for the additional columns, should anyone be interested:

 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "1"), "✅", "-") as "Mon",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "2"), "✅", "-") as "Tue",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "3"), "✅", "-") as "Wed",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "4"), "✅", "-") as "Thu",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "5"), "✅", "-") as "Fri",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "6"), "✅", "-") as "Sat",
 choice(any(dateformat(date(replace(rows.file.name, "_", "-")), "c"), (x) => x = "7"), "✅", "-") as "Sun"

Output:

image

1 Like

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