JIRA Time Tracking - How to filter Issues where logged time > remaining time?

[Jamil Wahbeh]

Hi all

Via JQL "workratio" I can filter issues where time logged is greater than original estimates. I need a JQL to filter issues where the logged time is greater than the remaining estimates.

Reason: In JIRA Structure the % of progress is based on logged work and remaining estimates. But as soon as the logged work matches the remaining estimates, the progress is set to 100%, indicating its finished. But if you continue logging time on this issue, it will stay (for good reasons) on 100%. I need to find those issues, where more time than the remaining effort was logged.

Many thanks for any support provided on this manner!

[Rustem Shiriiazdanov _Actonic_]

Hi @Jamil Wahbeh,

If you're open to 3rd party apps, this can be solved with Report Builder for Jira. You can set up a table report with "Sum of worklog duration" and "Sum of Remaining Estimate" as calculated values, then use Conditional Formatting to highlight cells where logged time exceeds the remaining estimate (condition: Greater than → Sum of Remaining Estimate). This way you instantly see which issues have gone over.

If there's demand, we can also extend the app to not just highlight but actually filter out only those issues, so you'd get a clean list of the outstanding issues (that's a matter of 1 hour of work, really). Let me know if that would be useful!

Regards,

Rustem from Actonic Products

[Bartek Szajkowski _ Orbiscend OU]

Hello @Jamil Wahbeh 

I'm Bartek from Orbiscend OU

If you are open for a third-party app, I would like to recommend to check ARGON - Powerful JQL Search with wide range of fieldValue function.

The fieldValue function (ex. Date fields, User fields, Duration fields, etc.) enables you to extract and compare values from specific fields in issues that match a given query. This is useful for comparing field values across issues or between different fields.

ex. Finding over-logged issue

timespent > fieldValue("project = DEV", "timeoriginalestimate")

All fieldValue functions and overview you can find in app documentation on Marketplace.
ARGON Powerful JQL Search 

I hope that with my clues, you will find the best solution for yourself.

Greetings
Bartek from Orbiscend OU

[Thiago Wenceslau -Appfire-]

Hi @Jamil Wahbeh 

I’m Thiago, a support engineer at Appfire.

Wouldn't the Remaining Estimates decrease as you increase the Time Logged, thus matching the criteria at 50% of Time Logged? In this case, you should be comparing the Time Logged with the Original Estimate, since its value won't change when Time Logged increases.

Also, if you are considering third-party apps, there’s a workaround using Power Scripts:

// This script returns issue keys where Field A is greater than Field B
string[] matches;
string[] issues = selectIssues("project = KAN");

for(string k in issues) {
// Access fields using the %key%.field syntax
if(%k%.timeSpent > %k%.estimate) {
matches += k;
}
}
return matches;

In this script, we fetch issues which Time Logged are greater than the Remaining Estimates

You can swap the %k%.estimate to %k%.originalEstimate on line 7 if you wish to compare to the Original Estimate instead. Make sure you correctly set the target project on line 3.

Additionally, you could integrate this script to a gadget or schedule a daily email containing the list of issues.

Please contact our support if you have any other questions about this.

Best regards, Appfire support team.

[Trudy Claspill]

Hello @Jamil Wahbeh 

Welcome to the Atlassian community.

Jira Data Center does not natively support creating JQL where the criteria is based on comparing one field to another. If you have third party apps (like ScriptRunner) that extend JQL capabilities, then it might be possible.

Also note that Remaining Estimate is a calculated value that can be changed. Initially when no time has been logged Remaining Estimate is equal to Original Estimate. But as time is logged the user can opt to manually change the Remaining Estimate, indicating that even more time is required or even less time is required (where otherwise the time logged would simply be subtracted from Remaining Estimate to get the new Remaining Estimate).

What problem are you actually trying to solve? If you are able to get a report as you want, what will you do with the information? Understanding that will help us provide you with suggestions.

[Jamil Wahbeh]

Thanks for reply @Trudy Claspill. Yes, the remaining estimates field is changing, but not only because of logged time, but because of re-estimates which could lead to an either increased or decreased remaining effort while the original estimates remain the same.

I would like like to find those issues, where the remaining effort has not been updated due to increased effort by logging more time than remaining effort would indicate.

We have ScriptRunner as well but I got told that this wouldn't help in my case.

---

Example:

Original Estimates: 10w

Remaining Estimates: 10w

Time logged: 15w

In this example the structure progress would be set to 100% which tells me the Feature/Story is finished effort wise. However, I wouldn't know, that 5w more have been logged for this issue (which is from a PM point of view important to understand, where the capacity was spent). I can derive this information somehow from JIRA structure by looking on each issue every day, hence a JQL would provide an easier access to this information.