Custom Filter for Tickets that tool more than 7 days to resolve

[Kashif Rahman]

I am trying to create a filter to identify issues that took more than 7 days to resolve within the past 30 days. I attempted to use the following JQL query, but it did not produce the expected results.
" resolution = Done AND timespent > -7d and created >= startOfDay("-30d") ORDER BY resolved DESC "

Could someone please assist me in identifying the correct query?

[Rahul_RVS]

Hi @Kashif Rahman 

If you would be interested in a mktplace app for this use case, you can try out

Time in Status Reports 

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options. You can also group your statuses to define your own cycle / lead times.

For your specific requirement, the app allows to create your own status group to define resolution time, and then within the app itself you can filter the issues with resolution time of greater than "xx" days.

More details here.

Disclaimer : I am part of the app team for this add-on

[Vitalii_Bobak_SaaSJet]

Hi @Kashif Rahman 

It seems you want to create a filter to identify issues resolved within the last 30 days that took more than 7 days to resolve. While the JQL query you provided points in the right direction, it seems there's some misunderstanding regarding the use of timespent and how it calculates durations.

Unfortunately, JQL does not directly support calculating the time between statuses (e.g., "Created" to "Resolved") or resolution time durations. However, you can achieve this with add-ons. Here's how you can use them to solve your issue:

---

Solving with Time Metrics Tracker:

1. Track "Time Between Created and Resolved":

   • In Time Metrics Tracker, create a custom time metric that tracks the time between the Created status and the Resolved status.
   • Apply a filter for issues resolved in the last 30 days by using filters.

You can also set up highlights of values that exceed the permissible level, and in this case, you can receive issue and email notifications.

---

Correcting the JQL Query:

While Jira's native JQL lacks direct support for calculating durations like "Created to Resolved," you could adjust your query to this for basic filtering:

resolution = Done AND resolved >= startOfDay("-30d") AND created <= resolved - 7d ORDER BY resolved DESC

Explanation:

• resolution = Done: Ensures the issue is resolved.
• resolved >= startOfDay("-30d"): Filters issues resolved in the last 30 days.
• created <= resolved - 7d: Ensures the issue took more than 7 days from creation to resolution.

However, this query only works in limited scenarios and won't give you accurate results for issues with complex workflows (e.g., reopened tickets). 

---

Add-on developed by my by SaaSJet team. 

Also you can book a demo with our specialist to see add-on in action!

Hope this helps!

[Hannes Obweger - JXL for Jira]

Hi @Kashif Rahman

as you can see from previous replies, this is an area where many customers rely on solutions from the Atlassian Marketplace. If this is an option for you, I think you'd like the the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the time in [status], time between [status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes

[Monika Rani]

Hi @Kashif Rahman ,

You should try this JQL:

resolution = Done AND resolved >= startOfDay("-30d") AND created <= startOfDay("-7d") ORDER BY resolved DESC

[Kashif Rahman]

Hi @Monika Rani 

Thank you for your response, but it's still showing unexpected results. For example, there is a ticket that was created on 26th December and resolved on 30th December, but it is being picked up by the filter.

Let me know if you have other ideas in your mind. 

[Monika Rani]

Hi @Kashif Rahman ,

The condition created <= startOfDay("-7d") filters issues created more than 7 days ago. However, this doesn't guarantee that the issues took more than 7 days to resolve. To ensure the query captures issues that took more than 7 days to resolve within the last 30 days, you need a way to calculate the time between created and resolved. Unfortunately, Jira's native JQL cannot compute the difference between two dates.

Could you try Automation rule for that.

Set up an automation rule in Jira to tag issues that take more than 7 days to resolve. Then filter issues using that tag:

• Automation rule:

  1. Trigger: Issue is resolved.
  2. Condition: {{issue.resolutionDate.diff(issue.created, "days")}} >= 7.
  3. Action: Add a label (e.g., took_more_than_7_days).

• JQL Query:

  resolution = Done AND resolved >= startOfDay("-30d") AND labels = took_more_than_7_days ORDER BY resolved DESC

  Let me know if it will help you.

[Kashif Rahman]

HI @Monika Rani 

Thanks for your reply.

I have used the following rule:

When: Issue transitioned
Completed, Done, Published

If: Compare two values
Checks
{{issue.resolutionDate.diff(issue.created, 'days')}} is
greater than 7

Then: Edit issue fields
Labels

However, I am getting the following error:

{{smart values}} condition

Unable to render smart values when executing this rule:

Failed to get value for issue.resolutionDate.diff(issue.created, 'days')

Please let me know if I have created a correct rule.


Thanks 
Kash

[Gizem Gökçe _OBSS_]

Hello @Kashif Rahman ,

The JQL query you provided doesn’t yield the expected results because the timespentfield represents the total logged work time, not the actual time an issue spent between its creation and resolution. Unfortunately, Jira’s native JQL doesn’t support date arithmetic or calculations like determining the duration between two timestamps directly.

If you’re open to using a third-party app, I recommend trying Timepiece - Time in Status for Jira, developed by my team at OBSS. Timepiece can calculate the duration an issue spent between specific events, such as its creation and resolution. You can easily generate reports to filter and analyze issues resolved within a specific time range and durations exceeding 7 days.

With Timepiece, you can:

1.Create a Duration Between Statuses report to calculate the exact time between the Created event and the Resolved/Closed/Done status. 

2. Apply filters to identify the resolution time exceeds 7 days.

If you'd like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or schedule a demo with us for a walkthrough of the app's features.

Hope it helps!

Gizem

[Kashif Rahman]

Hi @Gizem Gökçe _OBSS_ 

Thanks for your reply. I will try the app. 

[Patricia Modispacher (appanvil)]

Hi @Kashif Rahman

could you try: 

resolution = Done AND resolved >= startOfDay("-30d") AND resolved - created > 7d ORDER BY resolved DESC

I guess this could do the trick, since I think timespent refers to the worklog hours, not the calendar duration.

[Kashif Rahman]

Hi, 

The JQL has the following error

Expecting an operator but got '-'. The valid operators are '=', '!=', <, >, <=, >=, '~', '!~', 'IN', 'NOT IN', 'IS' and 'IS NOT'. (line 1, character 67)