This article describes what “Probability rates of completion Scope for Burn-up/Burn-down” metric is and how it helps and works

Table of Contents

minLevel	1
maxLevel	1

Creating a metric

See Custom Metric development for details

...

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

Introduction

We want to build a metric that is capable of predicting the probability of scope completion in several future iterations. In other words if we have two curves describing growth of the scope and the amount of tickets in “done“ status we want to know the probability of crossing these curves in the future.

...

To do so we will use the data about scope and done curves for every iteration in the past and normal distribution law as one of the most widespread law for many processes in the world. Now let’s explore the sql-query calculating required probabilities.

Fill the required parameters

...

Code Block

breakoutMode	wide
language	sql

variables as (
select 
        'tickets' as calculate_by,      -- choose the basement of calculation: tickets or story points 
        7    as iteration_duration,     --in days
        20   as iterations_to_analyze,
  		20   as future_iterations_num,  --number of iterations to make forecast for
  	    'no' as scope_change,           --yes/no
  		200  as division_step           --must be an even number!
),

types as (
select  a.* from (values ('bug'), ('task')) a (ticket_type) -- add ticket types to make forecast
)

...

calculate_by: metric calculation can be based on two types of data - tickets and story points. If there’s a need to make forecast by tickets type ‘tickets(exactly!) ‘t' before “as calculate_by“ (third row in the code snippet above). If it’s not - type any other word/symbol in quotation marks instead.
iteration_duration:the timeline in the metric is divided into weeks, so this parameter is set to 7 days and there’s no need to change it.
iterations_to_analyze: type here a number of previous iterations including current one to use their data in the analysis.
future_iterations_num: the number of iterations to be shown in the chart with related probabilities.
scope_change: this parameter defines if we add tickets or story points to the scope in the future or not. If not - type 'no' before “scope_change“, else - type any other word/symbol in quotation marks before “scope_change“ instead.
division_step: this parameter helps to change the accuracy of calculations. The higher the number, the higher accuracy we can get. Also this number must be even and shouldn’t be too high to slow code down too much (for our purpose to get two decimals after floating point 200 is enough).
ticket_type: this is the list of types defining what types the tickets we retrieve with our query must have retrieve with our query must have. To add new ticket type to the calculation type it in single quotes and parentheses in “values“ clause. Parts of “values“ clause must be separated by commas.

Start date

Code Block

language	sql

start_date_ as (
select date_trunc('week', current_date)::date 
- 
(select iterations_to_analyze*iteration_duration from variables) as start_date
)

...

Code Block

breakoutMode	wide
language	sql

disp_in_disp_out as (
select 
        day_,
        scope,
        done,  
        added_in,
  		velocity,
        AverageIn,
        
        (sum(case _in_disp_out as (
select 
        day_,
      when  scope,
        done,  
    n <= 0 
                then 
                (added_in - AverageIn)*(added_in - AverageIn),
  		velocity,
        AverageIn,
     end   coalesce(
           ) over ()(sum(case 
            when 
 )           n    <= 0 /
            then 
  (select -n-1 from iteration_end_dates_ order by n limit 1) as disp(added_in, --sigma(2)
  AverageIn)*(added_in - AverageIn)
      AverageOut,  end
        ) over ()
    (sum(case            )/nullif((select -n-1 from iteration_end_dates_ order by whenn limit 1), 0),
        0) as disp_in, --sigma(2)
  n <= 0    AverageOut,
        coalesce(
    then    (sum(case 
            (velocitywhen -
AverageOut)*(velocity - AverageOut)          n <= 0 
 end           then 
   ) over ()       (velocity - AverageOut)*(velocity - AverageOut)
     )   end 
        ) over ()
 /                 )/nullif((select -n-1 from iteration_end_dates_ order by n limit 1), 0),
        0) as disp_out,
        n

from avg_in_avg_out
)

Example of the output:

...

Expand

Code Block

language	sql

with

variables as (
 select 
        'ticketst'  as calculate_by,      -- choose the basement of calculation: tickets or story points
        7    as iteration_duration,     --in days
        2010   as iterations_to_analyze,
  		    20   as future_iterations_num,  --number of iterations to make forecast for
  	    'no'  as scope_change,           --yes/no ()
  		    200  as division_step           --must be an even number!
),

types as (
select  a.* from (values ('bug'), ('task')) a (ticket_type) -- add ticket types to make forecast
),

start_date_ as (
select date_trunc('week', current_date)::date 
- 
(select iterations_to_analyze*iteration_duration from variables) as start_date
),

iteration_days as (
select  generate_series(a.min_d, 
                        date_trunc('week', current_date)::date + a.iteration_duration*a.future_iterations_num,
                        '1 day'
                        ) 
    as  day_
  from  (
select 
        sd.start_date::date  as min_d,
      	v.iteration_duration,
      	v.future_iterations_num
  from  start_date_ sd,
      	variables v
    )   a
),

buckets as (
select  a.* from (values ('scope'), ('done')) a (bucket)
),

dates_buckets as (
 select 
        itd.day_, b.bucket
   from iteration_days itd, buckets b
),

ticket_keys_created as (
 select 
        t.id,
        t.created,
        t.type,
        t.status,
        t.story_points,
        'scope' as category
   from 
        ticket t
  where
        lower(t.type) in (select ticket_type from types)
),

ticket_keys_done as (
 select 
        t.id,
        t.type,
        t.status,
        t.done_date,
        t.story_points,
        'done' as category
   from 
        ticket t 
  where 
        lower(t.status) in ('closed')
    and lower(t.type) in (select ticket_type from types)
    and t.done_date >= (select sd.start_date::date from start_date_ sd)
),

intermediate_result as (
select 

        s.day_::timestamp,
        count(tkc.id)         as scope_tickets,
        count(tkd.id)         as done_tickets,
        sum(tkc.story_points) as scope_story_points,
        sum(tkd.story_points) as done_story_points,

        (s.day_::date - (select sd.start_date::date from start_date_ sd))
        /
         sd))/(select sd.iteration_duration from variables sd)+1 as iteration_n, --also a week number or an iteration number
     	 
        ((s.day_::date - (select sd.start_date::date from start_date_ sd))
        /
        (select v.iteration_duration from variables v)+1)
        -
        ((current_date - (select sd.start_date::date from start_date_ sd))
        /
        (select v.iteration_duration from variables v)+1) as "N"
           
   from 
        dates_buckets s
   left join 
        ticket_keys_created tkc
     on s.day_ between tkc.created and now()
    and s.bucket = tkc.category
   left join
        ticket_keys_done tkd
     on s.day_ between tkd.done_date and now()
    and s.bucket = tkd.category
  group by s.day_
  order by s.day_
  ),

