2f:T411,flattenedAccumulatedData = { let data = []; let sum = monthlyManagerTrendsDataXlsxMonthlyManagerData[0]["BEG OF MONTH HEADCOUNT"]; /*data.push({ month: "TOTAL START", key: "TOTAL START", value: 0, baseSum: sum, newSum: 0 });*/ monthlyManagerTrendsDataXlsxMonthlyManagerData.forEach((d) => Object.keys(d) .filter( (k) => k != "MONTH" && k != "END OF MONTH HEADCOUNT" && k != "BEG OF MONTH HEADCOUNT" ) .forEach((k) => data.push({ month: k == "TRANFERS OUT" || k == "ATTRITION" ? d["MONTH"] + " (OUT)" : d["MONTH"] + " (IN)", key: k, value: k == "TRANFERS OUT" || k == "ATTRITION" ? -d[k] : d[k], baseSum: sum, newSum: (sum += k == "TRANFERS OUT" || k == "ATTRITION" ? -d[k] : d[k]) }) ) ); /*data.push({ month: "TOTAL END", key: "TOTAL END", value: 0, baseSum: 0, newSum: sum });*/ return data; }30:T7ca,Plot.plot({ x: { //align: 0, round: false, domain: flattenedAccumulatedData.map((m) => m.month).concat(" ") }, y: { grid: true, domain: [250, 360], label: "Employees", grid: false }, color: { domain: ["HIRES", "PROMOS", "TRANFERS IN", "TRANFERS OUT", "ATTRITION"], range: ["#74c476", "#31a354", "#006d2c", "#de2d26", "#fb6a4a"], legend: true }, marks: [ Plot.barY(flattenedAccumulatedData, { x: "month", y1: "baseSum", y2: "newSum", dx: 12, fill: "key", title: (d) => `${d.month}\n${d.key}: ${d.value}\nSum: ${d.newSum}` }), () => htl.svg` `, Plot.areaY( flattenedAccumulatedData .filter((d) => d.key == "HIRES") .concat({ month: " ", baseSum: flattenedAccumulatedData[flattenedAccumulatedData.length - 1].newSum }), { x: "month", y: "baseSum", fill: "url(#gradient1)" } ), Plot.lineY( flattenedAccumulatedData .filter((d) => d.key == "HIRES") .concat({ month: " ", baseSum: flattenedAccumulatedData[flattenedAccumulatedData.length - 1].newSum }), { x: "month", y: "baseSum", stroke: "black" //strokeWidth: 2 } ), Plot.ruleY([0], { strokeDasharray: "1.5" }), Plot.axisX({ x: (d) => (d.indexOf("(IN)") >= 0 ? d : ""), tickFormat: (d) => d.substring(0, 3), textAnchor: "start" }), Plot.gridX({ x: (d) => (d.indexOf("(IN)") >= 0 ? d : ""), stroke: "white", strokeOpacity: 0.5 }), Plot.gridY({ ticks: 10, stroke: "white", strokeOpacity: 0.5 }) ] })31:T8bb,Plot.plot({ x: { //align: 0, round: false, domain: flattenedAccumulatedData.map((m) => m.month + m.key), grid: false }, y: { grid: true, domain: [250, 360], label: "Employees", grid: false }, color: { domain: ["HIRES", "PROMOS", "TRANFERS IN", "TRANFERS OUT", "ATTRITION"], range: ["#006d2c", "#31a354", "#74c476", "#fb6a4a", "#de2d26"], legend: true }, marks: [ () => htl.svg` `, Plot.areaY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), fill: "url(#gradient2)" } ), Plot.lineY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), stroke: "steelblue" } ), Plot.link(flattenedAccumulatedData, { x: (d) => d.month + d.key, y1: "baseSum", y2: "newSum", //dx: 12, markerEnd: "arrow", markerStart: "none", stroke: "key", strokeWidth: 2, title: (d) => `${d.month}\n${d.key}: ${d.value}\nSum: ${d.newSum}` }), Plot.ruleY(flattenedAccumulatedData, { x1: (_, i) => i > 0 ? flattenedAccumulatedData[i - 1].month + flattenedAccumulatedData[i - 1].key : 0, x2: (d) => d.month + d.key, y: "baseSum", strokeDasharray: "3" }), Plot.ruleX([0], { strokeDasharray: "1.5" }), Plot.axisX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), tickFormat: (d) => d.substring(0, 3), textAnchor: "start" }), Plot.gridX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), stroke: "white", strokeOpacity: 0.5 }), Plot.gridY({ ticks: 10, stroke: "white", strokeOpacity: 0.5 }) ] })32:T5b9,Plot.plot({ x: { //align: 0, round: false, domain: flattenedAccumulatedData.map((m) => m.month + m.key), grid: false }, y: { grid: true, domain: [0, 360], label: "Employees", grid: false }, marks: [ () => htl.svg` `, Plot.areaY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), fill: "url(#gradient3)" } ), Plot.lineY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), stroke: "steelblue" } ), Plot.ruleX([0], { strokeDasharray: "1.5" }), Plot.axisX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), tickFormat: (d) => d.substring(0, 3), textAnchor: "start" }), Plot.gridX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), stroke: "white", strokeOpacity: 0.5 }), Plot.gridY({ ticks: 10, stroke: "white", strokeOpacity: 0.5 }) ] })33:T8bb,Plot.plot({ x: { //align: 0, round: false, domain: flattenedAccumulatedData.map((m) => m.month + m.key), grid: false }, y: { grid: true, domain: [250, 360], label: "Employees", grid: false }, color: { domain: ["HIRES", "PROMOS", "TRANFERS IN", "TRANFERS OUT", "ATTRITION"], range: ["#006d2c", "#31a354", "#74c476", "#fb6a4a", "#de2d26"], legend: true }, marks: [ () => htl.svg` `, Plot.areaY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), fill: "url(#gradient2)" } ), Plot.lineY( flattenedAccumulatedData.filter( (d) => d.key == "HIRES" || d.key == "ATTRITION" ), { x: (d) => d.month + d.key, y: (d) => (d.key == "HIRES" ? d.baseSum : d.newSum), stroke: "steelblue" } ), Plot.link(flattenedAccumulatedData, { x: (d) => d.month + d.key, y1: "baseSum", y2: "newSum", //dx: 12, markerEnd: "arrow", markerStart: "none", stroke: "key", strokeWidth: 2, title: (d) => `${d.month}\n${d.key}: ${d.value}\nSum: ${d.newSum}` }), Plot.ruleY(flattenedAccumulatedData, { x1: (_, i) => i > 0 ? flattenedAccumulatedData[i - 1].month + flattenedAccumulatedData[i - 1].key : 0, x2: (d) => d.month + d.key, y: "baseSum", strokeDasharray: "3" }), Plot.ruleX([0], { strokeDasharray: "1.5" }), Plot.axisX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), tickFormat: (d) => d.substring(0, 3), textAnchor: "start" }), Plot.gridX({ x: (d) => (d.indexOf("HIRES") >= 0 ? d : ""), stroke: "white", strokeOpacity: 0.5 }), Plot.gridY({ ticks: 10, stroke: "white", strokeOpacity: 0.5 }) ] })34:[{"id":"53f6fb08d0b4b9e4c7c0139def7e66e564c5a698bb171db1a484b839997b55f4eebc86c88800e311edffcfab884527d5359c74ee5d500bfdd0af6af119969764","href":"https://static.observableusercontent.com/files/53f6fb08d0b4b9e4c7c0139def7e66e564c5a698bb171db1a484b839997b55f4eebc86c88800e311edffcfab884527d5359c74ee5d500bfdd0af6af119969764","name":"Monthly Manager Trends DATA.xlsx - Monthly Manager DATA.csv","type":"text/csv","lastModified":1696840738499.009,"size":401},{"id":"e73cf1d9216a27f18a4bfde8614d3ae02c106fb9091b189b48909f34707f896efc80970b6ff850230f1814ca1c86d784e43935564cd287013df3e8245bbed74e","href":"https://static.observableusercontent.com/files/e73cf1d9216a27f18a4bfde8614d3ae02c106fb9091b189b48909f34707f896efc80970b6ff850230f1814ca1c86d784e43935564cd287013df3e8245bbed74e","name":"37b958b9-f91c-48bd-8f6b-04a681a87565.png","type":"image/png","lastModified":1697438252374.667,"size":193391}] 2d:{"title":"SWD exercise - 10/2023","cells":[{"id":0,"value":"# SWD exercise - [10/2023](https://community.storytellingwithdata.com/exercises/good-to-great)","pinned":false,"hidden":false,"mode":"md"},{"id":390,"value":"

","pinned":false,"hidden":false,"mode":"html"},{"id":398,"value":"### STEP 1: Spot the inconsistencies! \n\nList the elements you can identify that are not consistent (but could be) across the various graphs. How big of an issue do you feel this is? Write a sentence or two with your thoughts.","pinned":false,"hidden":false,"mode":"md"},{"id":538,"value":"Seems to be a copy & paste of different sources:\n- different types of charts: vertical bars, lines, grouped & stacked\n- 3x showing delta values, 1 showing absolute values (the sum)\n- different colors used: blue, red & (dark) blue, red & (lighter) blue, black & blue\n- width and margins of bars is different\n- (horizontal) grid lines are visible in two of the charts\n- 1x without a legend, 3x placed on the right, centered blow the title or centered below the graph\n- varying font sizes & styles in title, axis labels, legend & data labels\n- data labels are on top or within the bars\n- month axis is labelled differently: rotated long month names & abbreviated names, capitals & normal style\n- axis label are centered or top aligned, written in capitals & normal style\n","pinned":false,"hidden":false,"mode":"md"},{"id":404,"value":"### STEP 2: Improve a graph. \n\nPick one of the graphs to remake. What takeaway would you highlight? (Make assumptions freely about the context as needed.) How can you focus attention on important data points? What steps could you take to transform the graph from good to great? Download slides with the individual graphs (the data is embedded in PowerPoint) or access the data directly (spreadsheet) to implement your suggested changes.\n","pinned":false,"hidden":false,"mode":"md"},{"id":424,"value":"#### (1) get the data","pinned":false,"hidden":false,"mode":"md"},{"id":8,"value":"monthlyManagerTrendsDataXlsxMonthlyManagerData = await FileAttachment(\"Monthly Manager Trends DATA.xlsx - Monthly Manager DATA.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":422,"value":"#### (2) finding the overall part-to-whole contribution of the 5 increasing & decreasing categories\n- stacking by month\n- 2nd: and differentiated by color shade","pinned":false,"hidden":false,"mode":"md"},{"id":9,"value":"flattenedData = {\n let data = [];\n monthlyManagerTrendsDataXlsxMonthlyManagerData.forEach((d) =>\n Object.keys(d)\n .filter(\n (k) =>\n k != \"MONTH\" &&\n k != \"END OF MONTH HEADCOUNT\" &&\n k != \"BEG OF MONTH HEADCOUNT\"\n )\n .forEach((k) =>\n data.push({\n month:\n k == \"TRANFERS OUT\" || k == \"ATTRITION\"\n ? d[\"MONTH\"] + \" (OUT)\"\n : d[\"MONTH\"] + \" (IN)\",\n key: k,\n value: k == \"TRANFERS OUT\" || k == \"ATTRITION\" ? -d[k] : d[k]\n })\n )\n );\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":34,"value":"Plot.plot({\n color: {\n legend: true\n },\n marks: [\n Plot.auto(flattenedData, {x: \"key\", y: \"value\", color: \"month\"})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":292,"value":"Plot.plot({\n x: {\n domain: [\"HIRES\", \"PROMOS\", \"TRANFERS IN\", \"TRANFERS OUT\", \"ATTRITION\"]\n //range: [\"#006d2c\", \"#31a354\", \"#74c476\", \"#fb6a4a\", \"#de2d26\"]\n },\n color: {\n round: false,\n domain: flattenedData.map((d) => d.month.substring(0, 3)),\n legend: true,\n scheme: \"tableau10\"\n },\n marks: [\n Plot.barY(flattenedData, {\n x: \"key\",\n y: (d) => Math.abs(d.value),\n fill: (d) => d.month.substring(0, 3),\n tip: true\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":431,"value":"#### (3) refactor into heatmap","pinned":false,"hidden":false,"mode":"md"},{"id":327,"value":"Plot.plot({\n marginLeft: 100,\n x: {\n round: false,\n domain: flattenedData.map((d) => d.month.substring(0, 3))\n },\n y: {\n domain: [\"HIRES\", \"PROMOS\", \"TRANFERS IN\", \"TRANFERS OUT\", \"ATTRITION\"]\n },\n color: {\n scheme: \"blues\"\n },\n marks: [\n Plot.cell(flattenedData, {\n x: (d) => d.month.substring(0, 3),\n y: \"key\",\n fill: (d) => Math.abs(d.value),\n tip: true\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":437,"value":"#### (4) now stack by category and along the monthes from left to right\n- 2nd: meaningful coloring: shades of red = minus, shades of green = plus","pinned":false,"hidden":false,"mode":"md"},{"id":287,"value":"Plot.plot({\n color: {\n legend: true\n },\n marks: [\n Plot.auto(flattenedData, {x: \"month\", y: \"value\", color: \"key\"})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"Plot.plot({\n x: {\n round: false,\n domain: flattenedData.map((d) => d.month.substring(0, 3))\n },\n y: {\n grid: true,\n domain: [-70, 70],\n label: \"Employees\"\n },\n color: {\n domain: [\"HIRES\", \"PROMOS\", \"TRANFERS IN\", \"TRANFERS OUT\", \"ATTRITION\"],\n range: [\n /*\"#bae4b3\",*/ \"#74c476\",\n \"#31a354\",\n \"#006d2c\",\n /*\"#fcae91\",*/\n \"#fb6a4a\",\n \"#de2d26\"\n ],\n legend: true\n },\n marks: [\n Plot.barY(flattenedData, {\n x: (d) => d.month.substring(0, 3),\n y: \"value\",\n fill: \"key\",\n tip: true\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":441,"value":"#### (5) integrate\n- realign with sum value\n- use waterfall (plusses/minusses) to highlight monthly differences\n- use meaningful colors as above for categories again\n- 2nd: de-weight delta values by using arrows instead of bars","pinned":false,"hidden":false,"mode":"md"},{"id":47,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":45,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":117,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":405,"value":"### STEP 3: Tell a story. \n\nImagine that you've been asked to give a short presentation to leadership on the Sales Manager population, including what has contributed to headcount changes over the course of the year. How would you combine the various data together into a cohesive presentation? What overarching story would you tell? Design the graphs and slides that will achieve this.","pinned":false,"hidden":false,"mode":"md"},{"id":469,"value":"#### (1) start by showing the development of total numbers","pinned":false,"hidden":false,"mode":"md"},{"id":464,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":489,"value":"#### (2) changes in total numbers are due to different reasons","pinned":false,"hidden":false,"mode":"md"},{"id":480,"value":"Plot.plot({\n x: {\n round: false,\n domain: flattenedData.map((d) => d.month.substring(0, 3))\n },\n y: {\n grid: true,\n domain: [-70, 70],\n label: \"Employees\"\n },\n color: {\n domain: [\"HIRES\", \"PROMOS\", \"TRANFERS IN\", \"TRANFERS OUT\", \"ATTRITION\"],\n range: [\n /*\"#bae4b3\",*/ \"#74c476\",\n \"#31a354\",\n \"#006d2c\",\n /*\"#fcae91\",*/\n \"#fb6a4a\",\n \"#de2d26\"\n ],\n legend: true\n },\n marks: [\n Plot.barY(flattenedData, {\n x: (d) => d.month.substring(0, 3),\n y: \"value\",\n fill: \"key\",\n tip: true\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":516,"value":"#### (3) bringing both together, showing absolute value & different changes over time","pinned":false,"hidden":false,"mode":"md"},{"id":509,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":507,"value":"#### (4) we see a steep increase in october so focus once more on the changes by monthes & reasons","pinned":false,"hidden":false,"mode":"md"},{"id":493,"value":"Plot.plot({\n marginLeft: 100,\n x: {\n round: false,\n domain: flattenedData.map((d) => d.month.substring(0, 3))\n },\n y: {\n domain: [\"HIRES\", \"PROMOS\", \"TRANFERS IN\", \"TRANFERS OUT\", \"ATTRITION\"]\n },\n color: {\n scheme: \"blues\"\n },\n marks: [\n Plot.cell(flattenedData, {\n x: (d) => d.month.substring(0, 3),\n y: \"key\",\n fill: (d) => Math.abs(d.value),\n tip: true\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":523,"value":"#### (5) Take aways\n- we see a steep in\n- trend for hiring seem to descent slowly over time\n- January & February suffered under attrition\n- additionally boosted by transfers in February & March\n- shift in October was due to a heavy promo action\n- partly reverted by transfers & attrition in November & December -> is there a link?","pinned":false,"hidden":false,"mode":"md"}],"files":"$W34","stdlib":"1","titling":"auto"}