Cumulative file count in dataview table

I have the following dataview table (table 1) that shows me the total amount of files (that are inside the “Library” folder and contain the tag #Article or #Book or #Video) created by month, in 2023:

TABLE WITHOUT ID 
dateformat(rows[0].file.cday, "MMM-yy") as "Month", 
length(rows) as "Files"
FROM "Library"
FLATTEN file.tags as dc 
WHERE contains(dc, "Article") OR contains(dc, "Book") OR contains(dc, "Video") AND file.ctime.year = 2023
GROUP BY dateformat(file.cday, "yyyy-MM")
SORT key

What I’m trying to do

I’m trying to add another table (table 2) that shows me almost the same thing, but cumulative (count total files of current month + previous month(s)). For example:

  • Table 1
Month Files
Jan-23 2
Feb-23 4
Mar-23 1
Apr-23 5
  • Table 2
Month Files
Jan-23 2
Feb-23 6
Mar-23 7
Apr-23 12

Any solution?

1 Like

I have the same problem :sweat_smile:

I don’t think it’s possible using pure DQL, but if you switch to dataviewjs utilising your original DQL query like in the example below, it’s doable.

```dataviewjs

const result = await dv.query(`
  TABLE WITHOUT ID key, length(rows) as "File count", length(rows) as "Cum. file count"
  WHERE startswith(file.folder, "ForumStuff/f55") 
  GROUP BY file.folder
`)

if (result.successful) {
  let prev = 0
  
  dv.array(result.value.values)
    .mutate(v => {
      v[2] = prev + v[2]
      prev = v[2]
    })
    
  dv.table(result.value.headers, result.value.values)
} else
  dv.paragraph(`~~~~\n${ result.error }\n~~~~\n`)
```

In this example I’m doing a simple count of files in my test vault, where the base queries is showing the folder, count of files (normal), and count of files (accumulated). Since arrays in javascript starts the index at 0 for the first column, the third column becomes v[2].

So this example should be usable where you just replace the query near the top with your own query, and adjust the references to v[2] to match your cumulative column number minus one.

The top of the result in my test case:

(Note that the second column, the normal file count, isn’t needed for the cumulation to occur, I just wanted it in the test case to show case that the addition was correct)

1 Like

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