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

[olli_raatikainen]

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 :)

[Rilwan Ahmed]

Hi @olli_raatikainen ,

Welcome to the community !!

1. Create a filter with JQL "labels =MPV" and save it. 
2. In your dashboard, add Pie Chart gadget, select the filter name as saved in step 1
3. Select Statistic Type = Status.

This will give you result like below screenshot

[olli_raatikainen]

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

[Rilwan Ahmed]

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

[Oliver Siebenmarck _Polymetis Apps_]

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

[Dave Mathijs]

HI @olli_raatikainen Welcome to the Atlassian Community!

First of all, you can create a filter in advanced issue search with following JQL:

labels = MVP AND statusCategory = Done

This will give you all issues with label "MVP" in a "green" status (category = Done).

Next, as @Rilwan Ahmed mentioned, you create a dashboard with the pie chat gadget based on this filter.