Average velocity by sprints
- Aleksandr (Unlicensed)
This article describes what “Average velocity by sprints” metric is and how it helps and works
What is “Average velocity by sprints” metric?
Average Velocity shows an amount of value (in story points or items) delivered within 1 - 3 - 6 - 12 sprints. 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-items, any other number - include sub-items
as y_n
)Retrieving last 12 closed sprints
To retrieve last 12 sprints we need to filter sprint table where “state” column value equals 'Closed', sort the result by “complete_date” in reversed order and fetch first 12 rows
sprints as (
select s.id,
s.start_date,
s.complete_date,
s.finish_date
from sprint s
where lower(s.state) = 'closed'
order by s.complete_date desc
limit 12
)Example of the output:
Joining of the last 12 sprints and related tickets
For proper joining sprints and tickets we need to take all of 12 sprints and join tickets by equality of id in Sprint table and any value of “sprints” column in Ticket table. Also we need to retrieve only those tickets which were completed and got Done-status before a sprint was finished. The result set of this query is sorted descending by “complete_date“ for proper calculation from last sprint to last 12 sprints
spsanditems as (
select s.id,
max(coalesce(s.complete_date, s.finish_date)) as complete_date,
count(wi.key) as items,
sum(wi.story_points) as story_points
from sprints s
left join ticket wi on s.id = ANY (wi.sprints)
and wi.done_date < coalesce(s.complete_date, s.finish_date)
and ((select * from include_sub_items) <> 1 OR wi.parent_task is null)
and lower(wi.status) in ('done', 'closed', 'resolved')
group by s.id
order by complete_date desc -- join tickets and aggregate story points and items per sprint
) Example of the output:
Full code recap
Finally we need to combine four similar queries to aggregate data within last 1 - 3 - 6 - 12 weeks
Example of the output: