Summing Property Fields Across Notes

[MattDaddy]

What I’m trying to do

I have client proposals that I’ve written over the course of the year, and I’d like a table that shows me how many proposals I’ve submitted and the total amount of the proposals grouped by either Pending, Won, or Lost. I have very little knowledge of coding, so any help would be much appreciated!

Things I have tried

I have tried simple and complex things (with help from ChatGPT and Copilot), but I haven’t gotten anything to work. Here are some of the pieces…

How the property looks in the proposal note template:

The simple solution does not populate the amounts. I do get lines for the various statuses, but there’s no summation of the the totals. Here’s the code:

table without id
  Status,
  sum(Proposal-Amount) as "Total Amount"
from "Proposals"
group by Status
sort Status asc

I also tried a couple complex solutions that errored out. Here are the two solutions:

const dvApi = this.app.plugins.plugins.dataview.api;
const proposalPages = dvApi.pages('"Proposals"');
const pendingAmount = proposalPages.where(p => p.Status == "Pending").sum(p => p.Amount);
const wonAmount = proposalPages.where(p => p.Status == "Won").sum(p => p.Amount);
const lostAmount = proposalPages.where(p => p.Status == "Lost").sum(p => p.Amount);
const totalAmount = pendingAmount + wonAmount + lostAmount;

const table = dvApi.container.createEl("table");
const headerRow = table.createEl("tr");
headerRow.createEl("th", { text: "Status" });
headerRow.createEl("th", { text: "Total Amount" });

const addRow = (status, amount) => {
  const row = table.createEl("tr");
  row.createEl("td", { text: status });
  row.createEl("td", { text: amount });
};

addRow("Pending", pendingAmount);
addRow("Won", wonAmount);
addRow("Lost", lostAmount);
addRow("Total", totalAmount);

dvApi.container.appendChild(table);

const dvApi = this.app.plugins.plugins.dataview.api;
const proposalPages = dvApi.pages('"Proposals"');
const pendingAmount = proposalPages.where(p => p.Status == "Pending").map(p => p.Amount).reduce((a, b) => a + b, 0);
const wonAmount = proposalPages.where(p => p.Status == "Won").map(p => p.Amount).reduce((a, b) => a + b, 0);
const lostAmount = proposalPages.where(p => p.Status == "Lost").map(p => p.Amount).reduce((a, b) => a + b, 0);
const totalAmount = pendingAmount + wonAmount + lostAmount;

dvApi.table(["Status", "Total Amount"], [
  ["Pending", pendingAmount],
  ["Won", wonAmount],
  ["Lost", lostAmount],
  ["Total", totalAmount]
]);

[holroy]

Something like the following should most likely do the trick:

```dataview
TABLE WITHOUT ID
  Status, sum(rows["Proposal Amount"]) as Sum, length(rows) as Count
FROM "Proposals"
GROUP BY Status
SORT Status asc
```

Remember that when you do GROUP BY all the original fields is gathered together into rows, and since you’ve opted to have a space in your property name you need to use the quoted syntax to access this, aka rows["Proposal Amount"].

[holroy]

If you want in a table with a single line, it’s a little more complex and requires both filtering and mapping the results, so then the query would look like:

```dataview
TABLE WITHOUT ID
  WonAmount as Lost
  , LostAmount as Won
  , PendingAmount as Pending
  , sum(WonAmount + LostAmount + PendingAmount) as Total
  , length(rows) as Count
FROM "Proposals"
WHERE Status
GROUP BY true
FLATTEN sum(map(filter(rows, 
  (fr) => fr.Status = "Lost"), 
  (mr) => mr["Proposal Amount"])) as LostAmount
FLATTEN sum(map(filter(rows, 
  (fr) => fr.Status = "Won"), 
  (mr) => mr["Proposal Amount"])) as WonAmount
FLATTEN sum(map(filter(rows, 
  (fr) => fr.Status = "Pending"), 
  (mr) => mr["Proposal Amount"])) as PendingAmount
SORT Status asc
```

A short explanation on the various FLATTEN statements:

• FLATTEN sum( ... ) as LostAmount – This part sums up all the elements from the ... and stores it into the LostAmount variable
• map( ... , (mr) => mr["Proposal Amount"]) – This maps each of the elements from ... and picks out only the proposal amount
• filter( rows, (fr) => fr.Status = "Lost) – This innermost part loops through the rows elements, and only returns those where the status is equal to “Lost”

In reverse order, what happens is therefore that we filter out the elements from rows where the Status is “Lost”, and then map each of those rows to its Proposal Amount before we sum those numbers up and store in to the LostAmount for later usage and display.

A crucial reason why this works at all is that we’re using GROUP BY true to collate all files with all information from the “Proposals” folder into the rows object. This then afterwards allows for massaging and trimming out the various parts we want to display.

[MattDaddy]

I’d like to add a row at the bottom to add up the Sum and Count columns.

I tried to add in some of the code from the second example like this:

TABLE WITHOUT ID Status, sum(rows["Proposal Amount"]) as Sum, length(rows) as Count 
FROM "Proposals" 
GROUP BY Status 
FLATTEN sum(rows["Proposal Amount"]) as TotalSum, length(rows) as TotalCount
SORT Status asc 

Obviously it didn’t work, but I’m guessing it is a simple solution that I need. Any help would be great, thanks!