JQL for all tickets not completed in a sprint that carried over (over a time span)

Question

I am currently using the following JQL query:

project=X AND <custom_field>=X AND sprint IN closedSprints() AND sprint IN futureSprints()

However, it doesn't seem to return the expected results.

What I am specifically looking for is the following:

* Return any ticket that was not completed in any previous sprint(s) AND
* The ticket was added to a future sprint 
* Filter by a custom field (in our case it's our engineering pod name)
* Filter over a particular timeframe (or maybe sprints?)

Is this possible?

Trudy P Claspill · Answer

Hi Roberto,

I pulled the report for two sprints ago that showed we had a total of three tickets that were not completed. These were rolled over to the new sprint.

Are the three issues still assigned to that "new sprint"? Is that "new sprint" currently not yet started or has it already been started and/or completed?

The JQL will show you issues based on the current information. If the new sprint is not in an 'unstarted" state, then that sprint is no longer a "futureSprint". The JQL will give you only issues that

- were in a sprint that has subsequently is currently Completed

- were incomplete when that sprint was Completed

- are currently in a future (not started) sprint

If you are trying to get a report of any issue that was ever carried over from one sprint to another that is more complicated. The filter would have to accommodate:

* an issue that was in sprint A and carried over to sprint B where it was completed
* an issue that was in sprint A and carried over to sprint B where it still wasn't completed, and was then moved to the backlog
* an issue that was in sprint A and is now in an active sprint C
* an issue that was in sprint A and is now in a future sprint D

3 and 4 are easy enough, but there is no filter option natively to select issues based on the number of closed sprints they have been in.

Regarding your point #4 there is no native JQL functionality for filtering issues based on the start or end dates of sprints in which they were included. There is no native functionality to constrain what is considered by the functions openSprints, futureSprints, and closedSprints based on dates.

Roberto Quezada · Answer

Hey Trudy!

For example. I pulled the report for two sprints ago that showed we had a total of three tickets that were not completed. These were rolled over to the new sprint. This is the out-of-the-box sprint report JIRA offers btw.

The JQL query results, should have at a minimum returned those three tickets but they do not. spot checking a few more previous sprints and there are some issues that were not completed and carried over. I even updated my JQL to be specific about the issue types and now my query looks like this:

project=X AND "Pod[Dropdown]"=X AND type in (Story, Bug, Spike) AND sprint in closedSprints() AND sprint in futureSprints()

The above query currently returns one ticket!

My custom field is a dropdown field for engineering team names that we use to "assign" a ticket to.

For the #4 option: I was looking to narrow down all of the tickets that carried over to a future sprint but only between a certain number of sprints (looking at the last 6 months for example).  I understand I may exclude tickets that may be in a current (open) sprint, which hopefully I can add an additional condition to address this later. I just want to see the first query work and give me back the expected results.

Let me know if this helped clarify what I'm looking for!

Trudy P Claspill · Answer

Hello @Roberto Quezada

Welcome to the Atlassian community!

How exactly is the filter not returning the results you expect? Provide the details for a specific issue that is missing that you expect to be included and an issue that is included that you expect to be missing.

What type of field is your custom_field?

Please provide more details about how you want #4 applied. If you are using a date range, what date field in the issues would that be compared to?