Use Structure to identify:

  1. Overdue tasks
  2. Number of overdue tasks per epic, initiative, or other larger grouping

  3. Ratio of overdue tasks to total tasks for each epic, initiative, or other large grouping

Step 1: Build Your Structure

In this guide, we're going to show you how to track overdue tasks at the epic level. To track overdue tasks for initiatives or any other large groupings, simply reconfigure the structure hierarchy - this can be done using different Insert, Extend, and Group generators.

To create a new structure:

  1. Go to the Jira menu and select Structure | Create Structure
  2. Add epics: Automation | Insert | JQL Query and enter the following JQL query: issuetype = Epic
    • To limit the epics to specific projects or other variables, add additional specifications (Example: AND project = "My Epics")
  3. Add stories: Automation | Extend | Stories under Epics...
  4. Add sub-tasks (optional): Automation | Extend | Sub-tasks...


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 3: 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 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

In this example, we 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.

Step 4: Calculate the Percentage of Stories That Are Overdue

So far, we've just identified and counted our overdue stories. But this doesn't paint a complete picture. And epic with 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 percentage of stories that are overdue for each higher level

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 we created above

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 Percent column will keep their original calculations, even if those columns are deleted or changed. (This also means if you make changes to the formulas used in the Overdue or Total Stories column, you'll need to re-select those columns in the Variables section of your Percent formula to apply those changes there.)