This article describes what “Bug growth by sprints” metric is and how it helps and works

What is “Bug growth by months” metric?

Bug Growth shows a number of Fixed defects vs. number of Logged defects for a time frame (month, sprint and so on). This view shows a trend of submitted defects exceeds the trend of defects being fixed - to understand if a team can timely manage the quality debt

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 of bugs data

bugs as (
    select 
            key as item,
            created as created,
            done_date as d_date,
            resolved as rslv_date
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
    )

Here we retrieve all bugs related data filtering data in Ticket table by equality condition of type-column to ‘Bug' or 'Sub-bug’

Example of the output:

Data aggregation and grouping

To get required result we need to join logged bugs with fixed bugs. To do so we need to truncate every creation date and done_date to month and join bugs by equality condition of these dates. Then we count unique values of item-column shown in the output above and extract year and month from every creation date and done_date to group counted values by these dates

Full code recap

with   
    bugs as (
    select 
            key as item,
            created as created,
            done_date as d_date,
            resolved as rslv_date
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
    )
      
    select 
            coalesce(date_trunc('month', logged.created),   
                     date_trunc('month', fixed.d_date)
                    ) 					               as dts,  --dates for sorting                                 
    		coalesce(to_char(logged.created, 'yyyy Mon'),   
                     to_char(fixed.d_date,   'yyyy Mon')
                    )                                   as year_and_month,
                     
            count(distinct logged.item)                 as logged_items,
            count(distinct fixed.item)                  as fixed_items
     from   bugs logged
     full   join
            bugs fixed
       on   date_trunc('month', logged.created) = date_trunc('month', fixed.d_date)
    where   logged.created is not null 
       or   fixed.d_date   is not null
    group   by year_and_month, dts
    order   by dts desc

Example of the output: