Dataview - Calculation Sum of work hours (clock-in and clock-out) with overtime

dev0zzz · July 21, 2022, 12:26pm

Things I have tried

I have been searching the whole wide Internet, but couldn’t come up with a solutions that fits my idea.

What I have so far is the following:

table
		dateformat(file.day, "cccc") as "weekday",
		dateformat(file.day, "WW") as "calendarweek",
		clock-in as "clockin",
		clock-out as "clockout",
		date("2022-01-01T" + clock-out) - date("2022-01-01T" + clock-in) as "workhours"
from "Calendar/days"
sort file.name desc

table
		date("2022-01-01T" + clock-out) - date("2022-01-01T" + clock-in) as "work hours"
from "Calendar/days"
group by dateformat(file.day, "WW | yyyy") as "calendarweek"
sort desc

What I’m trying to do

Everyday I arrive at work and I write down my time in a daily note in frontmatter as “clock-in” and when I leave I also note the time as “clock-out”.
My company expects me to work exactly 38.5 hours a week. From Monday to Thursday I have a break for exactly 45 minutes.
Usually I have to come to work at 07:00 and have to leave at 16:00, except fridays I leave earlier at 12:30.

However I work overtime sometimes and I want to know how many hours per day/week/month/year I have worked overtime, so I can know when to ask for time compensation.

I have an extra Note, where I want to keep track of all this information.
This is how it looks:

I havent figured out, how to achieve this work hour tracking, so I am asking you.

Additional Information:

I also want to track my time, where I am on vacation and/or am sick.

So how do you think would be the best solution to achieve this?

scholarInTraining · July 21, 2022, 4:24pm

Small fixes:

When you use a GROUP BY in your table, you need to change any field names in the displayed columns to be rows.field.
The result of date() - date() in dataview is a dur(), so you need to pull the hours field from that before you can get the total.
Then I think you want sum to total the hours for the week. So for example :

table
		sum((date("2022-01-01T" + rows.clock-out) - date("2022-01-01T" + rows.clock-in)).hours) as "work hours"
from "Calendar/days"
group by dateformat(file.day, "WW | yyyy") as "calendarweek"
sort desc

Good luck!

dev0zzz · July 22, 2022, 5:51am

Hm I tried that, but I get the following result:

scholarInTraining · July 22, 2022, 6:03am

Ooops, I must have made a mistake then, sorry! How I would investigate the issue would be to display pieces of the calculation and see where there is an issue. For example, just show the “workhours” (should be a list for each week), then if that shows up, do the conversion to hours, then the sum.
A typo in my previous query (would have only affected the English version, not the one in your images): if your field name contains a - then write it as rows["clock-out"] rows["clock-in"]. if your fieldname has no punctuation, can use the rows.fieldName way of writing it in the groupby.

Also it looks like your sort desc does not work - I think you need sort calendarweek desc?

dev0zzz · July 22, 2022, 6:33am

Hey thanks for your help and the quick answer, unfortunatly it doesnt resolve the issue.
Still no data the way I would like to see it^^

I guess, I have to invest some more of my, hopefully yours and the communities brain power into this = )

scholarInTraining · July 23, 2022, 8:10am

Hi, so sorry that my answers are not helping! Just to confirm: is your dataview plugin up to date (Settings → Community Plugins → Check for Updates button)?

I do not have the same time-tracking as you in my daily notes but I did experiment with a numeric field I have tasks-done-today.

TABLE sum(rows["tasks-done-today"]) AS "sum"
FROM "testFolder"
GROUP BY dateformat(file.day, "WW") as "calendarWeek"
SORT calendarWeek DESC

This appears to be working correctly in my vault, including seeing the output table sorted in the correct order. I cannot tell how your example differs from this one that works - maybe you can? Or maybe @Craig or @justdoitcc can add some more dataview expertise?
Good luck!

Edit: typo in codeblock

Craig · July 23, 2022, 3:03pm

Hi all,

Thanks for pinging me. This is an interesting problem! I think the issue is that we have a calculated field (workhours) that needs to be calculated per file before we can aggregate it up to the week level. Here’s the query I used to do the calculation:

