Purpose
Throughput shows a quantity of tasks a team delivers over time.
...
Resolved items value: a number of items moved to "Done" bucket (per Workflows in Project Configuration) over the same time range.
RAG thresholds: n/a.
PerfQL
| Expand |
|---|
| with
timeline as (
select
|
| Code Block |
|---|
| WITH
timeline AS (
SELECT
generate_series(
date_trunc('week', now()) - interval '12 weeks', |
--Change the time interval (week, month, quarter) if needed and the number of last values 12 previous + 1 current = total 13
date_trunc('week', now()),
'1 week'
) as weeks |
created as (
select
created AS (
SELECT
date_trunc('week', created) as created_date, |
--Change the time interval (week, month, quarter) if needed
count(*) as "Created Items" |
from ticket
where
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is |
null
group by null end
GROUP BY created_date |
resolved as (
select
resolved AS (
SELECT
date_trunc('week', done_date) as resolved_date, |
--Change the time interval (week, month, quarter) if needed
count(*) as "Resolved Items" |
from ticket
where
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is |
null
group by null end
GROUP BY resolved_date |
)select
)
SELECT
to_char(t.weeks, 'DD Mon') || '-' || to_char(t.weeks + interval '6 days', 'DD Mon, yyyy') as "Week", |
coalesce(c."Created Items", |
r."Resolved Items",
t.weeks
from
timeline t
left join created c
on 0) as "Created Items",
coalesce(r."Resolved Items",0) as "Resolved Items",
t.weeks
FROM timeline t
LEFT JOIN created c ON c.created_date = t.weeks |
left join resolved r
on
LEFT JOIN resolved r ON r.resolved_date = t.weeks |
order by -------DRILL DOWN-------- select
key,
type,
priority,
summary
from ticket
where | select
case when url is not null then '['||key||']('||url||')' else key end as "Issue Id",
type as "Type",
priority as "Priority",
summary as "Summary"
from ticket
where to_char(date_trunc('week',created), 'DD Mon') ||'-'|| to_char(date_trunc('week',created) + interval '6 days', 'DD Mon, yyyy') = clicked_x_value |
and
and case when is_include_sub_items() then true else parent_task is null end; |
|
| Expand |
|---|
select
key,
type,
priority,
summary
from ticket
where to_char(date_trunc('week',done_date), 'DD Mon') ||'-'|| to_char(date_trunc('week',done_date) + interval '6 days', 'DD Mon, yyyy') = clicked_x_value
and parent_task is null| title | Throughput (by Month) |
|---|
|
| Code Block |
|---|
| WITH
timeline AS (
SELECT
generate_series(
date_trunc('month', now()) - interval '11 month',
date_trunc('month', now()),
'1 month'
) as months
),
created AS (
SELECT
date_trunc('month', created) as created_date,
count(*) as "Created Items"
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is null end
GROUP BY created_date
),
resolved AS (
SELECT
date_trunc('month', done_date) as resolved_date,
count(*) as "Resolved Items"
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is null end
GROUP BY resolved_date
)
SELECT
to_char(t.months, 'YYYY Mon') as "Month",
coalesce(c."Created Items", 0) as "Created Items",
coalesce(r."Resolved Items",0) as "Resolved Items",
t.months
FROM timeline t
LEFT JOIN created c ON c.created_date = t.months
LEFT JOIN resolved r ON r.resolved_date = t.months
ORDER BY t.months asc; |
-------DRILL DOWN-------- | Code Block |
|---|
| select
case
when url is not null then '[' || key || '](' || url || ')'
else key
end as "Issue Id",
type as "Type",
priority as "Priority",
summary as "Summary"
from ticket
where
to_char(date_trunc('month',created), 'YYYY Mon') = clicked_x_value and
case
when is_include_sub_items() then true
else parent_task is null
end; |
|
| Expand |
|---|
| title | Throughput (by Quarter) |
|---|
|
| Code Block |
|---|
| WITH
timeline AS (
SELECT
generate_series(
date_trunc('quarter', now()) - interval '15 month',
date_trunc('quarter', now()),
'3 month'
) as quarters
),
created AS (
SELECT
date_trunc('quarter', created) as created_date,
count(*) as "Created Items"
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is null end
GROUP BY created_date
),
resolved AS (
SELECT
date_trunc('quarter', done_date) as resolved_date,
count(*) as "Resolved Items"
FROM ticket
WHERE
case when is_include_sub_items() then true else parent_task is null end
GROUP BY resolved_date
)
SELECT
'Q' || extract(quarter from t.quarters)::text || ' ' ||
extract(year from t.quarters)::text as "Quarter",
coalesce(c."Created Items", 0) as "Created Items",
coalesce(r."Resolved Items", 0) as "Resolved Items",
t.quarters
FROM timeline t
LEFT JOIN created c ON c.created_date = t.quarters
LEFT JOIN resolved r ON r.resolved_date = t.quarters
ORDER BY t.quarters asc; |
-------DRILL DOWN-------- | Code Block |
|---|
| select
case
when url is not null then '[' || key || '](' || url || ')'
else key
end as "Issue Id",
type as "Type",
priority as "Priority",
summary as "Summary"
from ticket
where
('Q' || extract(quarter from created)::text || ' ' || extract(year from created)::text) = clicked_x_value
and case
when is_include_sub_items() then true
else parent_task is null
end; |
|
| Note |
|---|
If any item was re-opened (no matter how many times) - the last occurrence of a respective status is taken into the consideration. By default sub-issues are not included into the calculation. To include them, check the checkbox on Project Settings>Data Sources>Task Tracking system>Workflows>Include sub items into metrics calculation.
|
Data Source
Data for the metric can be collected from a task tracking system (Jira, TFS/VSTS, Rally, etc.)
...