Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,293,500
Community Members
 
Community Events
165
Community Groups

Assistance needed with JQL query using "resolutiondate" field to filter out old completed tickets

Edited

Hi All

I am trying to show all tickets in the project that are currently in any to-do or in progress category as well as done tickets that were completed within the past 2 weeks. The filter query I use for my Kanban board uses the "resolutiondate"  field to determine when tickets that are completed drop off the dashboard/kanban board etc. 

The query I am using is: project = PLM AND issuetype = "New Partner Onboarding" AND status != Done OR project = "Partner Lifecycle Management" AND issuetype = "New Partner Onboarding" AND resolutiondate <= endOfWeek(-2w) OR project = PLM AND issuetype = "Existing Partner New Bank/New Kiosk" AND status != Done OR project = "Partner Lifecycle Management" AND issuetype = "Existing Partner New Bank/New Kiosk" AND resolutiondate <= endOfWeek(-2w) ORDER BY cf[12156] ASC, cf[12147] DESC, cf[11601] ASC

1. Did I write this query correctly if I am looking for the filter to show me all tickets that are either open, or resolved within the past 2 weeks only?

2. Does anyone know a better/easier way to make sure the tickets that have been resolved more than 2 weeks prior don't show on the dashboard view? (because the tiles are limited to 50 issues) 

 

Thanks!!

1 answer

Hi @Kyra Mayock 

For your second question, are you trying to limit the issues shown on a dashboard or on you Kanban board, or both?  Using the board for your dashboard gadget will do both...but if you only want one to be filtered, please create separate saved filters for each (board and dashboard).

On your first question, let's check some things first...

  • Are your PLM and "Partner Lifecycle Management" the same project?
  • You reference a couple of different issueTypes in that JQL: "New Partner Onboarding" and "Existing Partner New Bank/New Kiosk".  Do you want all open/resolved in last 2w issues shown for both, or something different?

Knowing the answers to these questions will help you structure an improved query, where you test the common things once with AND, and the different things separately with OR.

And FYI there is some good, free training from Atlassian that can help you learn about creating such queries, which you may find here: https://university.atlassian.com/student/path/849533-gain-project-insights-through-jql

Kind regards,
Bill

Hi Bill, 

I'd like the filter to apply to both, though the dashboard tile is more important since the Kanban board allows you to select how many weeks you want resolved tickets to show anyway.

What I am trying to do with the query is show every ticket in the PLM project (and yes it is the same as "Partner Lifecycle Management") that is either in the to-do category, the in progress category, and done category but only for those tickets that were resolved in the past 2 weeks. So I tried the status = done AND resolutiondate <= (-2w) but it doesn't seem to return anything that has the done status. I've read the documentation and watched the training videos but I'm still missing something I guess.

I do have separate dashboard tiles for each of the issuetypes currently, but they can also be grouped in 1 tile. The query I posted is for the Kanban board. I have others for the dashboard tiles but they are basically the same with only the 1 relevant issuetype.

 

Thanks!

Oh man, I think maybe I need to switch the <= to a >= 

Like Bill Sheboy likes this

Yes, that sounds correct. 

Also it can help for complex queries to create them piece by piece, confirming the results as you go.  Usually by adding the elements with AND clauses first and adding the OR ones later.

Finally remember that AND takes precedence over OR, so when there is ambiguity in the JQL use parentheses to wrap expressions.

For example:

project = PLM
AND issuetype IN ( "New Partner Onboarding",
"Existing Partner New Bank/New Kiosk" )
AND (
statusCategory != Done
OR ( statusCategory = Done
AND resolutionDate >= endOfWeek(-2)
)
)
ORDER BY cf[12156] ASC, cf[12147] DESC, cf[11601] ASC

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS

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