What I’m trying to do
I am attempting to create a row that totals the values in a dataview column. In the below query, I was hoping to create a total of the “Yearly Price” column.
TABLE SubType as "Type", StartDate as "Started", ExpirationDate as "Ends", Usage, Renews, Price, ("$" + (round(Renews*Price,2))) as "Yearly Price"
FROM "2 Areas/Finance/Subscriptions"
Things I have tried
I have found a few similar questions in the forum and tried to recreate it for my query with DataviewJS, but nothing I’ve tried pans out.
I actually figured out most of the way, but there are still some kinks that are beyond my understanding of DataviewJS at the moment. Here’s my code that is outputting the info I want it to so far. The minor issues are that the sum comes out with tons of numbers after the decimal point. Secondly, the total is placed under the wrong column (I’d ideally like it to appear under the 7th “Yearly Spending” column, but it appears under the second column.
As for the decimal points, I tried working (round()) into the code in some capacity, but it just kepy breaking. Any insight here would be appreciated!
const amounts = await dv.query(`
TABLE WITHOUT ID
file.link as "Sunbscription", SubType as "Type", StartDate as "Started", ExpirationDate as "Ends", Usage, Renews, Price, (round(Renews*Price,2)) as "Yearly Price"
WHERE file.folder = this.file.folder
WHERE number(round(Renews*Price,2))
`)
if ( amounts.successful ) {
const sum = amounts.value.values
.map(a => a[7])
.reduce((tmp, curr) => tmp + curr, 0)
amounts.value.values.push(["<span style='float: right'><strong>Yearly Total:</strong> $</span>", sum])
dv.table(amounts.value.headers, amounts.value.values)
} else
dv.paragraph("~~~~\n" + amounts.error + "\n~~~~")
I figured out the rounding issue, but still having trouble figuring out how to get the total to appear under the last column. Here’s a photo if anyone has any insight!
1 Like
