Dataview query to show tasks in a table (tasks across columns, days of the week as the rows)

Things I have tried

I realize this may be a simple query, but I’m just not figuring out how to put it together. Relatively new here.

I’ve watched several videos and attempted putting together some queries that I’ve copied from others and modified but I’m not really understanding some of the more advanced stuff.

What I’m trying to do

My goal is a dataview that’s essentially a habit tracker for my weekly review, that has the days of the week as the rows and the tasks (completed or not) displayed in the columns. I’d like to quickly see in a table how I did over the last week.

There’s a trick to this though (I think). The tasks are in a daily note that is shared with my wife, so there are tasks that belong to her there also.

Any help or guidance is appreciated!

I suggest you to add more info:

  • an example of your tasks;
  • what’s the format of your daily note (because you need to define where you’ve a valid date for each note);
  • a mockup of the wanted output;
  • some examples of your queries attempts (remember, more than videos, you need to take some time with the plugin documentation).

Tasks in tables are more easy if you only care about the text, i.e., if you don’t want to interact with them via checkbox, only the text.

By default tasks are interactive items only in tasks queries. It’s possible to configure some tricky ways to place “real” tasks in table, but isn’t the best thing to start.

Thanks for chiming in!

I can add some more info for sure. So I’m technically using 2 daily notes. 1 of them is shared with my wife, (file name = “PIGGS-MM-DD-YY”). This is where my daily habits/ goals actually live and they are formatted like so:

  • [ ] Task 1
  • [ ] Task 2
  • [ ] Task 3

(this is a mockup of what I’m wanting to accomplish)

Just displaying the tasks is perfect. No need to interact with them at the point of the weekly review. I’m primarily getting hung up on the syntax to view the status of a specific task across the last 7 notes. This is the closest I’ve got so far:

TABLE without ID
	file.link AS Weekday,
	file.tasks.text = "🏋️ Movement" AS 🏋️
FROM 
	"Spaces/Shared Notes/PIGGS/2022/12-2022" AND -"Extras"
WHERE
	file.cday <= dur(default(period,"-7d"))

Hmmm… some notes:

  • valid date - You don’t have a reliable date field (implicit or created). The creation date isn’t the most trusty data to work with. For many reasons: because if you have a system/computer/sync issue you’re done; because sometimes you can create the daily note not properly in the correct day; etc.
    You can define a reliable date in two ways: creating a field in your note with the exact day in “YYYY-MM-DD” format; or using a “YYYY-MM-DD” format in the daily note title (example: PIGGS-2022-12-23).
  • tasks - The tasks text is a regular thing? I asked because I don’t know if you use some inline field or tag or whatever to select the wanted type of tasks in each column. Or, if the filter is related with the exact expression (the task text) repeated in all your daily notes.
  • mockup - Instead of the task text you want an emoji to completed or uncompleted tasks.
  • your query
    • file.link AS Weekday - your file name don’t have the weekday
    • file.tasks.text = "🏋️ Movement" AS 🏋️ - you can’t filter tasks in this way
    • file.cday <= dur(default(period,"-7d")) - you have a period field?

To some extent, we are in the same startpoint: the information is insufficient and I don’t know if you’re aware of the metadata you need to use to get the wanted results.

The tasks are the same in each note that I create every day. They are tagged, and are always under the same heading.

For the file.link as Weekday… I knew it wasn’t showing that quite yet. I’m pretty confident I can work that out later.

For the file.cday, I don’t have a period field in metadata if that’s what you’re asking. I’m still figuring out how to work out showing the previous 7 days only. (I can probably figure this one out through more doc reading and trial and error though).

For filtering tasks in dataviews, that’s where I think I’m the most confused. I’ll keep plugging away at it. Thank you for the insight, and if you have any other advice, I’m all ears!

I’m not quite sure if DQL’s are up for this kind of task. I mean it’s easy(?) enough to get a row entry for a given date, and all the task for that date. The difficulty is spreading those tasks out across the columns.

Given your tags, it could possibly be done by flattening the task list multiple times into several fields ( But can you flatten it multiple times?)

I’m more of a coder, so I do see a possibility doing a DataviewJS query (why don’t we have a nice shortcut for that, like for the DQL’s?! :cry: ), where you do the same base query, but then map each of the tasks either by task text or tag into the correct column.

Anyways, that was just my thoughts on this process late in the night. Good luck with your endeavours!

I’ll leave one example here based in one point: adding a date field in each note.

example note

An example note (with an inline field to fix a date, but you can use a field in frontmatter):


dDate:: 2022-12-24

## Denni's Goals

- [x] 🐷 Create PIGGS
- [ ] 🏋️ Movement
- [ ] 📖 Bible App
- [ ] 💰 EveryDollar
- [ ] 🛏 Make the bed
- [ ] 💡 Learn a new thing
- [x] 🎸 Guitar - 10 minutes

the query

Now a query based in these conditions:

  • as I see in your mockup, to you the start of the week is in sunday… so, for the case the weekly query is placed in a note with a Saturday date. why? to use a comparison and calculation based on the date in that field
  • because you use in the section title an ' (“Denni’s Goals”) you need to ignore it in WHERE meta(T.section).subpath = "Dennis Goals"
  • the query is based in a fixed list of ordered seven tasks
dDate:: 2022-12-24

```dataview
TABLE WITHOUT ID
	upper(dateformat(dDate, "cccc")) AS Weekday,
	choice(rows.T.completed[0], "🟢", "❌") AS 🐷,
	choice(rows.T.completed[1], "🟢", "❌") AS 🏋️,
	choice(rows.T.completed[2], "🟢", "❌") AS 📖,
	choice(rows.T.completed[3], "🟢", "❌") AS 💰,
	choice(rows.T.completed[4], "🟢", "❌") AS 🛏,
	choice(rows.T.completed[5], "🟢", "❌") AS 💡,
	choice(rows.T.completed[6], "🟢", "❌") AS 🎸
FROM "your-folder-path"
FLATTEN file.tasks as T
WHERE meta(T.section).subpath = "Dennis Goals"
WHERE dDate <= this.dDate AND dDate >= this.dDate - dur(6days)
GROUP BY dDate
SORT dDate ASC
```

Isn’t an elegant query (with undesirable repetitions), but is one way inside dql limitations.

the result

(weekdays are in portuguese, my local language)

3 Likes

This is the part I’m a little hesitant to enforce. If OP at some point either add a task before the other tasks, or change the task order, it’ll be a mismatch on the overview.

If that’s something the OP can live with, I like your solution, but I would have preferred a more robust solution in general (which is why I wrote my post on its not being an easy mapping from task to column).

1 Like

Oh I see. Okay that would do the trick… but as you both mentioned, there will be limitations as my vault and aspirations grow. I guess it’s time to learn a bit of js :grimacing:. It’ll help me anyways. Your solution will work for me for now @mnvwvnm. Thank you!

You’re absolutely right! That’s why I pointed that.

Maybe this isn’t the best “technical” way to say it, but in DQL we are forced to write the columns expressions, one by one… but I guess in JS you can write a function, a variable, … to produce the columns declarations in a dynamic way. I.e, you’re not forced to write all the columns expressions in individual way.

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