Supervision Hours by Supervisor with Percentages

const startDate = new Date(`${dv.current()["Start Date"]}`);
const endDate = new Date(`${dv.current()["End Date"]}`);
 
const MIN_SESSION_HOURS = 0.5; // 30 minutes
 
// Fetch pages with the 'clienthours' tag and #supervision
let pages = dv.pages('"100 CPD Record" and #supervision')
  .where(p => {
    const fileDate = new Date(p.date);
    const hrs = Number(p.hours) || 0;
    return fileDate >= startDate && fileDate <= endDate && hrs >= MIN_SESSION_HOURS;
  })
  .sort(p => p.date, 'asc');
 
dv.paragraph(`Total records fetched: ${pages.length}`);
 
if (pages.length === 0) {
  dv.paragraph("No pages found for the specified query.");
} else {
  // Group by 'who' first, then within each group by 'Body' and 'medium'
  const whoGroups = {};
  let grandTotalHours = 0;
 
  pages.forEach(p => {
    const who = p.who || "Not stated";
    const body = p.Body || "No Body";
    const medium = p.medium || "No medium";
    const hours = p.hours || 0;
 
    if (!whoGroups[who]) {
      whoGroups[who] = {
        combinations: {},
        totalHours: 0,
      };
    }
 
    const comboKey = `${body} - ${medium}`;
 
    if (!whoGroups[who].combinations[comboKey]) {
      whoGroups[who].combinations[comboKey] = 0;
    }
 
    whoGroups[who].combinations[comboKey] += hours;
    whoGroups[who].totalHours += hours;
    grandTotalHours += hours;
  });
 
  // Display table for each 'who'
  for (const [who, data] of Object.entries(whoGroups).sort()) {
    const whoPercentage = ((data.totalHours / grandTotalHours) * 100).toFixed(2);
    dv.header(3, `Supervision With: ${who} (${whoPercentage}%)`);
 
    const tableData = [];
 
    for (const [combo, hours] of Object.entries(data.combinations).sort()) {
      const percentOfWho = ((hours / data.totalHours) * 100).toFixed(2);
      const percentOfTotal = ((hours / grandTotalHours) * 100).toFixed(2);
      tableData.push([
        `**${combo}**`,
        `**${hours}**`,
        `**${percentOfWho}%**`,
        `**${percentOfTotal}%**`,
      ]);
    }
 
    // Subtotal for this 'who'
    tableData.push([
      "**Subtotal**",
      `**${data.totalHours}**`,
      "**100%**",
      `**${whoPercentage}%**`,
    ]);
 
    dv.table(
      ["Body - Medium", "Total Hours", "% of Who", "% of Total"],
      tableData
    );
  }
 
  // Grand total
  dv.paragraph(`**Grand Total Hours Across All Supervision: ${grandTotalHours}**`);
}

TABLE
  file.link AS "Record",
  choice(length(default(medium, "")) = 0, "⛔ missing", medium) AS "Medium",
  choice(length(default(who, "")) = 0, "⛔ missing", supervisor) AS "Supervisor",
  choice(length(default(body, "")) = 0, "⛔ missing", body) AS "Body"
FROM "100 CPD Record/Supervision"
WHERE
  contains(file.tags, "#supervision")
  AND (
    length(default(medium, "")) = 0
    OR length(default(who, "")) = 0
    OR length(default(body, "")) = 0
  )
SORT file.name ASC