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

Question

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!

Amarildo Pereira _Northstack Apps_ · Answer

There's no way to compare two fields directly in native JQL: a clause compares a field to a value or a supported function, never to another field. So "timeSpent greater than remainingEstimate" has no direct equivalent.

But it's worth separating two things, because I suspect the second is what you actually need.

1. The literal question: Time Spent greater than Remaining Estimate.

workratio = (timeSpent / originalEstimate) × 100. While the remaining estimate is Jira's auto-computed one (remaining = original - spent, which is what you get when nobody overrides "Remaining" while logging), "spent greater than remaining" reduces to "spent / original greater than 0.5", i.e.:

project = "YOUR-PROJECT" AND workratio > 50

This breaks in two situations: when someone types a value into "Remaining" while logging, and when the Original Estimate is edited after work has already been logged (from that point Jira keeps Remaining independent of Original). In either case remaining is no longer original - spent, and the filter can return items where spent is not greater than remaining. Items with no original estimate can't be evaluated by workratio at all and won't match; query "originalEstimate IS EMPTY" separately if you want to see those.

2. What Structure is actually showing you.

For an unresolved item without sub-items, Structure's time-tracking progress is spent / (spent + remaining), so it reaches 100% when remaining reaches 0, which, with auto-computed estimates, is exactly when logged work reaches the original estimate. Everything logged after that keeps it at 100% (resolution settings and aggregation over sub-items can change this, but that's the base case).

So the items you're describing are probably the ones where logged time has exceeded the original estimate:

project = "YOUR-PROJECT" AND workratio > 100

Add "AND remainingEstimate = 0" if you want only the ones with nothing left on the estimate.

3. The direct comparison, inside Structure.

Since you're already in Structure, you don't need JQL for this: add a Filter by Formula generator (or a Formula column) with:

timeSpent > remainingEstimate

That's evaluated per item against the real field values, so it keeps working even when someone has edited "Remaining" by hand, which is the case where the workratio shortcut breaks.

One last thing: workratio reads Jira's native Time Spent field. If any part of the team logs hours through a tool that keeps them in its own store, those hours may not be in the native field and the filter will quietly under-report. Worth opening one item's Worklog tab and comparing against your report before rolling this out.

Rustem Shiriiazdanov _Actonic_ · Answer

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 · Answer

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