Open Issue Aging reveals the duration of an issue stay in backlog (by issue type) before it's taken into work. In other words, it shows an "age" of items in "To Do" state (bucket).
How metric helps
Open Issue Aging helps to ensure that a team backlog remains fresh and relevant. It also allows to identify and purge obsolete issues.
How metric works
Chart overview
Open Issue Aging is measured in days. Chart shows for now a number of items by type that spent from 1 to >60 days. By click on series a drill down opens which lists the issues included into calculation.
Calculation
Open Issue Aging is calculated for the issues that are in To Do status bucket per Project Configuration.
Open Issue Aging = <Today's date> - <Date created for the issue>
PerfQL
WITH ticket_scope AS (
SELECT id, url, key, priority, summary, type,
CURRENT_DATE - created::date AS age
FROM ticket
WHERE parent_task is null
AND is_to_do(id)
),
result AS (
SELECT type,
COUNT(CASE WHEN age BETWEEN 0 AND 7 THEN 1 END) AS "0-7 days",
COUNT(CASE WHEN age BETWEEN 8 AND 15 THEN 1 END) AS "8-15 days",
COUNT(CASE WHEN age BETWEEN 16 AND 30 THEN 1 END) AS "16-30 days",
COUNT(CASE WHEN age BETWEEN 31 AND 60 THEN 1 END) AS "31-60 days",
COUNT(CASE WHEN age > 60 THEN 1 END) AS "> 60 days"
FROM ticket_scope
GROUP BY type
)
SELECT *
FROM result;
----DRILL DOWN----
0 - 7 days
select
case
when url is not null then '[' || key || '](' || url || ')'
else key
end as "Issue Id",
type as "Type",
priority as "Priority",
summary as "Summary",
age as "Issue aging, days"
FROM
ticket_scope s
WHERE
type = clicked_x_value
AND age BETWEEN 0 AND 7
ORDER BY
age,
key;
8 - 15 days
select
case
when url is not null then '[' || key || '](' || url || ')'
else key
end as "Issue Id",
type as "Type",
priority as "Priority",
summary as "Summary",
age as "Issue aging, days"
FROM
ticket_scope s
WHERE
type = clicked_x_value
AND age BETWEEN 8 AND 15
ORDER BY
age,
key;
16 - 30 days
31 - 60 days
>60 days
Data Source
Data for the metric can be collected from a task tracking system (Jira, TFS/VSTS, Rally, etc.)
