Creating a Burndown Chart in Airtable
8
3740
0
Happy 2021 everyone! A new year, brings new projects to manage. And where would project managers be without their handy burndown charts? Well, with a bit of scripting and the vega-lite app, you can now make a burndown chart in Airtable!
For the uninitiated (aka me until two weeks ago), burndown charts “show the total effort against the amount of work for each iteration.” Meaning, they help you answer the question “are we completing our tasks on schedule?” They usually look something like this:
The line represents the planned tasks remaining, and the bars represent actual tasks remaining. if your bars are below the line that means you’re getting things done on time. The X axis can be grouped by the cadence in which you’re tracking - which could be days, weeks, sprints, etc.
Until recently, we couldn’t create burndown charts in Airtable because the native chart app doesn’t support combo/multi-field charts. But now with the Vega-Lite App (in beta), we have much more control over our charts and can achieve the elusive burndown!
To create a burndown chart, you need to know a couple things :
- The length of your project
- The intervals of time you’re tracking (months, weeks, days, etc.)
- The amount of work planned in each interval
- The amount of work completed in each interval
If you already have a list of tasks, you probably have all of this information in the following date fields in your Tasks table:
- Planned Task Start Date
- Planned Task End Date
- Actual Task Completion Date
The tricky part about burndown charts are their cumulativeness, and because of this we’re actually going to need to create a new table called Burndown with the following fields.
- Interval (Date)
- Planned Open Tasks (Number)
- Actual Open Tasks (Number)
Using the below script, we can use the fields from our Tasks table to calculate the information needed for our burndown chart and table. Additionally, the script outlines the code you’ll need to paste into the Vega-Lite app to render a burndown chart with helpful tooltips. This script uses script settings, so you should just be able to plug-and-play :crossed_fingers:t5:
/*
WHAT THIS SCRIPT "SHOULD" DO: Take a table of tasks with deadlines and calculate how many open tasks are available each interval by:
If Starting from Scratch:
1. Calculate the number of intervals between the Start Date (End date of First Task) and the
Max Date (End Date of last task), and a start date for each interval
2. Create Records for each interval in the burndown table
3. For each interval, calculate the cumulative number tasks expected to be open by that specific date. For example,
let's say your project runs from January to June. You expect the following open tasks for each month:
Jan: 600,
Feb: 500,
Mar: 400,
Apr: 300,
May: 200,
Jun: 100
The 100 tasks in June should be counted in each previous month. To get this number, we'll want to iterate through
intervals, and get the number of tasks that are due after the current time interval.
4. For each interval, calculate the cumulative number tasks that were actually open at the time of thtat interval.
To do this, we'll need to filter by the "Datet Completed" find when a task was open until.
If Updating:
- I'm not trying to think about this now
*/
// Settings - use the settinigs panel to configure
let settings = input.config({
title: 'Burndown Calculation',
description: 'This script will help you calculate the values needed to plot a burndown chart on the vega lite app. Your output table should have a linked field to the input table, and two number fields to calulate the # of tasks',
items:[
input.config.select('cadence',{
label:'Chart Cadence/Interval',
description: "Select the interval by which you'd like to track burndown",
options: [
{label: 'Daily', value: "1"},
{label: 'Weekly', value: "7"},
{label: 'Monthly', value: "30"},
{label: 'Quarterly', value:"90"}
]
}),
input.config.table('inputTable',{
label: 'Input Table',
description: "The table where you're tracking tasks and dates",
}),
input.config.table('outputTable',{
label: 'Output Table',
description: "The table that will hold the figures for the burndown chart",
}),
input.config.field('inputEndDate',{
parentTable: 'inputTable',
label: 'End Date',
description: "The field that is tracking dates in your input table"
}),
input.config.field('completedDate',{
parentTable: 'inputTable',
label: 'Completed Date',
description: "The field that tracks when a task was completed"
}),
input.config.field('intervalDate', {
parentTable: 'outputTable',
label: 'Interval Date',
description: 'Field where date of Interval will be stored'
}),
// input.config.field('tasksClosed', {
// parentTable: 'outputTable',
// label: 'Tasks Closed This Interval',
// description: 'Capture the number of tasks closed in the currrent interval'
// }),
input.config.field('plannedOpen', {
parentTable: 'outputTable',
label: 'Planned Open Tasks',
description: "Field where you'll capture the # of tasks that should be open for the week"
}),
input.config.field('actualOpen', {
parentTable: 'outputTable',
label: 'Actual Open Tasks',
description: "Field where you'll capture the current # of open tasks"
})
]
})
// 🍀 Load Input Table Records
let inputTableQuery = await settings.inputTable.selectRecordsAsync({fields:[settings.completedDate, settings.inputEndDate]})
let inputRecords = inputTableQuery.records
// 🍀 Later I'll figure out how to make thtis update friendly, right now I'm deleting everything in thte input table
let outputTableQuery = await settings.outputTable.selectRecordsAsync()
await batchAnd('Delete',settings.outputTable, outputTableQuery.records)
// 🍀 Find the number of intervals by getting the # of days between today/maxDate and dividing by the cadence.
let startDate = new Date(Math.min.apply(null,inputRecords.filter(a => a.getCellValue(settings.inputEndDate)).map( r => new Date(r.getCellValue(settings.inputEndDate)))))
let maxDate = new Date(Math.max.apply(null,inputRecords.map( r => new Date(r.getCellValue(settings.inputEndDate)))))
let diffTime = Math.abs(startDate - maxDate)
let diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
let intervals = parseInt(diffDays/parseInt(settings.cadence))
// 🍀 Get dates for each interval
let create = []
while (create.length < intervals){
startDate = addDays(startDate,parseInt(settings.cadence))
create.push(startDate)
}
//🍀 Calculate Planned/Actual Open Records for each Interval
create = create.map(a => ({fields:{
[settings.intervalDate.id]: a,
[settings.plannedOpen.id]: inputRecords.filter( r => new Date(r.getCellValue(settings.inputEndDate)) > a).length,
[settings.actualOpen.id]: a > new Date() ? 0 : inputRecords.filter( r => new Date(r.getCellValue(settings.completedDate)) > a ).length + inputRecords.filter(r => r.getCellValue(settings.completedDate) == null).length
}
}))
await batchAnd('Create',settings.outputTable,create)
let vegaLiteOutput =
`
{
"$schema": "https://vega.github.io/schema/vega-lite/v4.json",
"title": "Functional Workplans",
"width": "container",
"height": "container",
"layer": [
{
"mark": {"type": "bar", "tooltip": true},
"encoding": {
"x": {
"field": "${settings.intervalDate.name}",
"type": "temporal"
},
"y": {
"field": "${settings.actualOpen.name}",
"type": "quantitative"
}
}
},
{
"mark": {"type": "line", "point": true, "tooltip": true},
"encoding": {
"x": {
"field": "${settings.intervalDate.name}",
"type": "temporal"
},
"y": {
"field": "${settings.plannedOpen.name}",
"type": "quantitative"
},
"color": {
"value": "red"
}
}
}
]
}`
output.markdown(`## Paste the below snippet into the Vega Lite Editor`)
output.markdown('```'+vegaLiteOutput)
// Add Days function from StackOverflow
// Source: https://stackoverflow.com/questions/563406/add-days-to-javascript-date
function addDays(date, days) {
var result = new Date(date);
result.setDate(result.getDate() + days);
return result;
}
/*
Author: Jeremy Oglesby
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.
::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()
table = Table; the table the action will be performed in
records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform
::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;
switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;
case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
Some Notes/Things people can still do and I’d be grateful because I don’t know when I’ll have the time to revisit this:
- Right now, you have to press run on the script every time you want to update the table.
- This script is not optimized to calculate pre-determined sprint intervals, although, I’d love to see that.
- If you’d like to better format your dates - check out Vega-Lite’s documentation on Axis formatting and date formatting
- I think there’s a world in which we can accomplish a burndown without a second table or a script using a combination of Vega-Lite’s Pivot and Filtering options, but my brain is NOT doing that right now.
Okay let me know how it goes and happy new year!
