Best Method to Display the Sum Total of a Dataview Column?

Things I have tried

I’ve been messing around with the sum() function but everytime I try, I break the table I’ve created.

What I’m trying to do

I am attempted to track my spending in a rapid log format in my daily notes. In other words, I’m hoping to just type something along the lines of “Spending:: Starbucks - 10” into my daily notes rapid log, and then use Dataview to compile that information later.

So, for example, a given day’s rapid log may look like this:

  • 9:00am
    • Spending:: Bought tights, a card for Anna, and gift bags at Duane Reade - 23.48
  • 10:15am
    • Spending:: Corner store - 2.50
    • Breakfast:: Coffee and a croissant
  • 10:30am-12pm:
    • Church
    • PG/Chancel choir sang
    • Spending:: Offering - 30.00
  • 12:00-1am
    • Helped Gelilah with her tech stuff.
  • 1am-3pm
    • Spending:: Dinner at The Row with Anna - 54.75
    • Lunch:: Shrimp and Grits
  • 3:45-4:30pm
    • Watered Shannon’s plants.
    • Spending:: Starbucks Coffee Reload - 10.00
  • 5:15pm
    • Starbucks Coffee Reload - 10.00
  • Spending:: Moving Boxes - 117.00
  • 8pm
    • Dinner:: Leftover Bua Thai Phuket noodles.
  • 9:00pm:
    • IWatched:: [[Euphoria]]

I’ve gotten so far as making a table that displays my spending for that day using this query:

TABLE WITHOUT ID
file.link as Date, split(Spending, " - ")[0] as Where, split(Spending, " - ")[1] as Amount, TotalSpending as Total
FROM "Diary/Daily Notes"
FLATTEN Spending
WHERE file.name = this.file.name

Table looks like this:

Where I’m getting stuck is I would like to either (A) create a running total in the total column so the last row displays the total amount spent that day, or (B) just create an inline query that displays the total spending for that day.

I thought I could do this using the sum() function, but my tables break every time I attempt it. I’m assuming I’m not using the best methodology to achieve what I’m aiming to achieve.

Does anyone have any thoughts or ideas on how I can better accomplish this?

Thanks in advance!

Hi.

If you want a table in your daily note and your values in “Spending” using only one number, you can do this:

  1. First create another “Spending” field with this:
Spending:: **TOTAL** - `=round(sum(number(this.Spending)), 2)`
  1. Use this query:
```dataview
TABLE WITHOUT ID split(Spending, " - ")[0] AS Where, choice(contains(Spending, "TOTAL"), "**" + round(sum(number(this.Spending)), 2) + "**", number(Spending)) AS Amount
FROM "your/folder/path"
FLATTEN Spending
WHERE file.name = this.file.name
```
  1. If you don’t want the “TOTAL” inside your table, don’t create the field defined in step 1. and use this:
```dataview
TABLE WITHOUT ID split(Spending, " - ")[0] AS Where, number(Spending) AS Amount
FROM "Ltest"
FLATTEN Spending
WHERE file.name = this.file.name
```
**TOTAL**: `=round(sum(number(this.Spending)), 2)` $

4 Likes

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