```dataview
TABLE
	sum(map(rows, (item) => 
		date("2022-01-01T" + item.clock-out) - 
		date("2022-01-01T" + item.clock-in))) 
		as "workhours"
FROM "Scratch"
WHERE file.day
GROUP BY dateformat(file.day, "WW | yyyy") as "calendarweek"
SORT calendarweek desc
```

Before we can sum hours for the week, we must first use the map() function to iterate over each file and calculate the work hours for the day. That returns a list of durations, which we then use the sum() function to add together into a single field, which we name “workhours”.

I sort by week descending in this example, since that seems to be what you intended in your query.

(The FROM and WHERE clauses are different than yours, so as to just select the files from my particular vault.)

Finally, here’s a working example from my vault:

I hope this helps! Please let us know if it works for you.

scholarInTraining · July 23, 2022, 6:43pm

@Craig thanks! When I was testing things for my first post in this thread, I got an error about sum not handling Durations, but yours seems to be fine with that. Any idea why we got different results?

justdoitcc · July 24, 2022, 1:05pm

@scholarInTraining The bug is found!

Topic

Summary

1.How to debug a code in DQL or Inline DQL?
2.How to calculate the difference between two DateTimes as a Duration?
3.How to sum up the duration of a column(F_workhours), grouping by another column(F_calendarweek)?

1. Input

Summary

1.1. dictionary files

location: “100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data”

1.1.1. N06

filename : dic_20220601_clock

---
Date: 2022-06-01
clock-in: 06:30
clock-out: 13:25
---

filename : dic_20220602_clock

---
Date: 2022-06-02
clock-in: 06:00
clock-out: 14:10
---

filename : dic_20220603_clock (wrong input)

---
Date: 2022-06-03
clock-in: 06:00ice
clock-out: 14:10coffee
---

1.1.2. L07

filename : dic_20220704_clock

---
Date: 2022-07-04
clock-in: 07:20
clock-out: 14:00
---

filename : dic_20220705_clock

---
Date: 2022-07-05
clock-in: 07:20
clock-out: 14:00
---

filename : dic_20220709_weekend

---
Date: 2022-07-09
clock-in: 
clock-out: 
---

2. DQL10_debgug_Small_fixes_of_scholarInTraining

Summary

2.1. Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL10_debgug_Small_fixes _of_scholarInTraining	a date format string, or a list	yes	1.To debgug: `date("2022-01-01T" + rows.clock-out)` step by step 2.To sum hours for the week of groupBy data	A bug ocurrs.

2.2. Inline DQL

a_Result11==map([10, 20, 30], (e) => e + 6)
//=>16, 26, 36

a_Result12==map(["A", "B", "C"], (e) => e + "P")
//=>AP, BP, CP

2.2.1. OK

s_an_ISO_string=="2022-01-01T" + "13:25"//=>2022-01-01T13:25
a_an_ISO_string==map(["13:25","14:10"], (e) => "2022-01-01T"+ e)
//=>2022-01-01T13:25, 2022-01-01T14:10

2.2.2. the bug: NG

string + array = string , but not an ISO date string

s_Not_an_ISO_string=="2022-01-01T" + ["13:25","14:10"]//=>2022-01-01T13:25, 14:10

2.3. Notes

2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]

2.4. Code DQL10_debgug_Small_fixes_of_scholarInTraining

Summary_code

title: DQL10_debgug_Small_fixes_of_scholarInTraining =>1.To debgug: `date("2022-01-01T" + rows.clock-out)` step by step 2.To sum hours for the week of groupBy data
collapse: close
icon: 
color: 

```dataview
TABLE WITHOUT ID
      calendarweek AS "Group",
	  sum((date("2022-01-01T" + rows.clock-out) - date("2022-01-01T" + rows.clock-in)).hours) AS "work hours",

      rows.clock-out as "S1_rows.clock-out",
        "2022-01-01T" + rows.clock-out AS "S2_ISO_string",
      date("2022-01-01T" + rows.clock-out) AS "S3_date_S2",
      typeof(date("2022-01-01T" + rows.clock-out)) AS "S4_type_S3"        
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
GROUP BY dateformat(file.day, "WW | yyyy") AS "calendarweek"
SORT DESC
```

