Lead & Cycle Time (Custom)

1 Context
2 Description
3 Configuration
4 Code
5 Drill-down
6 How to interpret this metric & next steps

Context

Standard Lead and Cycle Time metric works excellently in most cases. But what if you want to track not the whole project scope (want to apply additional filtering), or you have many reopens & you need to track time spent on it, or you want to exclude weekends from cycle time duration? In such cases you may want to create a custom metric specifically for your needs. Here is an example of how to do it.

Description

This metric is based on Task Tracking System's (Jira, Azure Boards, or Rally) data.
Differences between custom & standard Lead and Cycle Time metric:
1. You can apply additional filtering for scope of calculation. For example, you want to exclude Epics or tickets closed with invalid resolutions.
2. In case ticket was closed & then re-opened, standard metric takes into account only what happened since the latest re-opening. The following metric has 2 re-open calculation logic, which helps to include all time spent on the ticket. See the difference between re-open calculation logic in the next section.
3. Metric provides 2 types of cycle time - standard cycle time & cycle time (excluding weekends). Example: Ticket put into work on Friday 1 PM and finished on Tuesday 3 PM. The difference is 4 days and 2 hours.
  1. Cycle time = 4 days, 2 hours
  2. Cycle time (excluding weekends) = 2 days, 2 hours

Configuration

We have 6 variables in the code of this metric:

Number of Intervals to Show. Number of time intervals (weeks, months or quarters) shown on the metric. Default: 12
Interval Type. Weeks, months or quarters. Default: Months
Reopen Logic.
- 1 = All time goes into last completed interval. Example: Ticket was created in Jan 2023 and closed in Feb 2023, then it was re-opened in Mar 2023 and finally closed in Apr 2023. Based on reopen logic #1, lead time will be reflected in Apr 2023 interval as the time between creation & final closure, cycle time will be sum of all time spent in active development & testing statuses from creation to closure.
- 2 = Time goes into each completed interval. Example: Ticket was created in Jan 2023 and closed in Feb 2023, then it was re-opened in Mar 2023 and finally closed in Apr 2023. Based on reopen logic #2, lead time will be reflected in Feb 2023 interval as the time between creation & first closure, one more lead time will be reflected in Apr 2023 interval as the time between re-opening in Mar 2023 & final closure. Cycle time will be split the same way.
In Progress Statuses. The list of active development statuses for Cycle Time calculation. Default: In Progress, Code Review, In Testing, Ready for Testing, In Development.
Done Statuses. The list of statuses is considered completed. Default: Done, Closed.
Scope. Scope for tracking. Default: All tickets.

Code

with number_of_intervals_to_show as
(
  select 12 --how many time intervals to show on the chart
  as show
),
   
interval_type as
(
  select 2 --1=week, 2=month, 3=quarter
  as i_type
),
 
reopen_logic as
(
select 2 --1=all time goes into last completed interval, 2=time goes into each completed interval
as rl
),
 
in_progress_statuses as --in progress statuses
(
select 'In Progress' as st
union select 'Code Review' as st
union select 'In Testing' as st
union select 'Ready for Testing' as st
union select 'In Development' as st
),
  
done_statuses as --statuses considered closed/done/completed
(
select 'Closed' as dst
union select 'Done' as dst
),
  
interval as --generating intervals
(
select * from
  (
select
  (date_trunc('week', now()) - interval '7' day* generate_series(0,(select show from number_of_intervals_to_show)-1))+interval '1 week' - interval '1 day' - interval '1 second' as i_finish,
  (date_trunc('week', now()) - interval '7' day* generate_series(0,(select show from number_of_intervals_to_show)-1)) - interval '1 day' as i_start,
  1 as type
union
select
  (date_trunc('month', now()) - interval '1' month* generate_series(0,(select show from number_of_intervals_to_show)-1))+interval '1 month' - interval '1 second' as i_finish,
  (date_trunc('month', now()) - interval '1' month* generate_series(0,(select show from number_of_intervals_to_show)-1)) as i_start,
  2 as type
union
select
  (date_trunc('quarter', now()) - interval '3' month* generate_series(0,(select show from number_of_intervals_to_show)-1))+interval '3 month' - interval '1 second' as i_finish,
  (date_trunc('quarter', now()) - interval '3' month* generate_series(0,(select show from number_of_intervals_to_show)-1)) as i_start,
  3 as type
) as temp
  where type=(select i_type from interval_type)
),
 
scope as --scope for calculation
(
 select *
  from ticket
  where (done_date is null or done_date>=(select min(i_start) from interval)) --below is an example of additional filtering that can be applied:
    --and (resolution is null or resolution not in ('Expired','Cannot Reproduce','Duplicate','Functions as intended'))
    --and type not in ('Epic')
),
 
status_history as --history of status changes
(
select b.key, b.summary, b.type, b.priority, b.done_date, b.created,
       a.start, a.status,
       ROW_NUMBER() OVER (PARTITION BY b.key ORDER BY a.start ASC) history_id
from tickethistory a
join scope b on a.workitem_id=b.id
  where field=0
),
 
reopen_history as --history of reopens
(
select  a.key, a.start, b.start as reopen
from status_history a
join status_history b on a.key=b.key and a.history_id+1=b.history_id
 and lower(b.status) not in (select lower(dst) from done_statuses)
where lower(a.status) in (select lower(dst) from done_statuses)
),
 
last_closure as --last closure within interval
(
select
    i.i_start, i.i_finish, a.key,
    max(a.start) as last_done
from interval i
join status_history a on lower(a.status) in (select lower(dst) from done_statuses) and a.start between i_start and i_finish
group by i.i_start, i.i_finish, a.key
),
 
