Create an exclusion criteria for a certain project status

[Prexa Patel]

I have created a custom filter for all 2021 projects for my team from which I want to exclude projects with a status of "cancelled" which were created in 2020. 

Thanks. 

[Bill Sheboy]

Hi @Prexa Patel 

Jira focuses on the issues not on the projects containing them, and...JQL is not a SQL.  Putting those ideas together I do not see how you could use JQL to find just projects without the associated issues (and the field values of interest).

You could investigate the REST API to check the projects directly, although I did not see that project status attribute being returned in the methods.

Or, you could investigate the marketplace add-ons to search for things to support this type of query: https://marketplace.atlassian.com/

Best regards,

Bill

[Prexa Patel]

@Bill Sheboy - thank you for the above. I was able to create a filter to aggregate all projects for 2021 for my team without focusing on the issues - here is the query - project = PDPMO AND "Project Status" != Cancelled AND due >= startOfYear() ORDER BY due ASC 

@Kristján Geir Mathiesen @Bill Sheboy as you can see from the query above - i am able to exclude all cancelled projects. The list however contains projects created in 2020 and cancelled in 2021. 

The goal is to create a filter that includes all projects with a due date in 2021 and exclude cancelled projects created in 2020. If a project was created and cancelled in 2021 i want to be able to include it in the filter. 

hope this helps. 

Thanks,

Prexa Patel

[Bill Sheboy]

Ah, now I believe I understand better...

Those project fields are not in my instance, and so I hypothesize that they are custom fields in your instance, either created by your site admin, by a marketplace addon product, or perhaps they are in a version of Jira cloud above standard?  Can you confirm any of this?

If so, you just need to identify the available fields and then you should be able to create your query.  For example, is there a field for "Project Created" that you may query to answer your need?

[Kristján Geir Mathiesen]

Hi @Prexa Patel 

I assume you are referring to the workflow status "cancelled". If so, then you could just add to your existing filter:

AND status != cancelled AND createdDate >= startOfYear(-1)

HTH,
KGM

[Prexa Patel]

Thank you Kristjan - the filter should be on the actual Project status field as cancelled. i am unsure why at my organization the workflow status is marked as "done" vs. actually cancelled. something i may need to research and work through to update. 

I used above formula on project status field and does not seem to work as it excludes all cancelled projects. 

[Kristján Geir Mathiesen]

@Prexa Patel  Oh. When you say "cancelled projects", do you mean the Project Category is cancelled?

[Prexa Patel]

[Prexa Patel]

@Kristján Geir Mathiesen as you can see in the above screen shot the 6th field is Project status. 

[Kristján Geir Mathiesen]

Thanks @Prexa Patel  Wow, I'm stumped. Sorry about that.