Attempting to summarize numbers with dollar sign added (bases)

Help
1

What I’m trying to do

I have a formula I use to turn my note properties numbers into dollar values like so:

formulas:
  cost_dollar: if(cost, "$" + cost.toFixed(2), "")

The challenge I face is due to the built in summarize function and it’s inability to deal with non-number values:

  Sum: |+
    values.filter(value.isType("number")).reduce(value + acc, 0).round(2)

This means that I can not “summarize” my cost_dollar(sum) field easily, the same way a number field is able to sum() totals up/down.

Things I have tried

I looked around on the help site for info, it’s where I got the formula for my cost_dollar function. I also searched a few times for similar results but I’m sure I’m missing some foundational element here, any help would be appreciated.

  sum23: |
values.filter(if(cost_dollar.isType(String), cost_dollar.replace("$",""), value).reduce(value + acc, 0).round(2))
2

The reason your “Sum” function doesn’t work is that the “filter” clause, instead of turning “strings which are numbers prefixed by dollar signs” into “the number portions, as numbers”, it just removes from the list any element that isn’t an unadorned number.

Instead of “filter”, try using “map”. Inside the parentheses, you want an expression that takes a string, strips off the dollar sign, and converts the result into a Number property. Then the input to the “reduce” function will be a List of Number values.

3

Give this summary a try:

"$" + values.map(number(value.replace("$",""))).filter(value.isType("number")).sum().toFixed(2)

There are multiple ways to go about it. A mapped and filtered reduce() could also work.

Or if you’re feeling frisky, you could CSS the dollar sign into the column by the formula name or property name. Then the summary would be a simple values.sum().round(2) with—if necessary—a filter for non-numbers.

1 Like
4

Thank you both for responding and attempting to help!

values.map(number(value.replace("$",""))).filter(value.isType("number")).Sum()

and

values.map(number(value.replace("$",""))).filter(value.isType("number")).sum()

Return empty results. While playing around with this I found that if I changed number to list from above like so:

values.map(list(value.replace("$",""))).filter(value.isType("list"))

Then I could at least see my values displayed in the results.

I’ll also note, that when there was only one value in the list, e.g.

"$" + values.map(number(value.replace("$",""))).filter(value.isType("number"))

Did display the sum of the available (1) numbers, so there’s that. So for singular unique values that seems helpful.

But other than that, I’ve made no progress with the help you’ve both provided.

5

To sum prices from a list of lists, append .flat() to the initial value:

"$" + values.flat().map(number(value.replace("$",""))).filter(value.isType("number")).sum().toFixed(2)

If you can’t get it to work for your specific case, then give relevant examples of your YAML and actual base formula(s) in order for people to help out.

6

I ended up creating a test vault to share, but realize you can’t easily do that because no zip files?

Anyway:

~/A/o/test> find . -type f -name ‘*.md’ 2>/dev/null -exec cat ‘{}’ ;

---
cost: 123
due date: 2025-12-22
paid: 2025-12-20
---
---
cost: 10.99
due date: 2025-12-31
paid:
---
---
cost: 111.11
due date: 2025-12-31
paid:
---
---
cost: 49.99
due date: 2025-11-12
paid: 2025-11-02
---
---
cost: 66.93
due date: 2025-11-26
paid: 2025-11-08
---

~/A/o/test> find . -type f -name ‘*.base’ 2>/dev/null -exec cat ‘{}’ ;

filters:
  and:
    - file.inFolder("Areas/Bills")
formulas:
  cost_dollar: if(cost, "$" + cost.toFixed(2), "")
views:
  - type: table
    name: Paid
    filters:
      or:
        - "!paid.isEmpty()"
        - paid < today()
    groupBy:
      property: file.folder
      direction: ASC
    order:
      - file.name
      - formula.cost_dollar
      - due date
      - paid
    summaries:
      cost: Sum
    columnSize:
      file.name: 109
      note.cost: 98
  - type: table
    name: Unpaid
    filters:
      or:
        - paid.isEmpty()
        - paid >= today()
    groupBy:
      property: file.folder
      direction: ASC
    order:
      - file.name
      - formula.cost_dollar
      - paid
      - due date

image
image980×529 26.3 KB

Hopefully this helps explain what I’m doing, the field “cost” is a number, the field “cost_dollar” is a function, and for whatever reason every time I plug in anything mentioned so far, I do not have a working result. For instance, this last comment from @dawni resulted in sum() nuking the results and the trailing tofixed being dropped. If I delete both the last two parts of that I see my two numbers with dollar signs in the summaries like so:

image
image985×514 27.7 KB

Thanks again for trying to help everyone

7

I pasted your YAML into five notes, and both of the Summarize formulas I shared return the correct sum:

Bases summary with currency symbol
Bases summary with currency symbol886×382 21.6 KB

```base
summaries:
  sum: '"$" + values.map(number(value.replace("$",""))).filter(value.isType("number")).sum().toFixed(2)'
filters:
  and:
    - file.inFolder("Areas/Bills")
formulas:
  cost_dollar: if(cost, "$" + cost.toFixed(2))
views:
  - type: table
    name: Paid
  filters:
    and:
      - paid
      - paid < today()
    groupBy:
      property: file.folder
      direction: ASC
    order:
      - file.name
      - formula.cost_dollar
      - due date
      - paid
    sort: []
    summaries:
      cost: Sum
      formula.cost_dollar: sum
  - type: table
    name: Unpaid
    filters:
      or:
        - paid.isEmpty()
        - paid >= today()
    groupBy:
      property: file.folder
      direction: ASC
    order:
      - file.name
      - formula.cost_dollar
      - paid
      - due date
    sort: []
    summaries:
      formula.cost_dollar: sum

```

A heads up: Your filters in your “Paid” view might have a mistake. You did:

filters:
  or:
    - "!paid.isEmpty()"
    - paid < today()

which is the same as simply

filters:
  or:
    - paid

To return only the notes where paid is in the past, use and:

filters:
  and:
    - paid
    - paid < today()

To return all notes where paid is not empty, you can use a single !paid.isEmpty() or even just paid.

8

So I kept trying to figure out why you reported this as working but it wasn’t working for me, it’s because you’re on the development version, and I was not.

Anyway, after fixing two lines in the base file, namely:

summaries:
  sum: '"$" + values.map(number(value.replace("$",""))).filter(value.isType("number")).sum().toFixed(2)'
...
formulas:
  cost_dollar: if(cost, "$" + cost.toFixed(2))

Also, you never mentioned that you changed the cost_dollar formula, which was part of the issue,
AND upgrading to the development version resulted in a solution for me.

9

Um, that reads to me like you’re blaming me for something? The two lines you said you fixed are the exact lines I gave you in the full base to copy-paste. If you then changed them further to make them work for you, then right on.

But it sounds like with the help of people in this thread, you now have a solution. So that’s good.

Have a good day.