iteration_end_dates_ as (
select 
        day_, 
        max(day_) over (partition by iteration_n) as sprint_end_date,
        case 
            when (select v.calculate_by from variables v) = 'ticketst'
            then scope_tickets
            else scope_story_points
        end as scope,
    
        case 
            when (select v.calculate_by from variables v) = 'ticketst'
            then done_tickets
            else done_story_points
        end as done,
        n
   from intermediate_result
  where n >= - (select v.iterations_to_analyze from variables v)
),

added_tickets_velocity_tickets as (    
select 
        day_,
        scope,
        done,
  		case 
            when n<=0 
            then done - (lag(done) over (order by n)) 
         end  as velocity,
  		 case 	
  			when 
                 (select v.scope_change from variables v) = 'no'
  			then  0
  			else 
               		case 
                    when n<=0 
                    then scope - (lag(scope) over (order by n)) 
                 end 
  		  end  as added_in,
        n
   from iteration_end_dates_
  where day_ = case n when 0 then current_date else sprint_end_date end
),

avg_in_avg_out as (    
select 
        day_,
        scope,
        done,
		added_in,
  	    velocity,
  	    avg(added_in) over () as AverageIn,
        avg(velocity) over () as AverageOut,
        n
  from  
        added_tickets_velocity_tickets
),

disp_in_disp_out as (
select 
        day_,
        scope,
        done,  
        added_in,
  		velocity,
        AverageIn,
        coalesce(
        (sum(case 
            when 
            n <= 0 
            then 
            (added_in - AverageIn)*(added_in - AverageIn)
        end
        ) over ()
                )/nullif((select -n-1 from iteration_end_dates_ order by n limit 1dates_ order by n limit 1), 0),
        0) as disp_in, --sigma(2)
        AverageOut,
        coalesce(
        (sum(case 
            when 
            n <= 0 
            then 
            (velocity - AverageOut)*(velocity - AverageOut)
        end 
        ) over ()
                )/nullif((select -n-1 from iteration_end_dates_ order by n limit 1), 0),
        0) as disp_out,
n,  (select -n-1 from iteration_end_dates_ order by n limit 1) as delimeter

from avg_in_avg_out
),

meanX_standdevX as (
select 
        day_,
        scope,
        done,
        case 
            when n = 0
            then scope - done
  		      when n > 0
  		      then lag(scope - done, n) over (order by n)
        end as work_left,
        n,
        (case 
                when n>= 0
                then n
        end)*(AverageOut - AverageIn) as me,
        
        sqrt(
            (case 
                when n>= 0
                then n
            end)*(disp_in + disp_out)
            ) as sd
  from 
        disp_in_disp_out
),

points as (
select 
        day_,
        scope,
        done,
        n, 
        work_left,
        me,
        sd,
        generate_series((work_left)::numeric,
                        (me+4*sd)::numeric,
                        (me+4*sd - work_left)::numeric
                        /
                        (select v.division_step from variables v)
                       
                       ) as points,
        
        (me+4*sd - work_left)::numeric/(select v.division_step from variables v) as h
   from 
        meanX_standdevX 
  where n > 0
  ),
    
normal_dist_values as (    
select
        day_,
        scope,
        done,
        n, 
        work_left,
        me,
        sd,
        points,
        h,
        dense_rank() over (partition by n order by points) as index,
        case 
            when h > 0
            then
            1::numeric/(sd*sqrt(2*pi())) * round(cast(exp(-power((points - me), 2)/(2*power(sd, 2))) as numeric), 4)
  		    else 0
  		 end as normal_dist_value
  from  points
  ),
 
simpson_rule as (
select  
        day_,
        scope,
        done,
        n, 
        work_left,
        me,
        sd,
        points,
        h,
        index,
        case 
                when index = 1 or index = (select v.division_step from variables v)+1
                then normal_dist_value
                when mod(index, 2) = 0
                then normal_dist_value*2
                when mod(index, 2) = 1
                then normal_dist_value*4
           end  as simpson_values
from
normal_dist_values
  )

select 
            to_char(day_, 'yyyy')||' - '||todate_charpart('week', day_, 'ww') as week_number,
      round((max(h)::numeric/3)*sum(simpson_values)::numeric, 2) as probability
 from simpson_rule
group by day_
order by day_

...

Version	Old Version 3	New Version 4
Changes made by	Aleksandr (Unlicensed)	Aleksandr (Unlicensed)
Saved on	Jun 07, 2022	Jun 09, 2022

Versions Compared

Key

Creating a metric

Introduction

Fill the required parameters

Start date

Page Comparison

Versions Compared

Key

Creating a metric

Introduction

Fill the required parameters

Start date