Situation

You want to easily identify:

  1. Overdue tasks
  2. Number of overdue tasks per epic
  3. Ratio of overdue tasks to total tasks for each epic

Solution

All of these can be accomplished by adding a few simple formula columns.

Step 1: Build Your Structure

Before creating any formula columns, we recommend organizing your structure into a hierarchy. While this isn't strictly necessary for identifying overdue tasks, it's extremely useful - and essential for aggregating overdue tasks at the epic level or higher.

If you're not familiar with how to build automated structures, we suggest that you start by reading Building a Structure with Automation.

Step 2: Mark Overdue Tasks

Once you have your structure in place, the next step is to identify all the overdue items. To do this, we need to add a formula column. Click the + button to the right of the column headers, and select Formula. 


Give the column an appropriate name and enter the following into the Formula section: if(due_date < today(); "overdue")

If you used "due_date" in your formula, the variable will automatically be mapped to the Due Date attribute. If you used another name for that variable (for example, “due”), you will need to define the variable by pointing it to the appropriate Due Date attribute.

Variations

This is just one way to mark overdue items. You could also:

  • Create a more visual warning that highlights not only overdue items, but also items coming due. You can read how to do this in Wiki Markup in Formula Columns.
  • Create a formula that relies on another due date.  For example, you may want to flag issues that aren't completed by the due date of their parent epic. In this case, change the formula to: if(type = "Story" and status != "Done" and parent{due_date} < today(); "overdue")
  • Aggregate overdue items for epics (we'll cover this next!)

Step 2: Aggregate Overdue Items for Epics, Initiatives, etc.

In the previous example we were simply returning the string “overdue”, but if we give that "overdue" flag a numeric value ("1"), we can then use the Sum over sub-items option to aggregate those values up the hierarchy. To accomplish this:

  1. In place of the formula we created in step 1, enter the following: if(type = "Story" and status != "Done" and due_date < today(); 1)
  2. Check the Sum over sub-items box

Notice that in this example, we have several issue types above the epic level. The overdue numbers represent the number of overdue stories beneath each of these, aggregating at each level - so "Upgrade Website" has 3 overdue items, one from the stories under "Update Apache web server" and two from the stories under "Test website performance".

In this example, we've limited our scope to only stories that are not yet "Done" - depending on your situation, you may want to adjust this to include other issue types, add additional qualifications, etc.

We could have just as easily added a "Team" level here, to identify overdue items by team - or any other custom field we want to track by!

Step 4: Calculate the Ration of Overdue Tasks to All Tasks

So far, we've just identified and counted our overdue stories. But this doesn't paint a complete picture. 1 overdue item out of 257 is very different from 1 out of 5! 

To get a better understanding of how these overdue items relate to the big picture, next we're going to:

  • Calculate total stories for each higher level in our hierarchy
  • Calculate the ratio of overdue stories to total stories

First, we need to create another Formula column, which we'll use to calculate our total stories. For this column:

  • Enter the following formula: if(type = "Story"; 1)
  • Check the Sum over sub-item box

Next, we'll create a third formula column to compare our values from the first two. For this column:

  1. Enter the formula: if(total_stories; overdue / total_stories)
  2. Map your "total_stories" and "overdue" variables to the appropriate columns


For this formula, we wrapped the expression in an if statement so we wouldn't get a DIV/0 error on lines without a value for total stories.


At this point, you can keep all three columns in your structure (we recommend saving them as a new view), or get rid of one or both of your original columns - our ratio column will keep their original calculations, even if those columns are deleted or changed. (This also means if you make changes to our Overdue or Total Stories column, you'll need to re-select those columns in the Variables section of your Overdue Ratio formula to apply those changes there.)