Dashboard JQL

[jmercado]

Hello Jira Community,

Could someone please help me fix my dashboard jql query? I am new to Jira (5 days of playing around) and I have a DB that I am trying to fix.

I would like to show all tickets that have breached SLA but I would like to filter all tickets where Status = Resolved or all tickets where LOS Request Type = Vendor Setup. LOS Request type is a custom drop-down field for us.

I have exported the manual excel file and based on my manual count i have 72 tickets breaching SLA. if I am to filter Resolved tickets and those under Vendor Setup, then I should only see 28.

Below is the query I am using but it does not seem to properly filter for the two criteria. this query is giving me 64 instead of 28. can you please tell me if I am missing anything in my query?

project = "10524" AND "Time to resolution" = breached() AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)

I don't see a way to attach a file, so I am just pasting the table below for the raw data. This is how I know that I should only be seeing 28 and not 64.

Custom field (LOS Request Type)	Issue key	Issue id	Status	Project key
Invoice Payments or Issues	LOS-204	451983	Pending	LOS
Vendor Setup	LOS-203	451974	In Progress	LOS
CL Support	LOS-202	451893	In Progress	LOS
Vendor Setup	LOS-201	451888	In Progress	LOS
Vendor Setup	LOS-199	451859	In Progress	LOS
Vendor Setup	LOS-198	451858	Waiting for support	LOS
Vendor Setup	LOS-197	451857	Waiting for support	LOS
Vendor Setup	LOS-196	451856	Waiting for support	LOS
Vendor Setup	LOS-195	451855	Waiting for support	LOS
Vendor Setup	LOS-194	451854	Waiting for support	LOS
Vendor Setup	LOS-193	451853	Waiting for support	LOS
Vendor Setup	LOS-191	451851	Waiting for support	LOS
Vendor Setup	LOS-190	451850	Waiting for support	LOS
Vendor Setup	LOS-189	451849	Waiting for support	LOS
Vendor Setup	LOS-188	451848	Waiting for support	LOS
Vendor Setup	LOS-187	451847	In Progress	LOS
Vendor Setup	LOS-186	451846	In Progress	LOS
Vendor Setup	LOS-185	451844	In Progress	LOS
Vendor Setup	LOS-184	451843	In Progress	LOS
Invoice Payments or Issues	LOS-180	451831	Waiting for support	LOS
Vendor Setup	LOS-179	451829	In Progress	LOS
New Matter Creation	LOS-177	451803	In Progress	LOS
Invoice Payments or Issues	LOS-176	451793	Waiting for customer	LOS
CL Support	LOS-175	451785	In Progress	LOS
Legal Ops Project or Task	LOS-174	451782	In Progress	LOS
Forecasts	LOS-172	451763	In Progress	LOS
CLM Support	LOS-169	451705	Pending	LOS
CL Support	LOS-163	451667	In Progress	LOS
Contracts General	LOS-162	451666	Waiting for support	LOS
Invoice Payments or Issues	LOS-161	451657	Resolved	LOS
Contracts General	LOS-147	451605	Resolved	LOS
Legal Ops Project or Task	LOS-146	451589	Waiting for support	LOS
Reports and Analysis	LOS-145	451586	Waiting for support	LOS
Vendor Setup	LOS-139	451190	In Progress	LOS
CL Support	LOS-138	450964	Waiting for support	LOS
CLM Support	LOS-136	450530	Resolved	LOS
CL Support	LOS-116	450509	Waiting for support	LOS
Legal Ops Project or Task	LOS-114	450507	Waiting for support	LOS
Legal Ops Project or Task	LOS-112	450505	Waiting for support	LOS
Invoice Payments or Issues	LOS-109	450487	Resolved	LOS
Contracts General	LOS-106	450383	Resolved	LOS
Invoice Payments or Issues	LOS-98	450201	Resolved	LOS
Forecasts	LOS-97	450124	Waiting for support	LOS
Vendor Setup	LOS-95	450122	Resolved	LOS
Vendor Setup	LOS-90	450048	In Progress	LOS
CL Support	LOS-89	450045	In Progress	LOS
CL Support	LOS-88	450042	Resolved	LOS
Invoice Payments or Issues	LOS-85	449956	In Progress	LOS
Invoice Payments or Issues	LOS-82	449935	In Progress	LOS
Vendor Setup	LOS-79	449824	Resolved	LOS
Invoice Payments or Issues	LOS-76	449790	In Progress	LOS
CL Support	LOS-75	449702	Resolved	LOS
Invoice Payments or Issues	LOS-74	449701	Resolved	LOS
CL Support	LOS-71	449622	Waiting for support	LOS
Forecasts	LOS-70	449621	Resolved	LOS
Invoice Payments or Issues	LOS-68	449571	Resolved	LOS
Vendor Setup	LOS-61	449430	Resolved	LOS
Vendor Setup	LOS-59	449359	Resolved	LOS
Vendor Setup	LOS-57	449355	Resolved	LOS
Forecasts	LOS-55	449147	Resolved	LOS
	LOS-43	448458	Waiting for support	LOS
	LOS-42	448457	Waiting for support	LOS
