I have a markdown note file named “DHG1” that contains a task list, organized by products. The tasks also have numbers associated with them. Here’s what the content looks like:
- [[fast]] used [n::4] 2023-10-24
- [[fast]] used [n::1] 2023-10-30
- [[slow]] used [n::4] 2023-10-24
- [[slow]] used [n::1] 2023-10-30
I’ve created a DataView with the following code, which shows individual entries:
TABLE WITHOUT ID
file.link AS "Files", regexreplace(Tasks.text, "^(\S+).*", "$1") AS Products, Tasks.n AS Number, Tasks.scheduled AS "Scheduled Date"
FLATTEN file.tasks AS Tasks
SORT file.link ASC, Tasks.text ASC
This DataView produces an output as shown in the attached screenshot.
However, I’m looking for a way to modify the DataView to show the sum of the numbers grouped by the “Products” and “Files” columns. Specifically, I’d like to see an output that looks like this:
Additionally, if I have multiple files, I’d like to know the sum grouped by each file as well.
Could anyone please assist me in achieving this?
The first output can be achieved by doing
sum() after grouping on a combination of
product, and that query would look something like this:
TABLE WITHOUT ID
key as Files,
key as Product,
sum(rows.task.n) as "Total number"
FLATTEN file.tasks as task
FLATTEN regexreplace(task.text, "^(\S+).*", "$1") as product
WHERE file.folder = this.file.folder
GROUP BY list(file.link, product) as "Files"
In the query above you would need to replace the
WHERE file.folder ... with something suiting your case, or remove that line altogether and replace it with a
FROM statement to be inserted before the
In my test setup this query returned:
I’m not quite sure what you would like for the “sum grouped by each file” as well. Using plain DQL query, like this one, you can’t easily do multiple sums across the other grouping. Depending on what you want, you could possibly do it using dataviewjs in addition to a query like the one above, but what should that table look like. Should it list the file with the sum of “fast” and “slow”, or should it list the products with the sum from all the files?
If it were to be done within one query, it would look a little hairy, and in addition it would most likely repeat that final sum in all rows (similar to how the file name is repeated above) as dataview isn’t really good at combining cells within a table.