I kind of triggered myself with these sum across the board, so here is a rather intense query doing three different sums in two tables. Before I present the query, and an output example, lets re-iterate on the base for the query, and which totals we’re going to build.
Base query requirements (with three different sum variants)
- The base data is a standard DQL query holding the columns:
project,date,tasks,hours. In all the sums below, we’re aiming to sum the hours in various ways, and the table is sorted (and in this particular case grouped) on the combination of the two first columns - We’re going to calculate the grand total of hours presented in the table. This is displayed at the very bottom of the table. Code related to this is marked with
**1**. This sum is done using a.reduce()function on all values, where we start with picking out the column to sum on - We’re going to calculate a running total of hours for each project, where we sum the hours for the current project (until the project changes). Code related to this is marked with
**2**, and the running group total is added below the lines for that project. This calculation is done by adding the hours as we tag along, and reset whenever the project key changes. - Lastly we’re going to calculate the hours for any given day, aka a date total, and in this case the values are scattered around in the table, but we want to sum up the hours related to unique values in the second column. Code related to this is marked with
**3**. Presentation of these sums is done in a separate table after the main table.
The output
An example output using the query below on some random test data:
Notice how I’ve used slighty different formatting for the various running and grand totals within the table of the main data, and a separate table for the date totals.
The query
## Multiple sums
```dataviewjs
const result = await dv.query(`
TABLE WITHOUT ID
rows.item.project[0] as "Project",
rows.file.link[0] as "Date",
rows.cleanText as Tasks,
sum(rows.item.hours) as Hours
FROM "ForumStuff/f71/f71232"
FLATTEN file.lists as item
FLATTEN regexreplace(item.text,
"[\\[\\(][^\\[\\)]*[\\]\\)]", "") as cleanText
WHERE item.project
GROUP BY item.project + " " + file.link as "Project code and date"
`)
if ( result.successful && result.value.values.length > 0) {
const values = result.value.values
console.log(values)
// **1** variable and calculation
let grandTotal = values
.map(v => v[3])
.reduce((a, c) => a + c, 0.0)
// **2** variables
let runningKey = null
let runningTotal = 0
let runningArray = []
let dateTotals = {} // **3** variable
// The main loop over all elements, remember
// to replace the variable names according to your
// column header
values.forEach( ([project, date, task, hour]) => {
// **2** Keep tally on the running total,
// and add a group total line if the key changes
if ( runningKey == null || runningKey != project ) {
if (runningKey != null) {
runningArray.push([
"",
"",
`_<span style="float: right">Total for ${ runningKey }</span>_`,
runningTotal
])
}
runningKey = project
runningTotal = hour
} else
runningTotal += hour
runningArray.push([ project, date, task, hour ])
// **3** Tally up the day totals
if ( date in dateTotals ) {
dateTotals[date] += hour
} else {
dateTotals[date] = hour
}
}) // End of all value loop
// **2** Add the running total for the last group
runningArray.push([
"",
"",
`_<span style="float: right">Total for ${ runningKey }</span>_`,
runningTotal
])
// **1** Add grand total to the array
runningArray.push([
"",
"",
`**_<span style="float: right">Overall total</span>_**`,
grandTotal
])
// **1** and **2** output
dv.table(result.value.headers, runningArray)
// **3** output
dv.table(["Date", "Hours"], Object.entries(dateTotals))
} else
dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```
Some key comments related to the query:
- The actual query,
**0**, can (and should) be tested standalone, so one is sure that the data is sound. This query can however be replaced with any query of your own, as long as you keep track of which columns you are using, and update the various totals you want to match the column numbers. Also remember that the first column is column 0. - When doing the grand total,
**1**, we first do a.map(a => a[3])to select the column used for this total, and then we do areduce( (a, c) => a + c, 0.0 ). This reduce operation loops through all items, and usescfor the current item, and updatesafor the accumulated value. The last part of0.0sets the initial value - When doing running totals,
**2**, a prerequisite is that the table is sorted related to its key. We then detect when the key changes, and resets the intermediate sums and so on. Two tricks related to this is that we need to get it going in the start using a test againstnull, and since we add new rows to the table every time the key changes, it’s better to use a temporary table to hold the new variant. And after the last line in the table, we need to add that last group as well (think of it as when we change the key to nothing… ) - Finally the date total,
**3**, which could be on any arbitrary column or value, the order is not important, since we use the value as a key into a table holding the accumulated sum. If the key is present then add current value, and if the key is not present add a new key with the current value. Since these values are scattered across the rows, it’s natural to present the result as a table of its own. - A final note on pushing in new rows into the
runningArray(or optionally appended to the originalresult.value.valuesarray), is that one needs to push an array with as many elements as there are columns in the original table. So you still need to add something into each column, even if only an empty string,"".
So there you have a specific query showing a generic way of adding various sums related to a DQL query of your choice. Hopefully, one can extrapolate how to repeat this process using other queries and/or sums.