Hello, I am trying to use Obsidian and Dataview to track my working hours by day and project. I am doing this by entering chunks of work into my daily notes like this:
e.g., in file “2022-11-22”
work:: meeting, 08:00, 09:30
work:: project1, 09:30, 12:15
work:: project1, 12:45, 14:15
work:: meeting, 14:15, 17:30
e.g., in file “2022-11-21”
work:: project2, 08:15, 12:15
work:: meeting, 13:00, 16:00
Tracking the hours themselves and summing them by project has been working great, but I cannot seem to figure out how to calculate a running total of how much overtime I have racked up.
The following table works to calculate overtime per day, assuming an 8h workday:
TABLE WITHOUT ID Date, round(sum(rows.Hours), 2) AS "Total Hours", round(sum(rows.Hours) - 8, 2) AS "Overtime" FROM "_Daily notes" WHERE work FLATTEN work AS W FLATTEN file.name AS Date FLATTEN split(W, "\, ") AS Project FLATTEN split(W, "\, ") AS Start FLATTEN split(W, "\, ") AS End FLATTEN date(Date + "T" + End) AS Tend FLATTEN date(Date + "T" + Start) AS Tstart FLATTEN round((Tend - Tstart).hours, 2) AS Hours GROUP BY Date
How do I go about summing up this “Overtime” column to get a total count?
Things I have tried
I searched the forums and found several questions asking about how to sum columns, but all the questions I saw asked about summing raw values. The solution in those cases was to construct a query that simply sums all raw values. In my case, that doesn’t work because I first have to perform an operation (subtract 8h) on already-grouped values (entries grouped by day), so I cannot just subtract 8h from each entry.
I have tried summing up all “work” entries across all daily notes and then subtracting [# days]*8 from that count. The problem is that I don’t know how to obtain the number of unique days. If I just take length(rows), it’s giving me the total number of entries, but that number is of course way too large since I usually have a bunch of entries per day. Is there some equivalent of
I also tried taking the table above that’s grouped by day and adding a second GROUP BY statement (e.g., grouping by Overtime, by null, or by an arbitrary index). This gave me the error message
Dataview: Every row during final data extraction failed with an error; first 3: No implementation found for 'null - number'
Alternatively, I have considered whether I could use the table above (overtime per day) as source input for a second table that sums across rows (yielding total overtime), but I also could not figure out how to do this yet.
What I’m trying to do
I am trying to group entries by date, then perform an operation on the daily values, and then sum the results across all dates. I would appreciate any hints!