Create Groupings and Subtotals in Dataview JS

[MattDaddy]

What I’m trying to do

I’d like to amend an existing Dataview JS table to do a couple things… first, I’d like to group by the Status column, and then I’d also like to create subtotals for the number of proposals and Amount for each Status grouping. Essentially, I’d like to be able to say “this is how many proposals we wrote this year, here’s how many we won and the dollar value, and how many are pending and the dollar value.”

I’m not a developer by any stretch of the imagination and have zero familiarity with coding. I’ve been utilizing this forum and some GenAI solutions to get this far. Below are the screenshot and code I have thus far. I truly appreciate the guidance!!

let pages = dv.pages('"Proposals"')

let totalPrice = pages.map((page, index) => [  
    page.status,
    page.file.link,  
    page.company,
	Number(page.Amount).toLocaleString('en-US', { style: 'currency',currency: 'USD',
    minimumFractionDigits: 0, 
    maximumFractionDigits: 0 })

]);

totalPrice["values"][totalPrice.length] = [  
    "**Total**",  
    "",  
    "",
    `<b>` + dv.func.default(dv.func.sum(pages.map(p => Number(p.Amount))), 0).toLocaleString('en-US', { style: 'currency', currency: 'USD',
     minimumFractionDigits: 0, 
    maximumFractionDigits: 0 }) + `<b>`
  
];

dv.table(["Status", "Proposal", "Company", "Amount"], totalPrice);

Things I have tried

I also tried to use this post from @holroy that looks amazing, but I couldn’t get it to work using my base code (and switching things around to use the other solution didn’t pan out either).

Automating Timesheet Help - Query embedded fields and sum hours across all files in current folder

I kind of triggered myself with these sum across the board, so here is a rather intense query doing three different sums in two tables. Before I present the query, and an output example, lets re-iterate on the base for the query, and which totals we’re going to build. Base query requirements (with three different sum variants) The base data is a standard DQL query holding the columns: project, date, tasks, hours. In all the sums below, we’re aiming to sum the hours in various ways, and the tab…

Below are the adjustments to my base code that I was hoping would work (and didn’t).

let pages = dv.pages('"Proposals"');

// Group pages by status
let groupedData = dv.groupBy(pages, p => p.status);

// Prepare the table with subtotals for each status
let totalPrice = [];
groupedData.forEach(group => {
    // Add rows for each proposal in the group
    group.rows.forEach(page => {
        totalPrice.push([
            page.status,
            page.file.link,
            page.company,
            Number(page.Amount).toLocaleString('en-US', { 
                style: 'currency', 
                currency: 'USD', 
                minimumFractionDigits: 0, 
                maximumFractionDigits: 0 
            })
        ]);
    });

    // Add a subtotal row for the group
    totalPrice.push([
        `**Subtotal (${group.key})**`, // Status subtotal label
        "",
        "",
        `<b>` + dv.func.sum(group.rows.map(p => Number(p.Amount))).toLocaleString('en-US', { 
            style: 'currency', 
            currency: 'USD', 
            minimumFractionDigits: 0, 
            maximumFractionDigits: 0 
        }) + `</b>`
    ]);
});

// Add a grand total row at the end
totalPrice.push([
    `**Grand Total**`,
    "",
    "",
    `<b>` + dv.func.sum(pages.map(p => Number(p.Amount))).toLocaleString('en-US', { 
        style: 'currency', 
        currency: 'USD', 
        minimumFractionDigits: 0, 
        maximumFractionDigits: 0 
    }) + `</b>`
]);

dv.table(["Status", "Proposal", "Company", "Amount"], totalPrice);

[holroy]

@MattDaddy

// Group pages by status
let groupedData = dv.groupBy(pages, p => p.status);

It kind of seems correct with the exception of the line above, which I believe should be:

// Group pages by status
let groupedData = pages.groupBy(p => p.status);

It’s a little hard to test, as it requires generation of quite a few test files.