Purpose

Created vs Resolved reveals team productivity versus a pipeline of tasks over time. As a result, it becomes clear if there is enough or not enough capacity to cope with that pipeline.

How metric helps

Looking after Created vs Resolved tendency helps balance a proper capacity and understand if need to adjust it.

Examples:

amount of created work exceeds the resolved one - this means a team has more work to do than their current capacity allows and the queue is growing. Consider extending team capacity or accept an increase of response/resolution timings.
amount of created work is nearly the same as resolved one - ideal match, all incoming tasks/tickets are resolved in a timely manner, no any overload or un-utilized capacity in a team.
amount of created work is lower than resolved one - incoming tasks/tickets don't fully utilize an available capacity of a team.

How metric works

Chart overview

Created vs Resolved is measured in natural amount of work items (e.g. support tickets or regular tasks), i.e. no any effort or sizing is taken into consideration here. A chart shows a cumulative number of created tasks in comparison to a cumulative number of resolved tasks. The chart can be viewed for the following periods prior to today:

last 7 days
last 30 days
last 90 days

So that the axis X shows calendar dates prior to today for the last N days.

Calculation

Created items

1st value in the chart - Cr0: a number of items popped up in the "To Do" status bucket at the first day of the selected time range
2nd and subsequent values = Cr0 + Cr1 + ..+ CrN,

where

Cr1, ..., CrN - a number of items in any status of "To Do" bucket at the 2nd day and further.

Resolved items

1st value in the chart - R0: a number of items in a status from the "Done" bucket at the first day of the selected time period
2nd and subsequent values = R0 + R1 + ..+ RN,

where

R1, ..., RN - a number of items in a status from the bucket "Done" at the 2nd day and further.

Calculation notes

If any item was re-opened (no matter how many times) - the last occurrence of a respective status is taken into the consideration.

PerfQL

WITH 
  timeline AS (
    SELECT generate_series (date_trunc('day', now()) - interval '7 days', now() - interval '1 days', '1 day') as days
  ), 
  created AS (
    SELECT count(key) as created, date_trunc('day', created) as day
    FROM ticket
    WHERE case when is_include_sub_items() then true else parent_task is null end
    GROUP BY day
  ), 
  resolved AS (
    SELECT count(key) as resolved, date_trunc('day', done_date) as day
    FROM ticket
    WHERE done_date is not null AND case when is_include_sub_items() then true else parent_task is null end
    GROUP BY day
  )
SELECT
  to_char(t.days, 'YYYY-MM-DD') as "Day",
  sum(coalesce(c.created, 0)) over (order by t.days) as "Created",
  sum(coalesce(r.resolved, 0)) over (order by t.days) as "Resolved"
FROM timeline t
  LEFT JOIN created c ON t.days = c.day
  LEFT JOIN resolved r ON t.days = r.day
ORDER BY "Day" ASC;

WITH 
  timeline AS (
    SELECT 
      generate_series(date_trunc('day', now()) - interval '30 days', now() - interval '1 days', '1 day') as days
  ),
  created AS (
    SELECT
      count(key) as created,
      date_trunc('day', created) as day
    FROM ticket
    WHERE case when is_include_sub_items() then true else parent_task is null end
    GROUP BY day
  ),
  resolved AS (
    SELECT
      count(key) as resolved,
      date_trunc('day', done_date) as day
    FROM ticket
    WHERE done_date is not null and case when is_include_sub_items() then true else parent_task is null end
    GROUP BY day
  )
SELECT
  to_char(t.days, 'YYYY-MM-DD')as "Day",
  sum(coalesce(c.created,0)) over (order by t.days) as "Created",
  sum(coalesce(r.resolved,0)) over (order by t.days) as "Resolved"
FROM timeline t
LEFT JOIN created c ON t.days=c.day
LEFT JOIN resolved r ON t.days=r.day
ORDER BY "Day" ASC;

WITH 
  timeline AS (
    SELECT 
      generate_series(date_trunc('day', now()) - interval '90 days', now() - interval '1 days', '1 day') as days
  ), 

  created AS (
    SELECT
      count(key) as created,
      date_trunc('day', created) as day
    FROM ticket
    WHERE 
      case when is_include_sub_items() then true else parent_task is null end
    GROUP BY day
  ), 

  resolved AS (
    SELECT
      count(key) as resolved,
      date_trunc('day', done_date) as day
    FROM ticket
    WHERE 
      done_date is not null and 
      case when is_include_sub_items() 
        then true 
        else parent_task is null 
      end
    GROUP BY day
  )

SELECT
  to_char(t.days, 'YYYY-MM-DD') as "Day",
  sum(coalesce(c.created,0)) over (order by t.days) as "Created",
  sum(coalesce(r.resolved,0)) over (order by t.days) as "Resolved"
FROM timeline t
LEFT JOIN created c ON t.days=c.day
LEFT JOIN resolved r ON t.days=r.day
ORDER BY "Day" ASC;

Data Source

Data for the metric can be collected from a task tracking system (Jira, TFS, Rally, etc.)