Table Listing number of tasks

[psarras]

I am trying to create a table. Each row will be a day of the week, and each column will have different metrics about this week. Most of the things I want to track are task related. i.e. how many tasks related to exercise I completed, how many tasks in total. etc...

Let's say I am using the [completed:: 2022-04-28] to signify that they are done on that day (even though the task itself might be saved on a non diary file). How should I do my query on the table? I am not sure this is possible even...My thought was to expand on an idea posted here in the past. The snippet below assumes on each day you have a link to the corresponding week so you use the "where" filtering to only focus on those 7 days. However, I would be interested to do more filters on the individual columns.

table WITHOUT ID
	file.link as Date, 
	join(list(sleep-hour, sleep-minute), ":") as "Sleep Duration",
	choice(physical-activity, "✅", "❌") as "Physical Activity"
WHERE week = this.file.link
sort file.name asc

The lines below obviously are not working, but maybe they show what I mean to do. So in this case I am getting all the tasks that are completed and are not including the

table WITHOUT ID
	file.link as Date,
	length(tasks
		where completed and !contain(text, "#exercise") 
                and contain(completed, date.link) as "Tasks ✅"

WHERE week = this.file.link
sort file.name asc

How it might look like

Also, it would be cool if there is a way to easily get some of the autocomplete there :P

[AB1908]

Oooh subqueries eh? I don't think blacksmithgu would implement this in the near future (maybe he will, who knows? 🤷 ).

For now, you can nest function calls but it would be a little messy. However, before we get into that, could you give us an example of how you've set your tasks up so we can get an idea of the type of index you have?

[blacksmithgu]

I haven't implemented subqueries yet, which would make this doable in Dataview; it can be achieved in DataviewJS via some manual for loops to collect the information.

[psarras]

Hey, that sounds great. I am trying to do that with dataviewJs at the moment although not super familiar with JS. It looks a bit simpler to debug, maybe.

My thought was to collect all the tasks completed within my period and then group them by date. So I have a bucket per day. Do the same for other topics irrelevant to tasks but again group them by day. Then I can loop through all the buckets by day and append them to a table.

Are there any resources besides the docs to help me do that?

[AB1908]

Honestly, the best way is to use the console.

[psarras]

hmm I'm kinda close to it but not exactly

I have something like that

I am wondering if there is a way to "join" the different groups together (simple)? Or maybe even a smarter way to decide on the dates at the start and then do all the groups as one object?

Happy to hear your thoughts, I am sure I am making lots of newb mistakes :)

const start = "2022-04-25"
const end = "2022-05-01"
const limit = 3

const startDate = new Date(start)
const endDate = new Date(end)

console.log("Period", start, end)

let date_pages = dv.pages().where(x => 
   {
   if(x.created)
   {
   	//console.log(x)
   	const d = new Date(x.created.path)
   	return x.created && d >= startDate && d <= endDate
   }
   return false
   }
)
console.log("Pages after 1st of April " + date_pages.length)

let pages = date_pages.where(x => {
   //console.log(x)
   return x.file.tasks && x.file.tasks.length > 0
})

let pages_with_meetings = date_pages.where(x => x.category.contains("meeting"))
const group_meetings = pages_with_meetings.groupBy(x => x.created)
console.log("pages_with_meetings: ", pages_with_meetings.length)

let pages_notes_plus = date_pages.where(x => !x.category.contains("meeting"))
const group_notes_plus = pages_notes_plus.groupBy(x => x.created)
console.log("pages_notes_plus: ", pages_notes_plus.length)


console.log("Total pages with tasks: " + pages.length)

console.log(pages.file.tasks)

const allTasks = pages.file.tasks.where(x => { 
   //console.log(x)
   return x.completed
})

const page_task_pair = pages.where(x => x.file.tasks.completed).map(x => [x, x.file.tasks.where(t => t.completed)])

let pairs = []
for(let pair of page_task_pair)
{
   //console.log(pair)
   for(let task of pair[1])
   {
   	pairs.push([task, pair[0]])
   }
}

pairs = dv.array(pairs)

console.log("All Pairs: ", pairs, pairs.length)

console.log("All completed tasks: " + allTasks.length)
const groups = pairs.groupBy(x => {
   const done = (x[0].done) ? dv.fileLink(x[0].done.toFormat("yyyy-MM-dd")) : x[0].done
   const created = x[1].created
   //const pa = dv.pages(x.link)
   //console.log(x, "doneOn: ", doneOn, "created: ", created, dv.date(created).toFormat("yyyy-MM-DD"))
   const group = ((done) ? done : created)
   //const group = x.doneOn
   return group
})
console.log(groups)

dv.table(["Date", "Completed ✅", "Goal Achieved"], groups.map(k => [k.key, k.rows.length, k.rows.length > limit ? "✅" : "❌"]))

dv.table(["Date", "Meetings"], group_meetings.map(k => [k.key, k.rows.length]))

dv.table(["Date", "Notes"], group_notes_plus.map(k => [k.key, k.rows.length]))

[AB1908]

You'll have to define what you mean by "join". Can you show us some sample output?

[psarras]

Like the one on the question! :) One day per row and then the stats per column. Now each group might have different number of days as I might not have take a meeting/not on that day.

[psarras]

Ok I think I did it using dataviewjs which is more flexible.

this is assuming you have tasks like that

- [ ] this is a new task [done::2022-05-04]

or

[created::[[2022-05-04]]]

- [ ] this is a new task

Also files with the category::"meeting" are getting counted

const start = "2022-04-25"
const end = "2022-05-01"
const startDate = new Date(start)
const endDate = new Date(end)

let date_pages = dv.pages().where(x => x.created).where(x => {
		const d = new Date(x.created.path)
		return x.created && d >= startDate && d <= endDate
})

const all_completed_tasks = dv.pages().where(x => x.created)
    .where(x => x.file.tasks.completed.length > 0).map(x => [x, x.file.tasks.where(t => t.completed)])
console.log("all_completed_tasks", all_completed_tasks)

let all_completed_tasks_pairs = []
for(let pair of all_completed_tasks)
{
	//console.log(pair)
	for(let task of pair[1])
	{
		all_completed_tasks_pairs.push([task, pair[0]])
	}
}

function get_date(pair_array) {
	const c = pair_array[1].created
	const d = pair_array[1].date
	const dt = pair_array[0].done
    const created = (c) ? c : d
	const done = (dt) ? dv.fileLink(dt.toFormat("yyyy-MM-dd")) : dt
	const date = (done) ? done : created
	return date
}

all_completed_tasks_pairs = dv.array(all_completed_tasks_pairs).map(x => [x[0], x[1], get_date(x)]).where(x => {
	const d = new Date(x[2].path)
	return d >= startDate && d <= endDate
})

const group_pages_all = date_pages.groupBy(x => x.created)
dv.table(["Date", "🗒 Notes", "🤝 Meetings", "New Tasks", "Tasks ✅"], group_pages_all.map(g =>
		{
			//console.log(g)
			const num_new_tasks = g.rows.where(x => x.file.tasks.length > 0).length
			const tasks_completed = all_completed_tasks_pairs.where(x => 
			{
				const date = x[2]
				if (!date)
					return false
				return date.path == g.key.path
			}).length
			
			//console.log("all tasks", tasks_completed)
			const num_meetings = g.rows.where(x => x.category == "meeting").length
			const num_notes = g.rows.where(x => x.category != "meeting").length
			return [g.key, num_notes, num_meetings, num_new_tasks, tasks_completed]
		}))