Include a simple operation in the JQL

[Pierre Leroux]

Hi,

I am trying to create a filter that would display issues that would follow a certain operation constraint:

remainingEstimate+timespent>originalEstimate

Would anyone know how I could achieve that?

Thanks in advance!

[Trudy Claspill · Accepted solution]

Hello @Pierre Leroux 

Native JQL does not support comparing one field to another, nor executing math operations within the JQL statement.

What do you want to do with the output of the filter?

If you want to use only native Jira functionality and be able to create a filter with JQL to retrieve the issues, then one way you could solve the problem is by creating a custom field that could be used to keep track of the condition having been met or not met. You would then also create Automation Rules to populate the field.

If you are interested in that solution, let us know and we can provide more details.

You might also be able to find a third party app in the Atlassian Marketplace that would add some functionality that would enable you to get what you want. I have not tried to solve this type of problem with a third party app, so I don't have any specific recommendations. You might be able to achieve it with one of the apps that provides Calculated Fields.

[Pierre Leroux]

Thanks for this useful answer.

To your question "What do you want to do with the output of the filter?", I would say we want to build a dashboard that show the tasks that have a fair chance to exceed the original estimate in terms of time spent. Before a task is completed, if the remaining estimate in addition to the time already spent on a jira exceeds the original estimates, there is a very high chance this task will cost more than expected. We want to locate those tasks in real time, in order to act ASAP by helping the person in charge achieve their goal in time and budget.

[Trudy Claspill]

By adding a custom field that you populate with an Automation Rule, you would be able to build a filter based on that custom field and use that filter with several of the natively available dashboard gadgets.

With the Automation Rule, you would be updating the issue in real time by creating one or more rules that would detect changes to the Remaining Estimate, Time Spent and Original Estimate fields.

Based on the answer on this post you could create a rule that detects a change to the Original Estimate field.

And you could create another rule that uses the Work Logged trigger to detect a change to Remaining Estimate and Time Spent.

Time Spent and Remaining Estimate for an issue can be accessed using the {{issue.timetracking}} smart value.

You could use an Advanced Compare Condition to compare the sum of the two fields to the value of the Original Estimate field.

You can use the Log action to print out field and smart values to the Audit Log for the rule to double check the values. Note that the values for these fields may be stored as seconds in the database which is why you might want to print them in the Audit Log to double check the actual values that are used in the Automation Rule.

And finally, you would use an Edit action to update your custom field.