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

[Kyra Mayock]

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!!

[Bill Sheboy]

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

[Kyra Mayock]

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!

[Kyra Mayock]

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

[Bill Sheboy]

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