Hi.
Previous declaration
To start I need to clarify: I’m not a coder or similar, I’m just an obsidian user exploring a new world.
This to say: my attempt below is only that, an attempt with a bad code. It’s just an idea with a mix of sources, trying to put together different references.
I acquired some skills in DQL (regular dataview) but I’m not versed in JS. So, if any good expert in the Forum want to “rebuild” or correct the code below, please do it.
Example notes
You don’t need to add multiple fields for date attributes (week, month, year…). You just need to have a valid date. You can create a specific field - as date: 2022-12-16 - or, as suggested in the examples below, daily notes with a date in title in the format YYYY-MM-DD (with this type of name you can use the implicit field file.day, a date type of metadata).
Some examples:
2022-11-28
---
spent:
- {name: eggs, value: 2.90}
- {name: cooffe, value: 2.10}
---
2022-12-01
---
spent:
- {name: cigarretes, value: 9.11}
- {name: cat food, value: 11.36}
---
2022-12-12
---
spent:
- {name: eggs, value: 2.5}
- {name: restaurant, value: 27.88}
---
2022-12-16
---
spent:
- {name: cigarretes, value: 4.09}
- {name: newspaper, value: 2.40}
---
2022-12-18
---
spent:
- {name: flowers, value: 6.75}
- {name: journal, value: 2.75}
---
The query
To start, it’s important to say that there is no way to do totals calculations in normal way in a new row at the bottom of the column. This implies some tricky ways with js (at least for js dummies like me), “injecting” multiple calculations.
Well, now, the ugly part (because I’m more comfortable in DQL, I use a mix of DQL and DVJS).
(a query placed in the note 2022-12-18)
// defining the query for current daily note date (let's say, today)
const DQL = await dv.tryQuery(`
TABLE WITHOUT ID S.name, S.value
WHERE file.path = this.file.path
FLATTEN spent AS S
`)
// Total title 1
const totalTitle = ["<span style='display:flex;justify-content:right'><b>Total Spent</b></span>"]
// calculate today total
const spentToday = DQL.values.map(g => g[1]).reduce((acc, val) => acc + val, 0).toFixed(2)
// Total today
const totalToday = ["<span style='display:flex;justify-content:right'>Today</span>", "$ " + spentToday]
// calculate costs in the current week until today
const DQLWeek = await dv.tryQuery(`
TABLE WITHOUT ID round(sum(rows.val),2)
WHERE spent
WHERE dateformat(file.day, "yyyy-WW") = dateformat(this.file.day, "yyyy-WW") AND file.day <= this.file.day
FLATTEN spent.value AS val
GROUP BY true
`)
// Total week
const totalWeek = ["<span style='display:flex;justify-content:right'>This week</span>", "$ " + DQLWeek.values[0][0]]
// calculate costs in the current month
const DQLMonth = await dv.tryQuery(`
TABLE WITHOUT ID round(sum(rows.val),2)
WHERE spent
WHERE dateformat(file.day, "yyyy-MM") = dateformat(this.file.day, "yyyy-MM") AND file.day <= this.file.day
FLATTEN spent.value AS val
GROUP BY true
`)
// Total month
const totalMonth = ["<span style='display:flex;justify-content:right'>This month</span>", "$ " + DQLMonth.values[0][0]]
// calculate costs in the current year
const DQLYear = await dv.tryQuery(`
TABLE WITHOUT ID round(sum(rows.val),2)
WHERE spent
WHERE file.day.year = this.file.day.year AND file.day <= this.file.day
FLATTEN spent.value AS val
GROUP BY true
`)
// Total year
const totalYear = ["<span style='display:flex;justify-content:right'>This year</span>", "$ " + DQLYear.values[0][0]]
// define a horizontal line in table
const hrArray = Array(2).fill('<hr style="padding:0; margin:0 -10px; border-top: 1px solid var(--background-modifier-border)">');
// push all values
DQL.values.push(hrArray)
DQL.values.push(totalTitle)
DQL.values.push(hrArray)
DQL.values.push(totalToday)
DQL.values.push(hrArray)
DQL.values.push(totalWeek)
DQL.values.push(hrArray)
DQL.values.push(totalMonth)
DQL.values.push(hrArray)
DQL.values.push(totalYear)
DQL.values.push(hrArray)
// create table
dv.table(["Purchased", "Cost"], DQL.values);
// remove the number of rows in first column title
this.container.querySelectorAll(".table-view-table tr:first-of-type th:first-of-type > span.small-text")[0].style.visibility = "hidden";
The result
lol
):
