how to get completed items in the sprint but without showing carried over tickets?

Question

I'm trying to generate a JQL query to get the amount of story points based on completed sprints but I'm getting duplicated story points over the sprints as sometimes the tickets are carried over to other sprints.

The JQL query used is:

project = "POL" AND sprint IN (2, 75, 110, 344, 744, 876, 1008) ORDER BY createdDate DESC

Alexey Pavlenko _App Developer_ · Answer

Hi @Daniela Cancino ,

1. Your title contradicts to your JQL:

project = "POL" AND sprint IN (2, 75, 110, 344, 744, 876, 1008) ORDER BY createdDate DESC

This JQL will show all the tickets presented in the sprints.
2. Actually, I think your problem is the opposite. Since you are trying to aggregate the story points of all sprints, you might encounter a situation where the same ticket wasn't completed in several sprints. In this case, you need to sum this ticket twice. With JQL natively, you won't be able to do that. It counts these kind tickets only once.
You can consider the app I have developed - Multi-team Scrum Metrics & Retrospective. It will consistently show the total story points for the number of sprints you specify.

Best regards,
Alexey

Bill Sheboy · Answer

Hi @Daniela Cancino -- Welcome to the Atlassian Community!

That is not possible with the built-in features of JQL.

Some workarounds depend upon how often you want to perform this type of query and your cost / effort constraints:

* need this one time* manually review the built-in sprint reports and record the sums
* export the issues to a spreadsheet, use formulas to detect the last sprint, and pivot on the results to sum the values

* need this often* have money to spend* investigate marketplace apps which add features to JQL
* investigate marketplace apps which add reporting to handle this case

* do not have money to spend, and willing to build something* create an automation rule, triggered on Sprint Completed, which saves the sprint in a custom field, such as "Completed In Sprint", and then use built-in JQL to check that field and sum the values
* create a stand-alone application using the REST API endpoints to perform this type of reporting

Kind regards,
Bill