Date Comparison

[Derek Kauble]

I need to run a JQL query where I can compare the created date to the statusCatagoryChangedDate.  Basically I'm looking for the issues that were completed in 31 days or less as a metric, so something like createdDate >= 'catagoryChangedDate - 31'.  Unfortunately that doesn't work because that's not a date value.  Any help on how to return this info would be amazing.  

[Valeriia_Havrylenko_SaaSJet]

Hi @Derek Kauble 👋

Welcome to the Community!

Unfortunatelly, this type of query is not possible without 3party add-ons.

I suggest you to try Time in Status for Jira (developed by my SaaSJet team) with 7 types of status time reports including Status Entrance Date report. It shows the date a particular issue has entered each of a status (Resolved or Closed, etc.).

• set date or date ranges you need
• get dates when the issue has entered each of the statuses on the grid.

 
On the chart, it shows the number of tasks that have entered a status on a certain date - for example, 1 task out of 5 tasks has entered the Progress status for the first time on the 23-rd of May.

 

Also, you can export a report as XLSX or CSV files for further analysis.

Add-on has a 30-day free trial version and free up to 10 users. 
Please, let me know if you have any questions

Hope it helps 😌

[Bill Sheboy]

Hi @Derek Kauble -- Welcome to the Atlassian Community!

What you are trying to measure is often called Lead Time: the duration from request creation until completion, often measured in units of calendar or clock time (rather than only working hours).

Short answer: that type of query is not possible with out-of-the-box JQL.

Longer answer:

JQL cannot compare the values of different fields in a query, such as Created and Resolved date / time values.  The possible work-arounds for your scenario depend upon on how often you need this information and your willingness to spend money / do additional work.

• Need this one time
  • Export the issues to a spreadsheet and perform the comparison there using spreadsheet functions
• Need this often, and have money to spend
  • Investigate marketplace addons which extend the features of JQL for queries like this
  • Investigate marketplace dashboard gadgets which add this specific type of measurement
• Need this often, and do not have money to spend
  • Using automation rules, and perhaps a custom field or label, perform the calculation and save the results.  Then the built-in JQL features would support your analysis.
  • You appear to be using a Team-managed project.  If not, and you are using a Company-managed project, the Control Chart report could be used instead to see this measure.

Kind regards,
Bill

[Bloompeak Support]

Hi @Derek Kauble

Welcome to the Atlassian Community!

You can use cycle time (the time it takes for the team to start working on an issue and complete it) and/or lead time (the time interval between the moment an issue is requested to the moment it is completed) reports to get what you need. For cycle time and lead time reports, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For your case you can have a look at Cycle Time for Each Issue, Lead Time for Each Issue reports as examples.

For more detail, you can have a look at Cycle Time and Lead Time in Jira: Productivity Measurement with Two Critical Parameters article and Status Time Reports How to Videos.

If you are looking for a completely free solution, you can try the limited version Status Time Reports Free.

Hope it helps.

[Gizem Gökçe _OBSS_]

Hello @Derek Kauble ,

Welcome to the Atlassian Community!

There are a few common metrics you can use to measure and monitor your team's performance. Among these, the most frequently used metrics are Cycle Time and Lead Time. However, Jira does not provide this functionality out-of-the-box. The required data is available in each issue's history but you will need to use a marketplace app to get ready-to-go reports.

Cycle Time: Cycle time is simply the amount of time it takes for a unit of work to go from an In Progress state to a Completed state. The clock “starts ticking” on the cycle time as soon as the work moves from a “Ready” state to an “In Progress” state, and it stops ticking when that work item goes from an “In Progress” state to a “Completed” state.

Lead Time: Lead time is the amount of time it takes for a unit of work to go from an In Queue state (i.e. a customer has requested some work to be done) to a Completed state. The clock “starts ticking” on lead time as soon as the work moves from a “Ready” state to an “In Progress” state, and it stops ticking when that work item goes from an “In Progress” state to a “Completed” state. It includes, and is therefore always longer than, cycle time.

If these concepts sound good to you to use in your teams performance monitoring, let's talk about how you can turn these metrics into a report.

I can recommend Time in Status app, which is built by my team at OBSS. It is available for both Jira Cloud and Data Center. 

Time in Status mainly allows you to see how much time each issue spent on each status or each assignee. 

    

You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects for Jira Cloud.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

    

https://marketplace.atlassian.com/apps/1211756/

I hope you have the opportunity to review and use Time in Status.

In addition, I believe the use case whose link I have provided below will be useful for you to review.

How to get Lead Time or Cycle Time ? 

Kind Regards,

Gizem