Tracking time spent on different categories based on Full Calendar

What I’m trying to do

I use Full Calendar to track my time. To distinguish between categories, I use different folders as well as abbreviations in the titles. In my weekly notes, I have a Dataview overview of how much time was spent on different things, but I would also like to have a single overview where I can compare weeks. That means that the query has to distinguish between information from different weeks. I’ve succeeded in creating a table that has the necessary information (the category, the week and the duration), but now I need to have it sum the data for each week and category. I’ve tried a lot of things, but I’m not getting any further.

Things I have tried

This is the table I have at the moment. The pages in this table are for different subcategories within one folder.

And the code:

const work_amounts = await dv.query(`
TABLE WITHOUT ID  
	dateformat(date, "WW") AS Week,
	split(title, " ", 1) AS Category,
	sum(number(dur(
		date(dateformat(date, "yyyy-MM-dd") + "T" + endTime) - date(dateformat(date, "yyyy-MM-dd") + "T" + startTime)).hours)) AS Duration
FROM "Calendar/Work" 
WHERE contains(this.file.name, string(date.year))
`) 

const work_sum = work_amounts.value.values 
.map(a => a[2]) 
.reduce((tmp, curr) => tmp + curr, 0)

work_amounts.value.values.push(["", "", "Total: " + work_sum])  
dv.table(work_amounts.value.headers, work_amounts.value.values) 

(To generate this table, only using the dataview part would work as well).

I also tried to see if I could use the query results in the individual week pages to create metadata that could then be queried in the overview, but it seems like there’s no way to do it. This is an example of what I have in my weekly page:
image
(Uses the folder Calendar/Sleep to find at the average amount of sleep I got each night)
I tried using dv.paragraph(“[SleepAvProp::” +sleep_average +“]”) to generate a property, but that didn’t work. It ends up looking like an inline property:
image
But a query doesn’t pick it up.
So for now I just manually copied the result of the query each week as [wwHoursAvSleep:: {the query result for that week}]

And then I did manage to use that to create an overview in the overview page:


Code:

const pages = dv.pages('"Personal Notebooks/Weekly notes"', ) 
const testNames = pages.map(p => p.file.name).values 
const testMarks = pages.map(p => p.wwHoursAvSleep).values 

const chartData = { 
	type: 'bar', 
	data: { 
		labels: testNames, 
		datasets: [{ 
			label: 'Sleep', 
			data: testMarks, 
			backgroundColor: [ 'rgba(144, 190, 245, 0.2)' ], 
			borderColor: [ 'rgba(144, 190, 2452, 0.8)' ], 
			borderWidth: 1, 
		}] 
	} 
} 			
window.renderChart(chartData, this.container) 

Ideally I would like to combine these two things, so that I can make charts that show information about time spent on different things each week.

I hope this makes sense. I’ve been puzzling over this for ages and I’m pretty much stuck now. Some help would be much appreciated!

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