Why this JQL function is not working?

[Javier Castelblanco Diaz]

Hi,

Maybe some of you can help me find why this JQL formula is not getting the desired results:

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations != "Intechsol Corp" AND Organizations = "Garage Isla Verde LLC" AND Organizations = "Auto Grupo PR" AND Organizations = "Señorial Auto" AND Organizations = "Mora Housing" AND Organizations = "Bella International" AND Organizations = "Allied Logistic" AND Organizations = "The Marketing Source" AND Organizations = Cidrines AND Organizations = EncoPR ORDER BY createdDate

My outcome is to find created tickets during March and April of only those organizations I choose (the 9 clients with most open tickets during that period).  

As you will see I get results when I include only one organization, but once I add another organization, then it doesn't show any results. 

• With this formula I get results: (Adding only one organization)

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations != "Intechsol Corp" AND Organizations = "Garage Isla Verde LLC" ORDER BY createdDate

• With this formula I don't get results: (Adding more than one organization)

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations != "Intechsol Corp" AND Organizations = "Garage Isla Verde LLC" AND Organizations = "Auto Grupo PR" ORDER BY createdDate

[Payne · Accepted solution]

Your JQL is seeking issues that have all 9 of those organizations; you've got

Organizations = "Garage Isla Verde LLC" AND Organizations = "Auto Grupo PR" AND Organizations = "Señorial Auto" ...

That's like looking for a car that is red AND blue AND black AND grey. An issue won't have all 9. You should switch from AND to OR, like this:

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations != "Intechsol Corp" AND (Organizations = "Garage Isla Verde LLC" OR Organizations = "Señorial Auto" OR Organizations = "Auto Grupo PR" OR Organizations = "Mora Housing" OR Organizations = "Bella International" OR Organizations = "Allied Logistic" OR Organizations = "The Marketing Source" OR Organizations = Cidrines OR Organizations = EncoPR) ORDER BY createdDate

or, better yet, use IN:

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations != "Intechsol Corp" AND Organizations in ("Garage Isla Verde LLC","Señorial Auto","Auto Grupo PR","Mora Housing","Bella International","Allied Logistic","The Marketing Source",Cidrines,EncoPR) ORDER BY createdDate

Further, you don't need to explicitly exclude "Intechsol Corp" since it doesn't match the 9 organizations listed in the IN clause. So, it would be:

project = "Intechsol's Customer Services" AND createdDate >= startOfMonth(-2) AND createdDate <= endOfMonth(-1) AND status != Canceled AND status != Declined AND status != Done AND Organizations in ("Garage Isla Verde LLC","Señorial Auto","Auto Grupo PR","Mora Housing","Bella International","Allied Logistic","The Marketing Source",Cidrines,EncoPR) ORDER BY createdDate

[Javier Castelblanco Diaz]

Hi @Payne ,

Thank you so much.  It works perfectly fine.  

[John_Phelan]

Why do you have to put (-2) and (-1) for the date range?

[Payne]

@John_Phelan , he wanted to see tickets created during March and April. When run in May, startOfMonth(-2) is March 1, and endOfMonth(-1) is April 30.