2f:T11ac,sankeyChart = { try { const svg = d3.create("svg"); //.attr("viewBox", [0, 0, width, height]); const sankey = d3 .sankey() .nodeId((d) => d.name) .nodeWidth(15) .nodePadding(10) .extent([ [1, 5], [width - 1, height - 5] ]); //https://github.com/vega/datalib/wiki/Utilities#dl_duplicate const { nodes, links } = sankey(dl.duplicate(sankeyData)); const nodeWidth = Math.max(nodes[0].x1 - nodes[0].x0, 5); // Container for draggable nodes const node = svg .selectAll("g") .data(nodes) .join("g") .attr("transform", (d) => `translate(${d.x0}, ${d.y0})`); // d3V6 events: https://observablehq.com/@d3/d3v6-migration-guide#events node .attr("cursor", "move") .call(d3.drag().on("start", dragStart).on("drag", dragMove)); // Relative to container node .append("rect") .attr("height", (d) => d.y1 - d.y0) .attr("width", (d) => d.x1 - d.x0) .attr("fill", color) .attr("stroke", "#000") .append("title") .text((d) => `${d.name}\n${format(d.value)}`); // Relative to container/rect node .append("text") .attr("font-family", "sans-serif") .attr("font-size", 10) .attr("x", (d) => (d.x0 < width / 2 ? 6 + (d.x1 - d.x0) : -6)) // +/- 6 pixels relative to container .attr("y", (d) => (d.y1 - d.y0) / 2) // middle of node .attr("dy", "0.35em") .attr("text-anchor", (d) => (d.x0 < width / 2 ? "start" : "end")) .text((d) => d.name); const link = svg .append("g") .attr("fill", "none") .attr("stroke-opacity", 0.5) .selectAll("g") .data(links) .join("g") .style("mix-blend-mode", "multiply"); if (edgeColor === "path") { const gradient = link .append("linearGradient") .attr("id", (d) => (d.uid = DOM.uid("link")).id) .attr("gradientUnits", "userSpaceOnUse") .attr("x1", (d) => d.source.x1) .attr("x2", (d) => d.target.x0); gradient .append("stop") .attr("offset", "0%") .attr("stop-color", (d) => color(d.source)); gradient .append("stop") .attr("offset", "100%") .attr("stop-color", (d) => color(d.target)); } // if link .append("path") .attr("class", "link") .attr("d", d3.sankeyLinkHorizontal()) .attr("stroke", (d) => edgeColor === "none" ? "#aaa" : edgeColor === "path" ? d.uid : edgeColor === "input" ? color(d.source) : color(d.target) ) .attr("stroke-width", (d) => Math.max(1, d.width)); link .append("title") .text((d) => `${d.source.name} → ${d.target.name}\n${format(d.value)}`); function dragStart(event, d) { d.__x = event.x; d.__y = event.y; d.__x0 = d.x0; d.__y0 = d.y0; d.__x1 = d.x1; d.__y1 = d.y1; } //dragStart function dragMove(event, d) { d3.select(this).attr("transform", function (d) { const dx = event.x - d.__x; const dy = event.y - d.__y; d.x0 = d.__x0 + dx; d.x1 = d.__x1 + dx; d.y0 = d.__y0 + dy; d.y1 = d.__y1 + dy; if (d.x0 < 0) { d.x0 = 0; d.x1 = nodeWidth; } // if if (d.x1 > width) { d.x0 = width - nodeWidth; d.x1 = width; } // if if (d.y0 < 0) { d.y0 = 0; d.y1 = d.__y1 - d.__y0; } // if if (d.y1 > height) { d.y0 = height - (d.__y1 - d.__y0); d.y1 = height; } // if return `translate(${d.x0}, ${d.y0})`; }); // .attr('transform', function (d) { // https://github.com/d3/d3-sankey#sankey_update sankey.update({ nodes, links }); link.selectAll(".link").attr("d", d3.sankeyLinkHorizontal()); } //dragMove const _width = `${100}%`; svg.attr("id", "viewer"); // used by setupPanAndZoom function svg.attr("style", "border: 1px solid lightgray"); svg.attr("width", width); svg.attr("height", height + 10); //return svg.node(); const h = html`

${svg.node()}

`; return h; } catch (e) { return html`Sankey can not be generated because selections generated: ${e}`; } }30:T596,dwbexleyWorkbookData = { /* const workBook = await FileAttachment( "ConsolidatedBexleyFiancialStatements@2.xlsx" ).xlsx(); const data = workBook.sheet("Sankey Analysis Data", { headers: true }); */ const data = financialData; // Data Wrangling for Sankey Analysis // Extract Year data.forEach((d) => (d.Year = `${d.Year} ${d.Category}`)); // Zero out missing actuals data.forEach((d) => (d.Actual = d.Actual ? d.Actual : 0)); // Detemine deficit or surplus spending by category // Sankey must have unique nodes data.forEach( (d) => (d.Subcategory = d.Subcategory == "Income" && d.Fund == "Operating" ? "Operating Income" : d.Subcategory) ); data.forEach( (d) => (d.Fund = d.Fund == "Operating" && d.Category == "Expense" ? "Operating Expense" : d.Fund == "Operating" && d.Category == "Income" ? "Operating Income" : d.Fund == "Reserve" && d.Category == "Income" ? "Reserve Income" : d.Fund) ); data.forEach( (d) => (d.Fund = d.Fund == "Reserve" && d.Category == "Expense" ? "Reserve Expense" : d.Fund) ); data.forEach((d) => (d.Fund = `Total ${d.Fund}`)); data.forEach( (d) => (d.Category = "Income" || d.Category == "Expense" ? `Total ${d.Category}` : d.Category) ); return data; }31:Tcb2,sankeyData = { // Deficit or Surplus let links = []; if (data.length == 0) return null; // https://observablehq.com/@d3/d3-group#rollups d3.rollup( data.map(({ Fund, Category, Actual }) => ({ Fund: Fund.split(" ")[1], Category, Actual })), (v) => d3.sum(v, (d) => d.Actual), ...["Fund", "Category"].map((g) => (d) => d[g]) ).forEach((value, key) => { const difference = value.get("Total Expense") - value.get("Total Income"); // const difference = value.get("Total Income") - value.get("Total Expense"); if (difference != 0) { //if (difference > 0) { links.push({ source: `Total ${key} Income`, target: difference > 0 ? `${key} Deficit` : `${key} Surplus`, value: 0, parent: "Total Income" }); // } links.push({ source: difference > 0 ? `${key} Deficit` : `${key} Surplus`, target: difference > 0 ? "Total Income" : "Total Income", value: difference > 0 ? difference : -difference, parent: difference > 0 ? "Total Expense" : "Total Income" }); } }); const incomeLinks = fnGetLinks("Total Income", incomeDimensions, [ "Fund", "Category" ]); const expenseLinks = fnGetLinks("Total Expense", expenseDimensions, [ "Category", "Fund" ]); const totalIncome = d3.sum( incomeLinks.filter((d) => d.target == "Total Income"), (d) => d.value ); const totalExpense = d3.sum( expenseLinks.filter((d) => d.source == "Total Expense"), (d) => d.value ); links = links .concat([ { source: "Total Income", target: "Total Expense", value: totalIncome, parent: "Total Income" } ]) .concat(incomeLinks) .concat(expenseLinks); // All the unique nodes const nodeSet = new Set(); let nodes; for (const link of links) { nodeSet.add(link.source); nodeSet.add(link.target); nodes = [...nodeSet].map((name) => ({ name: name })); } return { nodes: nodes, links: links, totalIncome, totalExpense }; function fnGetLinks(category, dimensions, categorySort) { const sort = ( category == "Total Income" ? [...dimensions].concat(categorySort) : categorySort.concat([...dimensions]) ).map((g) => (d) => d[g]); const data2 = category == "Total Income" ? data : data.filter( (d) => !expenseDimensions.includes("Account") || !excludeSubCategories.includes(d.Subcategory) ); // https://observablehq.com/@d3/d3-group#rollups const rollup = d3.rollup( data2.filter(({ Category }) => Category == category), (v) => d3.sum(v, (d) => d.Actual), ...sort ); // https://observablehq.com/@d3/d3-group-d3-hierarchy const hierarchy = d3.hierarchy(rollup).sum(([, value]) => value); // re-summarize at each hierarchy level const links = hierarchy .links() .filter(({ source }) => source.data[0]) .map(({ source, target }) => ({ source: source.data[0], target: target.data[0], value: target.value, parent: category })); return links; } // fnGetLinks }32:T494,color = { // https://github.com/d3/d3-scale#scaleOrdinal // https://github.com/d3/d3-scale-chromatic#schemeCategory10 // https://observablehq.com/@d3/d3-scaleordinal // https://github.com/d3/d3-scale-chromatic#interpolateGreens // https://github.com/d3/d3-scale-chromatic#interpolateReds const incomeDomain = [0, sankeyData.totalIncome]; const expenseDomain = [0, sankeyData.totalExpense]; const adjIncomeRange = d3.scaleLinear().domain(incomeDomain).range([0.3, 1]); // const greens = d3.scaleSequential(incomeDomain, d3.interpolateGreens); const greens = (d) => d3.interpolateGreens(adjIncomeRange(d)); const reds = d3.scaleSequential(expenseDomain, d3.interpolateReds); // const color = d3.scaleOrdinal(d3.schemeCategory10); // return (d) => (d.name.includes("Deficit") ? "red" : color(d.name)); return (d) => { const parent = d.sourceLinks.length == 0 ? d.targetLinks[0].parent : d.sourceLinks[0].parent; return d.name.includes("Deficit") ? reds(d.value) : d.name.includes("Surplus") ? greens(d.value) : parent == "Total Income" ? greens(d.value) : reds(d.value); }; }33:[{"id":"485af4aae1c6fc8775543e05a3af1ac36fbb60d071a1f257b32e691652394af4bf0dbd758e392ee98830268a4a1d541e012f878640e062400bb634f8a03390e2","href":"https://static.observableusercontent.com/files/485af4aae1c6fc8775543e05a3af1ac36fbb60d071a1f257b32e691652394af4bf0dbd758e392ee98830268a4a1d541e012f878640e062400bb634f8a03390e2","name":"ConsolidatedBexleyFiancialStatements.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1666093688504.575,"size":180098},{"id":"8b181a1e30265436435fce291821516321b771ce6821b01421a19d37873da093522b16461f932f07c9ccf2e9925f65718b39af6a93ca018d0d8afe0e0758c490","href":"https://static.observableusercontent.com/files/8b181a1e30265436435fce291821516321b771ce6821b01421a19d37873da093522b16461f932f07c9ccf2e9925f65718b39af6a93ca018d0d8afe0e0758c490","name":"ConsolidatedBexleyFiancialStatements@1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1666271864310.824,"size":169270},{"id":"2ddd3f7888b503e8710ceec2b970b6ce15f19d86521e69ef49f937989764702de62d85723c0a8cfb93ecb487ecf8eac9367731ccfc467b72e294d164122328ae","href":"https://static.observableusercontent.com/files/2ddd3f7888b503e8710ceec2b970b6ce15f19d86521e69ef49f937989764702de62d85723c0a8cfb93ecb487ecf8eac9367731ccfc467b72e294d164122328ae","name":"ConsolidatedBexleyFiancialStatements@2.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1666279439852.437,"size":169341}] 2d:{"title":"Income and Expense Sankey Analysis","cells":[{"id":0,"value":"# Income and Expense Sankey Analysis","pinned":false,"hidden":false,"mode":"md"},{"id":393,"value":"{\n sankeyChart;\n //setupPanAndZoom({ id: \"viewer\", enableId: \"enable\", disableId: \"disable\" });\n if (!sankeyData) return \"No data to visualize.\";\n const controller = svgPanZoom(\"#viewer\", {\n zoomEnabled: true,\n controlIconsEnabled: false,\n fit: true,\n center: true\n });\n controller.zoomOut();\n return md`***Instructions***: Use mouse buttons and scroll to navigate/zoom. Mouse-over [Sankey](https://en.wikipedia.org/wiki/Sankey_diagram) for path/node values.`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"viewof year = {\n const column = Array.from(\n new Set(dwbexleyWorkbookData.map(({ Year }) => Year.slice(0, 4)))\n );\n const chkBoxes = Inputs.checkbox(column, {\n label: \"Year(s)\",\n sort: \"ascending\",\n multiple: true,\n value: column\n });\n return chkBoxes;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"viewof incomeDimensions = getDimensions(\"Income\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":157,"value":"viewof expenseDimensions = getDimensions(\"Expense\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":598,"value":"viewof includeSubcategory = {\n const chkboxes = Inputs.checkbox(expenseSubcategories.sort(), {\n label: expenseDimensions.includes(\"Account\")\n ? \"Include Accounts for Expense Subcategory(s)\"\n : \"\",\n value: expenseSubcategories.slice(0, 1),\n disabled: !expenseDimensions.includes(\"Account\")\n });\n return chkboxes;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":418,"value":"// must just before cell to fullscreen\nfullscreen = {\n const button = html`