Vendor Setup	LOS-41	448456	In Progress	LOS
	LOS-40	448455	Resolved	LOS
	LOS-36	448440	Resolved	LOS
CLM Support	LOS-32	448309	Resolved	LOS
Invoice Payments or Issues	LOS-28	448088	Resolved	LOS
Invoice Payments or Issues	LOS-27	448086	Waiting for support	LOS
	LOS-26	448081	Resolved	LOS
CL Support	LOS-25	448080	Resolved	LOS
Invoice Payments or Issues	LOS-22	447987	Waiting for support	LOS
Invoice Payments or Issues	LOS-20	447979	CANCELED	LOS

[Mark Segall · Accepted solution]

Hi @jmercado - The issue is in your "OR" statement.  With the OR clause, you're telling Jira to return Vendor Setup OR return anything unresolved.  Change the OR to AND and see if this returns the desired results:

project = "10524" AND "Time to resolution" = breached() AND "LOS Request Type[Dropdown]" != "Vendor Setup" AND status != Resolved

[jmercado]

Thanks, Mark.

that is actually what I was looking for. all tickets that are NOT Vendor Setup or NOT Resolved (all Unresolved). Actually, since I cannot fix this yesterday, I ended up doing the basic search. In the field dropdowns, I unchecked my filters (that is vendor set up and Resolved). Below is the JQL I got in the background. It does not look pretty (it's too long), but looks like it worked for my case. Would you comment again please if this is unsafe or not ideal to do?

[query intentionaly deleted]

[Mark Segall]

The thing to keep in mind with OR is that it can be deceptive when searching for the negative.  Let's break down your original JQL:

project = "10524" AND "Time to resolution" = breached() AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)

• Project = "10524" 
  • You're telling it to look at a single project
• AND "Time to resolution" = breached() 
  • Within the project find everything breached
• AND ("LOS Request Type[Dropdown]" != "Vendor Setup" OR status != Resolved)
  • Within the project everything breached and 
    • all issues that are not tagged as Vendor Setup (any status)
      OR
      any issues where status is not resolved regardless of Vendor Setup

Now your new query that was created by basic view translates to this (which is very similar to the one I provided in my previous response:

Project = LOS and status Not IN (Resolved) and "LOS Request Type[Dropdown]" NOT IN ("Vendor Setup") AND "Time To Resolution" = Breached()

Let's break this one down:

• Project = LOS
  • Still telling it to look at a single project
• AND status Not IN (Resolved)
  • Within the project all issues that are not resolved
• AND "LOS Request Type[Dropdown]" NOT IN ("Vendor Setup")
  • Within the project all issues that are not resolved and not tagged Vendor Setup
• AND "Time To Resolution" = Breached()
  • Within the project all issues that are not resolved, not tagged Vendor Setup, and in breach

[jmercado]

Thanks so much. I changed the formula and copied yours and I got the same result (compared to my long basic query). I will use this formula moving forward. Thanks as well for patiently breaking down the details of the JQL query. That's really helpful.

Thanks again and have a great day, @Mark Segall