Create progress bars by counting number of YAML fields

Hello guys. Thank you for checking out my thread. I appreciate it.

I’m trying to track different project progress by calculating the no. of status “pending” vs. no. of status “watched” of a particular folder.

Example of YAML with status watched:

---
title: 2. SOLID Design Principles
due: 
status: watched
---

Example of YAML with status pending:

---
title: 3. Builder
created: 2022-04-12
due: 
status: pending
---

With the following databiew query:

TABLE length(rows) as Tag
FROM "Courses/Design Patterns in Java" 
WHERE contains(status, "pending") OR contains(status, "watched")
GROUP BY status

This gives the following table:

I could get the number of status field I want. But I don’t know how to divide no. of “watched” by no. of “pending” ((no. of watched) / (no. of pending)). In the above case, 1/25.

Here’s what I want to achieve in pseudo-code:

TABLE 
count(number("pending")) as Pending,
count(number("watched")) as Watched,
(round((Watched/Pending)*100) + "%") AS Progress
FROM "Courses" 
GROUP BY different folders under "Courses"

Expected result:

screenshot-2022-04-18-20-55-27

Below is the directory tree of “Courses”:

.
├── Courses
│   ├── Design Patterns in Java
│   │   ├── 00. MOC Design Patterns in Java.md
│   │   ├── 01. Introduction.md
│   │   ├── 02. SOLID Design Principles.md
│   ├── Understanding TypeScript
│   │   ├── 00. MOC Understanding TypeScript.md
│   │   ├── 01. Getting Started.md
│   │   ├── 02. TypeScript Basics & Basic Types.md
│   │   ├── 03. The TypeScript Compiler (and its Configuration).md
│   └── Understand JavaScript
│       ├── 00. MOC Understand JavaScript.md
│       ├── 01. Introduction and The Goal of This Course.md
│       ├── 02. Setup.md
│       ├── 03. Big Words and Javascript.md

Any help is much appreciated. Thank you again!

Hi,

I’m not sure if it works with your folder structure, but try this:

TABLE WITHOUT ID
	split(C, "/")[length(split(C, "/")) - 1] AS Course,
	round(length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100) + "%" AS Percentage,
	"<progress value='" + length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100 + "' max='100'></progress>" + "<br>" + round(length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100) + "%" AS "Progress Bar"
FROM "Courses"
WHERE contains(status, "pending") OR contains(status, "watched")
GROUP BY file.folder as C

The main question about the folders is that file.folder gives the path of the folder (not only the folder name).
If you know regex (not my case) you can use regexreplace() to transform the full path in only the folder name (the last part of the path).
Because I don’t know regex, I use in the query above the function split() in a weird way… but I think it works.

1 Like

Hello mnvwvnm! Thank you! Your code works flawlessly and it’s exactly what I wanted. Just want to thank you again. Whenever I look into dataview related stuff I always learn from your replies.

If anyone is interested, here’s the result:

To pay it forward, here’s my breakdown of your code (correct me if I’m wrong).

TABLE WITHOUT ID
	split(C, "/")[length(split(C, "/")) - 1] AS Course,
	round(length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100) + "%" AS Percentage,
	"<progress value='" + length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100 + "' max='100'></progress>" + "<br>" + round(length(filter(rows.status, (s) => s = "watched")) / length(rows) * 100) + "%" AS "Progress Bar"
FROM "Courses"
WHERE contains(status, "pending") OR contains(status, "watched")
GROUP BY file.folder as C

TABLE WITHOUT ID gets rid of the default “File” or “Group” field in the output.
split(string, delimiter, [limit]) splits the folders into entries (i.e., Course)
rows is the “rows array field with all of the pages that matched”. We only need the rows.status field to filter against the “watched” field.
<progress /> is the tag to display the progress bar

I really need to study the doc more closely. Thanks again!

1 Like

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