Dataview help - summing values

Data view plug-in help

Hi all. Wondering if anyone can assist me regarding displaying a sum of values taken from various sources. So for example I want to list the total sum of all outgoing finances. Each outgoing has its own note with the meta data listed. Eg : “Total_amount:: €20” may be displayed in a note. I want to list the total of ALL “Total_amount” values in a data view table/list. It seems the values need to be in an array but how do I do this. Do I need to use JavaScript and if so how do I do this? All notes would be tagged with #finance so can be identified this way in a query. Hope someone can help. Thanks.

1 Like

Let me try understand…

  1. You have a ‘central’ note with outgoing links (outlinks) to other notes. Each of these (outgoing) notes has an inline-field named “Total_amount”. (My first suggestion: remove “€” from number)

  2. In the ‘central’ note you want:
    a) a list/table of all (outgoing) notes and the “Total_amount” in each one?
    b) the sum of all “Total_amount”?
    c) both?

For option a) you can use something like this:

TABLE Total_amount as subtotal
FROM outgoing([[#]])
WHERE Total_amount
* Note2:
1 - [[#]] is the equivalent to "this.file". You can change the # by the file name
2 - "WHERE Total_amount" invoke only the (outgoing) notes with the field "Total_amount"

For b), if all invoked outgoing notes have the inline-field “Total_amount”, then you can use an inline query (if any outgoing note don’t have the field “Total_amount” and a value you’ll get an error):

="**Total**" + " €" + sum(this.file.outlinks.Total_amount)

For c) I don’t know any unified solution.

This options are simple dataview queries. I’m not habilitated to use the ‘dataviewjs’ queries.

This is an odd solution…

| Col 1                    | Col 2                                   |
| ------------------------ | --------------------------------------- |
| `=this.file.outlinks[0]` | `=this.file.outlinks[0].Total_amount`   |
| `=this.file.outlinks[1]` | `=this.file.outlinks[1].Total_amount`   |
| `=this.file.outlinks[2]` | `=this.file.outlinks[2].Total_amount`   |
| `=this.file.outlinks[3]` | `=this.file.outlinks[3].Total_amount`   |
| `=this.file.outlinks[4]` | `=this.file.outlinks[4].Total_amount`   |
| `=this.file.outlinks[5]` | `=this.file.outlinks[5].Total_amount`   |
| `=this.file.outlinks[6]` | `=this.file.outlinks[6].Total_amount`   |
| `=this.file.outlinks[7]` | `=this.file.outlinks[7].Total_amount`   |
| `=this.file.outlinks[8]` | `=this.file.outlinks[8].Total_amount`   |
| **Total**                | `=sum(this.file.outlinks.Total_amount)` |

Maybe I had misinterpreted your example: “outgoing finances” ≠ “outgoing links”. My bad, english is not my language.
Well, with that difference, I can suggest this:

  1. A table with the list of the “Total_amount” by file
TABLE Total_amount
FROM #finance
WHERE Total_amount
  1. For the total of all “Total_amount” in all the pages with the tag #finance, you can use this (but don’t ask me why, my javascript knowledge is equal to zero):
	.Total_amount.array().reduce((acc, val) => acc + val, 0))
  1. To join the two queries, you can create a md table and embed the blocks of the two previous queries:
| List          | Total         |
| ------------- | ------------- |
| ![[#^83260c]] | ![[#^826374]] |

- to find the blocks inside the note you write ![[#^ and then select the blocks > for the first column the block of query 1 (list); for the second one the block of query 2 (total)

1 Like

Thank you so much! Option 2 gave me what I needed. How can I append “€” in front of the summed value?

My answer is a mess… I really need to learn something about javascript to use dataviewjs.
The dataviewjs query (option 2) comes from other post… I just adapted it.
About your question, I made some tests and this works (certainly not the right java code, but it works):

dv.paragraph("€" + dv.pages("#finance")
	.Total_amount.array().reduce((acc, val) => acc + val, 0))

Thanks! Any idea how I could apend the euro to values in dataview tables?

A table with which query?

Table Total_amount
From “inbox”
Where Total_amount

Also any idea on how to include decimal places in the array summing?

Add prefix “€” is a ‘cosmetic’ thing, not a real unit. In the same way, you can add the suffix “.00”

TABLE ("€" + Total_amount + ".00") as "Total Amount"
FROM "inbox"
WHERE Total_amount

But if you have values in “Total_amount” as 20.55 then you don’t need the suffix “.00”

When I used this query for values containing decimal places it returned a value with loads of decimal places in the total. ```dataviewjs
.Total_amount.array().reduce((acc, val) => acc + val, 0))

I see. Maybe that requires any special js function. In dataview plugin documentation I found the function “round”, but I don’t know how to apply it (in simple dataview queries is simple).

Wait I found a way… please verify if works

dv.paragraph("€" + dv.pages("#finance")
	.Total_amount.array().reduce((acc, val) => acc + val, 0).toFixed(2))

Thank you so much for all your help. I really appreciate it.

1 Like

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