Average velocity by weeks
- Aleksandr (Unlicensed)
This article describes what “Average velocity by weeks” metric is and how it helps and works
What is “Average velocity by weeks” metric?
Average Velocity shows an amount of value (in story points or items) delivered within 4 - 13 - 26 - 52 weeks. Average Velocity helps to compare the productivity of a team on a long-term versus a short-term time interval - it reveals if an overall performance improves or degrades
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
Including/excluding sub-items
First of all we need to decide if we want to include sub-items into the calculation or not. To include sub-items we need to change number “1” in the first query to any other number, for example 0 or 2. If there is a need to exclude sub-items - skip this point (the query must look as shown below)
include_sub_items as
(
select 1 -- 1 - exclude sub-tasks, any other number - include sub-tasks
as y_n
)Generating last 52 weeks
To generate first day of every week of last 52 weeks we use “generate_series()“ function where the beginning and the end of required period of time are defined. To find the day of the beginning we need to subtract interval of 52 weeks from the first day of the last completed week. To find the end of the period we need to subtract 2 days from the first day of the last completed week to exclude current week from the result. The step “1 week“ divides this period of time into 52 weeks. To find the end of every week we need to add interval of 6 days, 23 hours, 59 minutes and 59 seconds to get Saturday 23:59:59
weeks as (
select a.start_of_the_week,
a.start_of_the_week
+ interval '6 day 23 hour 59 minute 59 second' as end_of_the_week
from (
select * from generate_series(
case trim(to_char(now(), 'day'))
when 'sunday'
then date_trunc('day', now())
else date_trunc('week', now()) - interval '1 day'
end - interval '52 week', --the beginning of period
case trim(to_char(now(), 'day'))
when 'sunday'
then date_trunc('day', now())
else date_trunc('week', now()) - interval '2 day'
end, --the end of the period
'1 week') start_of_the_week
) a
)Example of the output:
Retrieving and filtering required data
PerfQL-statement below retrieves data according to calculation requirements
spsanditems as
(
select
done_date as dt,
key as items,
story_points as story_points
from ticket
where lower(status) in ('done', 'closed', 'resolved')
and (select y_n from include_sub_items) <> 1 or lower(type) not like 'sub%'
)Example of the output:
Joining of generated weeks and filtered data
“done_date“ must be between the start and the end of its week for proper joining weeks and filtered data. The result set of this query is sorted descending by “start_of_the_week“ for proper calculation from last 4 weeks to last 52 weeks
Example of the output:
Full code recap
Finally we need to combine four similar queries to aggregate data within last 4 - 13 - 26 - 52 weeks
Example of the output: