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 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
spsanditems_by_weeks as (
select
weeks.start_of_the_week as start_of_the_week,
weeks.end_of_the_week as end_of_the_week,
coalesce(count(spsanditems.items), 0) as items,
coalesce(sum(spsanditems.story_points), 0) as story_points
from weeks
left join
spsanditems
on spsanditems.dt between weeks.start_of_the_week
and weeks.end_of_the_week
group by weeks.start_of_the_week,
weeks.end_of_the_week
order by start_of_the_week desc
)
Example of the output:
Full code recap
with
include_sub_items as
(
select 1 -- 1 - exclude sub-tasks, any other number - include sub-tasks
as y_n
),
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',
case trim(to_char(now(), 'day'))
when 'sunday'
then date_trunc('day', now())
else date_trunc('week', now()) - interval '2 day'
end,
'1 week') start_of_the_week
) a
),
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%'
),
spsanditems_by_weeks as (
select
weeks.start_of_the_week as start_of_the_week,
weeks.end_of_the_week as end_of_the_week,
coalesce(count(spsanditems.items), 0) as items,
coalesce(sum(spsanditems.story_points), 0) as story_points
from weeks
left join
spsanditems
on spsanditems.dt between weeks.start_of_the_week
and weeks.end_of_the_week
group by weeks.start_of_the_week,
weeks.end_of_the_week
order by start_of_the_week desc
)
select
1 as num,
'Last 4 weeks' as "Title",
round(avg(sp4.items)) as "Items",
round(avg(sp4.story_points)::decimal) as "Story Points"
from (select items, story_points from spsanditems_by_weeks limit 4) sp4
union all
select
2 as num,
'Last 13 weeks' as "Title",
round(avg(sp13.items)) as "Items",
round(avg(sp13.story_points)::decimal) as "Story Points"
from (select items, story_points from spsanditems_by_weeks limit 13) sp13
union all
select
3 as num,
'Last 26 weeks' as "Title",
round(avg(sp26.items)) as "Items",
round(avg(sp26.story_points)::decimal) as "Story Points"
from (select items, story_points from spsanditems_by_weeks limit 26) sp26
union all
select
4 as num,
'Last 52 weeks' as "Title",
round(avg(sp52.items)) as "Items",
round(avg(sp52.story_points)::decimal) as "Story Points"
from (select items, story_points from spsanditems_by_weeks limit 52) sp52
order by num;
Finally we need to combine four similar queries to aggregate data within last 4 - 13 - 26 - 52 weeks
Example of the output:
Copyright © 2024 EPAM Systems, Inc. |
|---|
All Rights Reserved. All information contained herein is, and remains the property of EPAM Systems, Inc. and/or its suppliers and is protected by international intellectual property law. Dissemination of this information or reproduction of this material is strictly forbidden, unless prior written permission is obtained from EPAM Systems, Inc. |