last_reopen_before as --last reopen before interval
(
select
    i.i_start, i.i_finish,
    a.key,
    max(a.reopen) as last_reopen_before_interval
from interval i
join reopen_history a on a.start<=i_start
  group by i.i_start, i.i_finish, a.key
),
   
cycle_time_logic1 as --cycle time calculation for reopen logic 1
(
select
    i.i_start, i.i_finish,
    s.key, s.summary, s.type, s.priority, s.created, s.done_date,
    a.status, a.start as start, b.start as finish,
    DATE_PART('day', b.start-a.start)+DATE_PART('hour', b.start-a.start)/24.0+DATE_PART('minute', b.start-a.start)/1440.0 as duration
from interval i
join scope s on s.done_date between i.i_start and i.i_finish
left join status_history a on s.key=a.key and lower(a.status) in (select lower(st) from in_progress_statuses)
left join status_history b on s.key=b.key and a.history_id+1=b.history_id
),
 
lead_cycle_time_logic2 as --lead & cycle time calculation for reopen logic 2
(
select
    c.i_start, c.i_finish,
    a.key, a.summary, a.type, a.priority,
    c.last_done, coalesce(d.last_reopen_before_interval,a.created) as created_or_reopen, a.created,
    a.status, a.start as start, b.start as finish,
    case when a.start<d.last_reopen_before_interval or lower(a.status) not in (select lower(st) from in_progress_statuses)
    then 0
    else DATE_PART('day', b.start-a.start)+DATE_PART('hour', b.start-a.start)/24.0+DATE_PART('minute', b.start-a.start)/1440.0
    end as cycle_duration,
    case when a.start<d.last_reopen_before_interval
    then 0
    else DATE_PART('day', b.start-a.start)+DATE_PART('hour', b.start-a.start)/24.0+DATE_PART('minute', b.start-a.start)/1440.0
    end as lead_duration
  from status_history a
join status_history b on a.key=b.key and a.history_id+1=b.history_id
join last_closure c on a.key=c.key and b.start<=c.last_done
left join last_reopen_before d on a.key=d.key and c.i_start=d.i_start
),
 
duration_without_weekend_logic1 as --excluding weekends for reopen logic 1
(
select  *,
    (SELECT (count(*)-1)+(duration::decimal % 1) AS duration_without_weekend
FROM   generate_series(start
                     , start + interval '1' day*floor(duration)
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6)
from cycle_time_logic1
),
 
duration_without_weekend_logic2 as --excluding weekends for reopen logic 2
(
select  *,
  case when cycle_duration>0 then
    (SELECT (count(*)-1)+(cycle_duration::decimal % 1)
FROM   generate_series(start
                     , start + interval '1' day*floor(cycle_duration)
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6)
  else 0 end AS duration_without_weekend
from lead_cycle_time_logic2
),
 
final_calculations_tickets as --final calculations for tickets (reopen logic 1 & 2)
(
select  1 as logic,
        case when (select i_type from interval_type)=1 then to_char(i.i_start,'YYYY-MM-DD')    --format for weeks
          when (select i_type from interval_type)=2 then to_char(i.i_start,'YYYY-MM') --format for months
          else to_char(i.i_start,'YYYY "Q"Q') end as i_start, --format for quarters
        key, summary, a.type, priority, created as created_or_reopen, done_date as last_done,
        greatest(0,DATE_PART('day', done_date-created)+DATE_PART('hour', done_date-created)/24.0+DATE_PART('minute', done_date-created)/1440.0) as lead_time,
        sum(greatest(0,duration_without_weekend)) as cycle_time_without_weekends,
        sum(greatest(0,duration)) as cycle_time
from interval i
left join duration_without_weekend_logic1 a on i.i_start=a.i_start
group by logic,i.i_start, key, summary, a.type, priority, created, done_date
union
select  2 as logic,
        case when (select i_type from interval_type)=1 then to_char(i.i_start,'YYYY-MM-DD')    --format for weeks
          when (select i_type from interval_type)=2 then to_char(i.i_start,'YYYY-MM') --format for months
          else to_char(i.i_start,'YYYY "Q"Q') end as i_start, --format for quarters
        key, summary, a.type, priority, created_or_reopen, last_done,
        sum(greatest(0,lead_duration)) as lead_time,
        sum(greatest(0,duration_without_weekend)) as cycle_time_without_weekends,
        sum(greatest(0,cycle_duration)) as cycle_time
from interval i
left join duration_without_weekend_logic2 a on i.i_start=a.i_start
group by logic,i.i_start, key, summary, a.type, priority, last_done, created_or_reopen 
)
 
select  i_start::varchar as timeline,
        avg(lead_time) as "Lead Time",
        avg(greatest(0,cycle_time)) as "Cycle Time",
        avg(greatest(0,cycle_time_without_weekends)) as "Cycle Time (excluding weekends)"
from final_calculations_tickets
where logic=(select rl from reopen_logic)
group by i_start
order by i_start asc

Drill-down

Drill-down query to show the ticket's attributes & results of calculation for a clicked interval:

select  key, summary,
        round(lead_time::decimal,2) as "Lead Time",
        round(cycle_time_without_weekends::decimal,2) as "Cycle Time (without weekends)",
        round(cycle_time::decimal,2) as "Cycle Time",
        type, priority, created_or_reopen, last_done
from
(
  select *
from final_calculations_tickets
where logic = (select rl from reopen_logic)
) as temp
where i_start=clicked_x_value 
order by cycle_time_without_weekends desc

How to interpret this metric & next steps

Please read the information on how to interpret this metric here.