This article describes what “Velocity by weeks” metric is and how it helps and works

1 What is “Velocity by weeks” metric?
2 Creating a metric
3 Creating a Custom Selection
4 Defining week borders
- 4.1 The beginning of a week
- 4.2 The end of the week
5 Retrieving required data
6 Data aggregation and grouping
7 Full code recap

What is “Velocity by weeks” metric?

Velocity shows an absolute amount of value (measured in story point or items) delivered within a week. It helps to determine an amount of work a current project team can process within a particular period

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

Defining week borders

The beginning of a week

case trim(to_char(done_date, 'day'))
     when 'sunday'
     then date_trunc('day', done_date)
     else date_trunc('week', done_date) - interval '1 day'
 end

First of all we need to define week borders for every date in “done_date“ column of “Ticket” table. To do so we check “done_date“. If it's Sunday “done_date“ is truncated to get rid of the hours and minutes in it. If not then “done_date“ is truncated to Monday and period of one day then subtracted from it.

The end of the week

case trim(to_char(done_date, 'day'))
     when 'sunday'
     then date_trunc('day', done_date)
     else date_trunc('week', done_date) - interval '1 day'
     end
  + '6 day 23 hour 59 minute 59 second'

The end of the week can be found as sum of the earlier found beginning of the week and a period of time that consists of 6 days, 23 hours, 59 minutes and 59 seconds to get Saturday 23:59:59.

Retrieving required data

The whole select-statement that filters data according to calculation requirements looks like this:

select 
        key as item, 
        story_points as story_point, 
        done_date as finish_dt,

        case trim(to_char(done_date, 'day'))
        when 'sunday'
        then date_trunc('day', done_date)
        else date_trunc('week', done_date) - interval '1 day'
        end
        as start_of_the_week,

        case trim(to_char(done_date, 'day'))
        when 'sunday'
        then date_trunc('day', done_date)
        else date_trunc('week', done_date) - interval '1 day'
        end 
        +    '6 day 23 hour 59 minute 59 second'
        as end_of_the_week

  from  ticket
 where  lower(status) in ('done', 'closed')
   and  lower(type) not like 'sub%'               -- sub-items (i.e. sub-tasks, sub-bugs in JIRA) are not included into the calculation;
   and  done_date <                               -- only completed week/months are shown in the chart, no future ones;
        case trim(to_char(now(), 'day'))
        when 'sunday'
        then date_trunc('day', now())
        else date_trunc('week', now()) - interval '1 day'
        end

It’s required to take into account only completed tickets, to exclude sub-items from the result and to exclude data for current week

Example of the output:

Data aggregation and grouping

To get required result we need to concatenate “start_of_the_week“ and “end_of_the_week“ columns turned into strings and apply special format to present every week as “Month Day - Month Day, Year“. Then we need to summarize story points, to count tickets and to group them by “start_of_the_week“ and “end_of_the_week“ columns

Full code recap