How do I create Plan to Complete chart from Jira by Number of Stories

[Ann]

I can use Excel to create the actual chart. I need to know how I can pull data on what stories were planned for a sprint and what were completed? What do I filter the issues? what columns do I pull for data?

[Bill Sheboy]

Hi @Ann 

My understanding of a percent, planned to complete chart is:

1. for each time frame to analyze (e.g., for a completed Sprint)
2. gather the planned work item count for the start of the time frame (e.g., Sprint started)
3. gather the completed work items count during the time frame
4. plot the percentage of completed work items / planned work items for each time frame unit (e.g., Sprint start to end dates)
5. optionally, display the total count, average percentage or a running average over some time frames (e.g., the last N-Sprints)

Essentially, it is showing the percentages one could find by summarizing data on the built-in Velocity report.

Some possible challenges to chart this reliably with Jira and the Scrum framework are:

• the Sprint field can be modified after a Sprint closes;
• work items can be archived or deleted;
• if measuring on anything other than the work item counts, the "measure" could be altered over time; and
• with Scrum, a Sprint Plan is created immediately before the Sprint starts, and thus if one wanted to draw this chart as a forecast far into the future, it would be unstable and frequently change due to the inspect-and-adapt / respond-to-change aspects of Scrum.

With those challenges, it may help to either investigate a marketplace app specifically to draw this type of chart and which takes a snapshot of the work items at sprint start, when completed, and at end of sprint; or without such an app, one could try using automation rules to store additional information in each work item in custom fields or entity properties, reducing the chance of errors.  (e.g., at Sprint start, save the scope; at item completion, save the active Sprint, etc.)  Then use JQL on those custom fields to export the data and process it with pivot tables for the chart.

Kind regards,
Bill

[Felipe Justino]

Hi @Ann 

To create a Plan to Complete Chart by number of Stories from Jira, you basically need to extract two sets of data: what was planned at the beginning of the sprint and what was completed by the end of the sprint.
Planned Stories are all those that were already associated with the sprint at the time it began. Completed Stories are those that belong to the sprint and have a status in the Done category at the end. With these two numbers, you can set up the comparison in Excel.
When exporting from Jira to CSV/Excel, it is recommended to pull at least the following columns: Issue Key, Summary, Status, Sprint, Created, Resolved/Resolution Date, and, if used, Story Points. Since the chart is by number of stories, you will only use the record count.
In Excel, simply consolidate the number of planned stories versus completed stories and generate a column or bar chart with these two values. If you want to supplement the analysis, you can also separate what was added to the sprint after it started and what was removed, to explain variations between what was planned and what was delivered.

Use this JQL:

project = ABC
AND issuetype = Story
AND sprint = 123
AND created <= startOfSprint(123)

Where:

• ABC = your project key

• 123 = your Sprint ID (or use sprint in openSprints() if active)

How to Identify “Completed” Stories (Done in the Sprint)

Use this JQL:

project = ABC
AND issuetype = Story
AND sprint = 123
AND statusCategory = Done