This article describes what “Defect average lifetime (all priorities)” metric is and how it helps and works

1 What is “Defects average lifetime (all priorities)” metric?
2 Creating a metric
3 Creating a Custom Selection
4 Retrieving required data
5 Getting the result
6 Full code recap

What is “Defects average lifetime (all priorities)” metric?

Defects Average Lifetime (all priorities) shows how much time on average a defect spends in a development cycle.

Defects Average Lifetime (all priorities) helps to monitor, analyze and improve a defect processing flow in a project. It's suggested not to keep a defect open for more than 30 days. It may cause a user's satisfaction decrease and an application overall quality and stability.

Creating a metric

See Custom Metric development for details

Creating a Custom Selection

See Custom Metric development to see how to get to a Custom Selection mode.

Further will be the explanation of the code you should put in the “PerfQL” field

Retrieving required data

select 
       done_date::date - created::date as difference
  from Ticket
 where lower(type) in ('bug', 'sub-bug')
   and lower(status) in ('done', 'closed', 'resolved')
   and lower(resolution) not in ('rejected', 'cannot reproduce', 'functioning as designed', 'function as intended')
   and done_date between date_trunc('day', now()) - interval '90 day' 
 				             and date_trunc('day', now())

To get all needed data we need to filter it by type, status (to exclude invalid bugs) and by date of completion (we retrieve all completed bugs for the last 90 days). Then we calculate the lifetime of every bug: we find the difference between date of creation and date of completion. The result is shown below.

Example of the output:

Getting the result

After all differences are defined we need to calculate average lifetime using avg() function

Full code recap

--Defect average lifetime all priorities
with a as (
select 
       done_date::date - created::date as difference
  from Ticket
 where lower(type) in ('bug', 'sub-bug')
   and lower(status) in ('done', 'closed', 'resolved')
   and lower(resolution) not in ('rejected', 'cannot reproduce', 'functioning as designed', 'function as intended')
   and done_date between date_trunc('day', now()) - interval '90 day' 
 				             and date_trunc('day', now())
                      )
select 
 'all' as "Defects average lifetime" , 
       coalesce(round(avg(a.difference)), 0) as average 
  from a