JQL Invalid date using WAS operator

Andy Liesveld May 2, 2022

Trying to build a JQL query of historical past due items using the WAS and AFTER operators. I can query for a status after a static date, but I need it to query based on that items due date.

When I try to use our due date variable in the query I get the following:

 

Date value 'ENG DUE[Date]' for predicate 'after' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

 

Is there a way I can use our due dates by item as a value to query?

3 answers

1 accepted

3 votes
Answer accepted
Craig Nodwell
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 2, 2022

You could maybe try an Automation that would set a hidden custom field (flag) when the above criteria is met.  Then build your report out of a query on that field.
You could do the same thing as a post function on the transition.
That would allow for a historical reference regardless of current status or date range.
Do you have any plugins available to you?  Like scriptrunner. 

Andy Liesveld May 2, 2022

I'm going to look into this. I believe we are able to do that as we've got a number of automations baked into our process already. 

Admittedly, I'm not an administrative-level user. I'll have to have some of my folks help me set that up, but I'm confident I could create a JQL filter with such a field/automation in place.

I had assumed there was a simple syntax issue around the WAS operator.

2 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 2, 2022

Hi @Andy Liesveld , welcome to the community.

So you can't use historic operators with due date. However, you may be able to achieve your goal based on what you're saying but I'm not 100% sure on what you're attempting to do. Could you share your existing JQL and maybe explain a little bit more about what you're hoping to achieve?

Craig Nodwell
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 2, 2022

In support of this:
For reference.
supportedunsupported.JPG

From this page:
advanced-search-reference-jql-fields--#Custom-field 

0 votes
Andy Liesveld May 2, 2022

Here is the query that does not work: 

project = NIRSCHED AND status WAS "ENG" AFTER "ENG DUE[Date]"

Thanks for confirming the historic operator with due date. I was suspicious of that.

 

I'm working to build a query that would fetch all issues that have gone past due. Not just issues that are currently past due... I also need to capture items that have previously gone past due and now may or may not be closed. I want to track past due items over some period of time to show department metrics and trends.

In the query above, the ENG status means our engineering department owns the item until they move it to the next status. If it remains in ENG after the ENG DUE date field, it is past due. I've used these fields to successfully build a query that can find all CURRENT past due items. However, once the items are eventually moved on, I lose the ability to log them into an ongoing report that shows historical past due items.

If there's a better way to do this, I'm all for it.

Suggest an answer

Log in or Sign up to answer