2f:T49b,function getColorFromScale(scale) { // Helper function to convert hex color to RGB function hexToRgb(hex) { var shorthandRegex = /^#?([a-f\d])([a-f\d])([a-f\d])$/i; hex = hex.replace(shorthandRegex, function (m, r, g, b) { return r + r + g + g + b + b; }); var result = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex); return result ? [ parseInt(result[1], 16), parseInt(result[2], 16), parseInt(result[3], 16) ] : [255, 255, 255]; // Default to white if invalid hex color } var colors = ['#50C4AA', '#B6C95C', '#FACB3E', '#FC800F', '#FF4759']; // Calculate the index of the color in the color ramp based on the scale var index = Math.floor(scale * (colors.length - 1)); // Get the start and end colors from the color ramp var startColor = hexToRgb(colors[index]); var endColor = hexToRgb(colors[index + 1]); // Calculate the intermediate color based on the scale var color = startColor.map(function (channel, i) { var range = endColor[i] - channel; return Math.round(channel + range * (scale * (colors.length - 1) - index)); }); // Return the color in RGB format return "rgb(" + color.join(",") + ")"; } 30:T525,dynamicHTML = { const summary = document.querySelector('.summary'); summary.innerHTML = `

${totals[0].workouts}

Workouts

${totals[0].hours.toFixed(1)}

Hours

${totals[0].active_days}

Active Days

${totals[0].distance}

Miles

` }31:T8d3,32:[{"id":"df184f61bc249fdafb1c822fcb5f9d212fd4c53167bb8ea76e8f74b350709e3c757e9d5bfa8f0b02a1e43e3bd3657fde7a8674afcd1298c263325900f40113de","href":"https://static.observableusercontent.com/files/df184f61bc249fdafb1c822fcb5f9d212fd4c53167bb8ea76e8f74b350709e3c757e9d5bfa8f0b02a1e43e3bd3657fde7a8674afcd1298c263325900f40113de","name":"peloton-data@12.csv","type":"text/csv","lastModified":1687961807634.456,"size":603789},{"id":"f9bb17d3a3bb45aef7f8cdd8f33dcd89510ff91852e28a8d44b01ebb365551a6c5e7178738e56660f2946d165861fd3443707ca977f44d9ea38e3720f61adec6","href":"https://static.observableusercontent.com/files/f9bb17d3a3bb45aef7f8cdd8f33dcd89510ff91852e28a8d44b01ebb365551a6c5e7178738e56660f2946d165861fd3443707ca977f44d9ea38e3720f61adec6","name":"peloton-data@7.csv","type":"text/csv","lastModified":1686261259082.899,"size":595800},{"id":"f9bb17d3a3bb45aef7f8cdd8f33dcd89510ff91852e28a8d44b01ebb365551a6c5e7178738e56660f2946d165861fd3443707ca977f44d9ea38e3720f61adec6","href":"https://static.observableusercontent.com/files/f9bb17d3a3bb45aef7f8cdd8f33dcd89510ff91852e28a8d44b01ebb365551a6c5e7178738e56660f2946d165861fd3443707ca977f44d9ea38e3720f61adec6","name":"peloton-data@8.csv","type":"text/csv","lastModified":1686261353908.417,"size":595800},{"id":"3f1d8d8c67c157a836005006fb2bdfc1eb443d9ea2b3bff65e2942fe84fe33a565fefb27d3aefb389a5779d635b8855303cfcfc3e8f84d112120cedf81ad0827","href":"https://static.observableusercontent.com/files/3f1d8d8c67c157a836005006fb2bdfc1eb443d9ea2b3bff65e2942fe84fe33a565fefb27d3aefb389a5779d635b8855303cfcfc3e8f84d112120cedf81ad0827","name":"peloton-data@9.csv","type":"text/csv","lastModified":1686317528436.146,"size":596594},{"id":"50dd567ae3db3ea44306eba47d96fa37bbf1b90120e994305af8814a103a93957ec970f97ab2a3bc52f6ed0faa8466526d45c5ad1c52d2e2691cb47bd73edc41","href":"https://static.observableusercontent.com/files/50dd567ae3db3ea44306eba47d96fa37bbf1b90120e994305af8814a103a93957ec970f97ab2a3bc52f6ed0faa8466526d45c5ad1c52d2e2691cb47bd73edc41","name":"peloton-data@6.csv","type":"text/csv","lastModified":1686230298422.444,"size":595409},{"id":"b6f41096b99e633136d239cc3fb1a6a309243edd01d6c4aaed075ec8725f885574e68ba245075e622ba6a151004814e83bea544d033880d2ce736bce79029dab","href":"https://static.observableusercontent.com/files/b6f41096b99e633136d239cc3fb1a6a309243edd01d6c4aaed075ec8725f885574e68ba245075e622ba6a151004814e83bea544d033880d2ce736bce79029dab","name":"peloton-data@10.csv","type":"text/csv","lastModified":1686759212237.766,"size":599775},{"id":"811d30dac942f40675b4b793ab046b72f754827517655bf4ea0b5b65fec02b54b2f5f06132e4a260996634f9d13b7e37fb3daa9dfecff959590f5265cc7cdf8a","href":"https://static.observableusercontent.com/files/811d30dac942f40675b4b793ab046b72f754827517655bf4ea0b5b65fec02b54b2f5f06132e4a260996634f9d13b7e37fb3daa9dfecff959590f5265cc7cdf8a","name":"peloton-data.csv","type":"text/csv","lastModified":1685637713279.271,"size":590604},{"id":"86bbcbc0bee10f40e8fe0b77c0bb43d0acb2d4fb204afb72b42716c018876e408e3fa00517ffd28cd226e43704a94837a0b907848e97f7dbeb1becd2a8a45fea","href":"https://static.observableusercontent.com/files/86bbcbc0bee10f40e8fe0b77c0bb43d0acb2d4fb204afb72b42716c018876e408e3fa00517ffd28cd226e43704a94837a0b907848e97f7dbeb1becd2a8a45fea","name":"ref_instructors.csv","type":"text/csv","lastModified":1685637741244.36,"size":20360},{"id":"873fd4409ca19a1c881fdf1721e7b36f789c45c70e93086677d9e6a4f52a96b93165d51bc3e4439d9b0a191427ca22a26e62f3acbcd466cb559da0103ad2fafc","href":"https://static.observableusercontent.com/files/873fd4409ca19a1c881fdf1721e7b36f789c45c70e93086677d9e6a4f52a96b93165d51bc3e4439d9b0a191427ca22a26e62f3acbcd466cb559da0103ad2fafc","name":"peloton-data@11.csv","type":"text/csv","lastModified":1687878396383.824,"size":602967},{"id":"91f2e0bf9359946af4470155f085ea54d9b54fac1776446771f46087483a2307c26fdbfc502f97aad5a97d66d21187bb492a9c9991a5e3e8a71dd0e0378210e3","href":"https://static.observableusercontent.com/files/91f2e0bf9359946af4470155f085ea54d9b54fac1776446771f46087483a2307c26fdbfc502f97aad5a97d66d21187bb492a9c9991a5e3e8a71dd0e0378210e3","name":"pelotanya.Rproj","type":"application/octet-stream","lastModified":1688232673706.017,"size":205},{"id":"3ac5e889392407e53a352468c3d1789f7bbd7a87b905f23908dc6294c3f075b7b9ee704f26c6571ea8b070386c2e5ec628035e291dfb35ca738c0024921171ae","href":"https://static.observableusercontent.com/files/3ac5e889392407e53a352468c3d1789f7bbd7a87b905f23908dc6294c3f075b7b9ee704f26c6571ea8b070386c2e5ec628035e291dfb35ca738c0024921171ae","name":"peloton-data@13.csv","type":"text/csv","lastModified":1688232700230.631,"size":606982},{"id":"6324f1b7476a6395b71450d25bb723362759d2eb9f4b8b62045c1b83d1ff6495dd1cc8ac334927b1430719b1f0943b587cc6fce9e8f005c83aed259b3dcb313b","href":"https://static.observableusercontent.com/files/6324f1b7476a6395b71450d25bb723362759d2eb9f4b8b62045c1b83d1ff6495dd1cc8ac334927b1430719b1f0943b587cc6fce9e8f005c83aed259b3dcb313b","name":"peloton-data@14.csv","type":"text/csv","lastModified":1689260313143.578,"size":614638}] 2d:{"title":"Dashboard: PELOTANYA","cells":[{"id":0,"value":"# Dashboard: PELOTANYA\n\nPersonal workout dashboard based on activity on my Peloton app.","pinned":false,"hidden":false,"mode":"md"},{"id":586,"value":"viewof timeFrame = Inputs.radio([\"ITD\", \"YTD\",\"MTD\"], {label: \"Timeframe:\", value: \"YTD\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":49,"value":"viewof filters = (\n Inputs.form({ \n startDate: Inputs.date({label: \"Start Date\", value: startDate}),\n endDate: Inputs.date({label: \"End Date\", value: today}),\n workoutType: Inputs.select([\"Bike Bootcamp\", \"Cycling\",\"Running\",\"Strength\", \"Stretching\",\"Tread Bootcamp\",\"Walking\",\"Yoga\"], \n {label: \"Workout Type\", multiple:5, value:[\"Bike Bootcamp\", \"Cycling\",\"Running\",\"Strength\",\"Stretching\",\"Tread Bootcamp\",\"Walking\",\"Yoga\"]})\n },\n {template})\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":40,"value":"

","pinned":false,"hidden":false,"mode":"html"},{"id":130,"value":"

By Instructor${instructorTable()}

Monthly${lineChart()}

By Type${barChart()}

","pinned":false,"hidden":false,"mode":"html"},{"id":600,"value":"## Date Selection\nCreate default start and end dates for filter input based on radio button from time frame, e.g. YTD return the first day of the year based on today's date.","pinned":false,"hidden":false,"mode":"md"},{"id":606,"value":"todayRaw = new Date();","pinned":true,"hidden":false,"mode":"ojs"},{"id":843,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":590,"value":"today = new Date().toISOString().slice(0, 10);","pinned":true,"hidden":false,"mode":"ojs"},{"id":602,"value":"firstDayOfMonth = new Date(todayRaw.getFullYear(), todayRaw.getMonth(), 1).toISOString().slice(0, 10);","pinned":true,"hidden":false,"mode":"ojs"},{"id":613,"value":"firstDayOfYear = new Date(todayRaw.getFullYear(), 0, 1).toISOString().slice(0, 10);","pinned":true,"hidden":false,"mode":"ojs"},{"id":623,"value":"firstDateWorkout = d3.min(workouts, d=>d.created_at).toISOString().slice(0, 10);","pinned":true,"hidden":false,"mode":"ojs"},{"id":616,"value":"startDate = timeFrame === \"ITD\" ? firstDateWorkout : timeFrame===\"YTD\" ? firstDayOfYear : firstDayOfMonth","pinned":true,"hidden":false,"mode":"ojs"},{"id":703,"value":"Create list of months to pass into data base. Useful for aggregating by month in queries.","pinned":false,"hidden":false,"mode":"md"},{"id":630,"value":"months = d3.timeMonths(new Date(2019, 1, 1), new Date()).map(function(date) {\n return { month: d3.timeFormat(\"%Y-%m-%d\")(date) };\n});","pinned":false,"hidden":false,"mode":"ojs"},{"id":477,"value":"## Custom Functions\n\nFunctions to create chart/table widgets. Helper functions to stylize additional elements, e.g. table images and heatmap effect.","pinned":false,"hidden":false,"mode":"md"},{"id":249,"value":"instructorTable = () => { return Inputs.table(byInstructor, {\n columns: [\"instructor_name\", \"avg_difficulty\",\"workouts\",\"minutes\"],\n header: {\n instructor_name: \"Instructor\",\n avg_difficulty: \"Avg Difficulty\",\n workouts: \"Workouts\",\n minutes: \"Minutes\"\n },\n align: {\n avg_difficulty:\"center\",\n workouts: \"center\",\n minutes: \"left\"\n },\n width: {\n instructor_name:170,\n avg_difficulty:85\n },\n format: {\n instructor_name: x => instructor_image(x),\n avg_difficulty: x => heatmap(x,min_diff-0.1,max_diff+0.1),\n minutes: sparkbar(d3.max(byInstructor, d => d.minutes)),\n },\n maxWidth:550,\n rows:17,\n margin:1\n}\n )\n }","pinned":false,"hidden":false,"mode":"ojs"},{"id":559,"value":"max_diff = d3.max(byInstructor, d => d.avg_difficulty)","pinned":true,"hidden":false,"mode":"ojs"},{"id":562,"value":"min_diff = d3.min(byInstructor, d => d.avg_difficulty)","pinned":true,"hidden":false,"mode":"ojs"},{"id":251,"value":"function instructor_image(name) {\n var data = instructors.filter(d => d.name === name);\n return htl.html`

${name}

`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":304,"value":"function heatmap(value, minValue, maxValue) {\n // Calculate the color scale based on the value's position\n var scale = (value - minValue) / (maxValue - minValue);\n var color = getColorFromScale(scale);\n\n // Create the output div with the scaled background color\n return htl.html`\n

\n ${value}\n

\n `;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":723,"value":"Sparkbark adapted from [Mike Bostock](https://observablehq.com/@observablehq/input-table)","pinned":false,"hidden":false,"mode":"md"},{"id":721,"value":"function sparkbar(max) {\n return x => htl.html`

${x.toLocaleString(\"en\")}`\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":335,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs"},{"id":76,"value":"lineChart = () => { \n \n return Plot.plot({\n y: {grid:true, label:\"Hours\"},\n x: {label:\"Month\"},\n height: 250,\n width: 600,\n marks: [\n Plot.areaY(byMonth, {x: \"month_of\", y: \"hours\", fill:\"#40C9A2\", opacity:0.2}),\n Plot.line(byMonth, {x: \"month_of\", y: \"hours\", stroke:\"#40C9A2\", strokeWidth:2, tip:\"x\"}),\n Plot.ruleY([0])\n ]\n})\n\n }","pinned":false,"hidden":false,"mode":"ojs"},{"id":205,"value":"barChart = () => { \n return Plot.plot({\n y: {grid:true, label:\"Hours\"},\n x: {label:\"\"},\n height:250,\n width:600,\n marks: [\n Plot.barY(byType, {x: \"fitness_discipline\", y: \"hours\", fill:\"#40C9A2\", tip:\"x\", sort: {x: \"y\", reverse: false}}),\n Plot.ruleY([0])\n ]\n})\n }","pinned":false,"hidden":false,"mode":"ojs"},{"id":479,"value":"## Dynamic HTML\nThis creates the valueBox widgets displayed at the top.","pinned":false,"hidden":false,"mode":"md"},{"id":32,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":460,"value":"## Form Template \nTaken from Martien van Steenbergen. Used to create a two-column form template for filters.","pinned":false,"hidden":false,"mode":"md"},{"id":458,"value":"// Template credit: This layout is updated from Martien van Steenbergen @martien/horizontal-inputs\n\ntemplate = (inputs) => \nhtl.html`

${Object.values(inputs)}

\n`","pinned":true,"hidden":false,"mode":"ojs"},{"id":95,"value":"## CSS\nUse CSS to style dashboard components. Import FontAwesome template from cloudflare to add display icons.","pinned":false,"hidden":false,"mode":"md"},{"id":42,"value":"$31","pinned":true,"hidden":false,"mode":"html"},{"id":14,"value":"## Aggregations","pinned":false,"hidden":false,"mode":"md"},{"id":16,"value":"select \ncount(*) as workouts\n,sum(ride_duration)/60/60 as hours\n,count(distinct created_at) as active_days\n,coalesce(round(sum(case when distance<>'NA' then distance::double end),0),0) as distance\nfrom workouts\nwhere ride_id<>'NA'","pinned":true,"hidden":false,"mode":"sql","output":"totals","database":"var:db"},{"id":69,"value":"select \nmonth::date as month_of\n,coalesce(w.workouts,0) as workouts\n,coalesce(w.active_days,0) as active_days\n,coalesce(w.hours,0) as hours\nfrom months m \nleft join(\n select \ndate_trunc('month', created_at) as month_of\n,count(*) as workouts\n,count(distinct created_at) as active_days\n,sum(ride_duration)/60/60 as hours\nfrom workouts\nwhere ride_id<>'NA'\ngroup by 1\norder by 1 \n) w on w.month_of = m.month::date\nWHERE m.month::date >= ${filters.startDate}::date and m.month::date<= ${filters.endDate}::date\nORDER BY 1 ","pinned":true,"hidden":false,"mode":"sql","output":"byMonth","database":"var:db"},{"id":136,"value":"select \nfitness_discipline\n,count(*) as workouts\n,count(distinct created_at) as active_days\n,sum(ride_duration)/60/60 as hours\nfrom workouts\nwhere ride_id<>'NA'\ngroup by 1\norder by 1 ","pinned":true,"hidden":false,"mode":"sql","output":"byType","database":"var:db"},{"id":237,"value":"select \nw.instructor_name\n,i.image_url\n--,round(avg(avg_difficulty),2) as avg_difficulty\n,round(sum(avg_difficulty*ride_duration)/sum(ride_duration),2) as avg_difficulty\n,count(*) as workouts\n,count(distinct created_at) as active_days\n,sum(ride_duration)/60 as minutes\nfrom workouts w\n left join instructors i on i.id = w.instructor_id\nwhere ride_id<>'NA' and instructor_name <> 'NA'\ngroup by 1,2\norder by 6 desc","pinned":true,"hidden":false,"mode":"sql","output":"byInstructor","database":"var:db"},{"id":10,"value":"## Data Set Up","pinned":false,"hidden":false,"mode":"md"},{"id":12,"value":"db = DuckDBClient.of({\n workouts: filteredWorkouts, \n instructors: instructors,\n months : months\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":6,"value":"instructors = await FileAttachment(\"ref_instructors.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":835,"value":"workouts = await FileAttachment(\"peloton-data@14.csv\").csv({typed: \"auto\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":144,"value":"filteredWorkouts = workouts.filter(w => filters.workoutType.includes(w.fitness_discipline) && w.created_at>=filters.startDate && w.created_at<=filters.endDate)","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}