JQL: calculate issues with Done statuses out of all statuses with a certain label?

Question

For the life of me I cannot grasp how to write a JQL for the aforementioned need 🤦‍♂️ Let's say the label is "MVP" - how would one formualate a JQL line to accomplish that? I'd also want to have some kind of a pie chat showing the percentage, but that would be just the icing on the cake :)

Oliver Siebenmarck {Polymetis Apps} · Answer

Hi @olli_raatikainen ,

Welcome here!

By now, you know that JQL does not have features like COUNT as SQL has. And you have also seen the dashboard work around @Rilwan Ahmed showed you.

But what if you want to use the result more directly, say in a script or just want the number?  Well, that's where Jira Expressions come in. Jira Expressions are a way to select and aggregate data from one or more issues. You would mostly use them for custom conditions and validators in Jira Cloud, but they can also be used to wrangle data. You can access them either with the REST API or by using our free app Expression Tester.

What you'll want to do is to select a bunch of issue with JQL and then use Jira Expressions to perform the calculation you want. Let's start with a simple one:

JQL:

labels = MVP

Jira Expression:

issues.length

In my test instance, using the above app, it would look something like this:

12

While counting is quite useful, you might want to aggregate a bit more data. Let's say you'd like to know all the statuses and how many issues are in each status. You could adapt the Jira Expression to this one:

issues 
.map(l => l.status.name) 
.flatten() 
.reduce((result, label) => 
result.set( label, (result[label] || 0) + 1), new Map())

And get something like this back:

{
  "Done": 259,
  "Selected for Development": 2,
  "On Hold": 4,
  "In Progress": 19,
  "Backlog": 22
}

And that just's skimming the surface of all the fun you can have with Jira Expressions.

Hope that helps,

Oliver

Rilwan Ahmed · Answer

Glad that it helped you @olli_raatikainen . Please accept the answer. Users with similar query will find it helpful.

olli.raatikainen · Answer

Thank you so much, it was way easier than I thought :)  Got it done in a minute 👍