Was ticket in a sprint during a given period

[Billy Kelly]

Hi all, 

I've been looking to expand on a structure to help me track tickets completed in a given month and initially was using JQL status WAS IN "In Progress" DURING ("2022/03/01", "2022/03/30"  to do this but this query doesn't work and the number of statuses we use will make this hard to use, plus this will be tickets from across multiple projects

I was wondering if I can use sprint.startdate and sprint.enddate in a formula to capture all the open/closed sprints during the month of March? From here, I can just sum over the column to get the total tickets and current multiple columns for each month

Would be good to know if anyone has done this before or if a better approach should be used 

Thanks 

[Nicholas Ellis _ALM Works_]

Hi Billy,

You could use the sprint dates, but there are some interesting complications you can run into, such as an issue being in multiple sprints.  I would use the resolved date, and then define a date range around it.  for a trailing date I might do something like this:

IF DAYS_BETWEEN(resolved, NOW()) < 30: 1

or if you wanted to be more specific to the actual month you could do something like:

IF JQL { resolved < endOfMonth(-1) and resolved > endOfMonth(-2) } : 1

Both of these Expr fucntions will return 1, when the condition is true.  Then you can simply click "sub over sub-items" to get an issue count.

You could also replace the 1 with an estimate field if you wanted to sum that up as well.

Cheers,
Nick
[ALM Works]

[Billy Kelly]

Thanks, Nick, and good point on the tickets being in two sprints. 

With the above, I can build that out with different columns for different months but one issue is this is only tickets resolved in that month, and not for tickets that were in progress for that month 

[Nicholas Ellis _ALM Works_]

Hi Billy,

You can use your "was in progress during..." JQL query if you like, but I expect the performance will be much worse, as WAS queries can be quite computationally expensive.  As long as the structure is not very large (1k+ issues), I wouldn't expect it to be too much of a problem, but it is worth mentioning.

Cheers,
Nick
[ALM Works]

[Kathryn Vargas _Tempo_]

Hi @Billy Kelly, 

To add to what my colleague Nick shared, we're also hosting a virtual event soon: Structure Formulas: Unleash Data Superpowers in Atlassian Cloud. You're more than welcome to attend; we'll be covering the feature, the Expr language, and sharing formulas and use cases. Hope to see you there!

Best, 
Kathryn