2.3.1. Screenshots(DQL10)

3. DQL20_FLATTEN_calendarweek

Summary

3.1. Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL20_FLATTEN_calendarweek	a date format string, or a list	yes	1.To sum hours for the calendarweek 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field

3.2. Notes

2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]
2022-08-02 add WHERE F_clock_in AND F_clock_out

3.3. Code DQL20_FLATTEN_calendarweek

Summary_code

title: DQL20_FLATTEN_calendarweek => 1.To sum hours for the calendarweek 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "File",
      F_weekday AS "weekday",
      F_calendarweek AS "calendarweek",
      clock-in AS "clockin",
      clock-out AS "clockout",     
      F_workhours  AS "workhours"
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
SORT file.name desc
FLATTEN file.name AS F_name
FLATTEN dateformat(file.day, "cccc") AS F_weekday
FLATTEN dateformat(file.day, "WW | yyyy") AS F_calendarweek
FLATTEN date("2022-01-01T" + row["clock-in"]) AS F_clock_in
FLATTEN date("2022-01-01T" + row["clock-out"]) AS F_clock_out
FLATTEN F_clock_out - F_clock_in AS F_workhours
WHERE F_clock_in AND F_clock_out
```

3.2.1. Screenshots(DQL20)

4. DQL30_FLATTEN_groupBY_calendarweek

Summary

4.1. Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL30_FLATTEN_groupBY _calendarweek	a date format string, or a list	yes	1.To sum hours for the week 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field 4.Not to use `map` to dael with a list	1.To use `map` such as Craig : To calculate field (workhours) by using `map` to iterate over each element of lists is a basic method.

4.2. Notes

2022-07-26 add WHERE row["clock-in"] AND row["clock-out"]
2022-08-02 add WHERE F_clock_in AND F_clock_out

4.3. Code DQL30_FLATTEN_groupBY_calendarweek

Summary_code

title: DQL30_FLATTEN_groupBY_calendarweek =>1.To sum hours for the week of groupBy data 2.To use `FLATTEN` to transform lists into non-lists 3.To use `FLATTEN` to create an alias for a new field 4.Not to use `map` to dael with a list
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      GF_calendarweek AS "Group",
      sum(rows.F_workhours) AS "workhours"
FROM "100_Project/02_dataview/Q09_Sum_groupBy/Q09_test_data"
WHERE file.day
WHERE row["clock-in"] AND row["clock-out"]
FLATTEN file.name AS F_name
FLATTEN dateformat(file.day, "WW | yyyy") AS F_calendarweek
FLATTEN date("2022-01-01T" + row["clock-in"]) AS F_clock_in
FLATTEN date("2022-01-01T" + row["clock-out"]) AS F_clock_out
FLATTEN F_clock_out - F_clock_in AS F_workhours
WHERE F_clock_in AND F_clock_out
GROUP BY F_calendarweek AS GF_calendarweek
SORT GF_calendarweek DESC

```

4.2.1. Screenshots(DQL30)

scholarInTraining · July 24, 2022, 3:29pm

Thank you very much @justdoitcc !! That was very educational for me.

Craig · July 24, 2022, 10:21pm

Hmm, no, not off the top of my head. If you’d like to investigate further, you could post your data files and your query, and I could try to troubleshoot the error.

Craig · July 24, 2022, 10:23pm

That’s clever! I like the way you used FLATTEN to create calculated fields.

dev0zzz · July 25, 2022, 1:39pm

wow thank you all, that is brilliant!
I got the list right now the way I wanted it!

really great!!!

justdoitcc · July 26, 2022, 7:52am

You could post your data files as a zip file, and I could try to troubleshoot the error.

404r · July 26, 2022, 8:52am

I found the reason. It’s weekends. If you don’t work on weekends, there is no clock-in and clock-out.

justdoitcc · July 26, 2022, 9:27am

Thank you! It is taken into consideration.

404r · July 26, 2022, 9:56am

Yes, with filtering added it’s fine

system · August 2, 2022, 9:56am

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