Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL Filter Due Date and Diagrams

Hi together,

 

i would like to ask if anyone knows how to build a complex JQL Query which includes the following content:

 

Issues which are overdue

Issues which are in time (have time till due)

Issues which are closed

 

The reason why i am asking is, my department wants to have Diagrams which include this content.

 

For Example a pie diagram which three pieces shown all of those three.

dd.PNG

As my knowledge, the only possibility to show all of those three filter in one pie diagram is to put it into just one filter, is this right?

 

Thank you in advance.

1 answer

1 accepted

1 vote
Answer accepted

Hi @Simon Müller,

Welcome to the community, with an interesting use case :-). If I understand you well, you want to pull all issues (for the sake of complexity, I will assume these are all in 1 Jira project) in a single query, but then divide them across different segments based on:

  1. Resolution state (Open vs Resolved)
  2. Being on time (subsegment of open issues, based on due date)

Those are essentially 3 different queries:

Project = XX and Resolution != Unresolved (1)
Project = XX and Resolution = Unresolved and Due >= Now() (2A)
Project = XX and Resolution = Unresolved and Due < Now() (2B)

Combining those in 1 single query is not difficult at all, since they will return all issues in your project:

Project = XX

In order to combine the 3 data sets in a single pie chart, you will need to mark your issues with the current state they are in. Options could be assign them a label (On Time, Overdue, Closed) or set up a custom field (select list, radio buttons) to mark them.

You would need to set up some automation to update the labels / custom field to keep them in the right category. e.g. every issue could be created with the On Time label by default. When the due date passes and the issue is still unresolved, update it automatically to Overdue. When an issue is resolved, update it to Closed. Marketplace apps like Scriptrunner or Automation for Jira can help you fix the automation part. 

As soon as you have the categorisation part in place, your pie chart becomes easy. Just build your pie from the category.

A couple of site notes:

If you choose to use labels, be mindful of the fact that labels may be used for other things as well. So the query for you pie chart would need to be something like this:

Project = XX and labels in ("On Time", Overdue, Closed)

Also related to the use of labels - when you add a new label, you should be careful to also remove the old label at the same time.

And finally, although this approach may work, it is certainly not the only solution to visualise this type of information. Alternatives may include:

  • Instead of trying to fit this information in a single pie chart, create 3 gadgets on a dashboard showing details of the separate queries I listed at first.
  • Use a reporting app from the marketplace that has the capability to use calculated measures as a basis for your report (e.g. EazyBI).

Hope this helps!

Hey @Walter Buggenhout _ACA IT_ ,

 

thank for that fast response, it helped a lot!

 

Our System works great now!

 

Great Community :)

Glad I could be of assistance, @Simon Müller

Would you mind accepting my answer as well? That does help other users with similar questions finding answers easier;

Thx! 

Like Simon Müller likes this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

171 views 6 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you