Page Comparison

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

...

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")

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.

...

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;

...

1.  overdue /

...

1.  total_stories)

2. Map your "total_stories" and "overdue" variables to the appropriate columns we created above

...

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.)