Hello.. I need a help to make a table query using inline fields and consolidate per Date and Task and sum the time per activity.
By the way, English is not my language, sorry my mistakes…
I have the follow data in daily notes:
-
Day:: 2025-06-19
- Time:: 00:35
- Task:: Activity 2
-
Day:: 2025-06-19
- Time:: 00:55
- Task:: Activity 2
-
Day:: 2025-06-20
- Time:: 00:40
- Task:: Activity 3
-
Day:: 2025-06-21
- Time:: 00:20
- Task:: Activity 1
-
Day:: 2025-06-21
- Time:: 00:30
- Task:: Activity 2
-
Day:: 2025-06-21
- Time:: 01:25
- Task:: Activity 1
Things I have tried
I’ve tried this query with results:
TABLE WITHOUT ID item.Day, Task, Time
FLATTEN file.lists as item
FLATTEN filter(item.children, (c) => c.Task)[0].Task as Task
FLATTEN filter(item.children, (c) => c.Time)[0].Time as Time
WHERE item.Day
SORT Day
But, I’d like:
Same of Grouped by week, and summed:
TABLE sum(rows.cTime) as "Time", rows.cTask as "Tasks"
FLATTEN file.lists as item
FLATTEN filter(item.children, (c) => c.Time)[0].Time as cTime
FLATTEN filter(item.children, (c) => c.Task)[0].Task as cTask
WHERE item.Day
GROUP BY dateformat(item.Day, "WW") as Week
Pretended result:
Thanks for help!!!