Advanced Search Filter

Question

I am looking to view a personal report on a project for myself but I am not sure about how I would go about it using the advanced search filter. The filter I am looking to create is below;

I want to find out over the last 6 months, how many tickets in a project, that were assigned to me were resolved with 24 hours of assigned or created timestamp.

I am looking to do this so I can compare the number that's produced to the overall number of tickets that have been assigned to me within the last 6 months. I can then work out the percentage of tickets that have been answered within 24 hours.

Thanks for any help in advance

Marlene Kegel - codefortynine · Answer

Hi @Rhys McAleenan ,

We recently released Dynamic Fields for Jira - a Jira cloud app that might help to create your report.

Our app enables you to extract data from your issues and their history and display it within the issue.

With Dynamic Fields you could create a field for all resolved issues with the number of hours between last assignment (or creation date as a fallback) and resolution date. This is how the expression would look like:

issue.resolutionDate ? ((issue.resolutionDate.getTime() - (issue.changelogs.filter(changelog => changelog.items.some(item => item.fieldId == 'assignee'))[0] || {created: issue.created}).created.getTime()) / 3600000) : null

You could then filter for the issues in the past 6 months that were assigned to you and were resolved within 24 hours using JQL:

created >= -180d AND assignee = currentUser() AND DYNAMIC_FIELD <= 24 (where DYNAMIC_FIELD is the field you've created using he expression above.)

If you need help in creating your own custom dynamic field, don't hesitate to get in touch with our support.

Andy Heinzer · Answer

Hi Rhys,

I understand you're using Jira Service Desk Cloud and want to be able to see which issues you have resolved within 24 hours that were assigned to you.   This is actually a bit harder to do than it might seem on the surface.

Jira Core and Jira Software projects won't be able to do this via JQL alone.  This is because they cannot do this kind of dynamic date comparison between two date fields on each issue.  There is an existing feature request that if implemented would make this much much easier over in JRACLOUD-20727.

That said, Jira Service Desk though has SLAs, these allow you measure service levels provided to customers.  We can potentially use these instead to measure this time.

The problem with using SLAs here, is that they are being actively computed.  Meaning that it isn't feasible to setup an SLA to measure issues that have already been resolved.  So ultimately, I think the long term solution here is to create your own SLA, with a timer of 24 hours, and you'll also want to use a 24/7 calendar for this SLA otherwise the SLA could pause itself at the end of the standard 8 hour workday.  Steps to do that are in Create a new calendar.  The goal should be issue resolution, while the triggers could be both issue created and issue assigned.

Once that is setup, then going forward from that date, it should be easier to figure out which issues are meeting that SLA and which are not in JQL.

As for how to try to figure this out for your existing issues, well, I have given that some thought too and since JQL is not going to really be helpful here I think the next best way to figure this out would be within an external tool, like a spreadsheet.  Instead I have been looking into using a JQL query to just find the issues assigned to you such as

assignee=currentuser() order by resolved

And then exporting those issues into a spreadsheet or using the google sheets add-on.  Provided you export both the created date and the resolution date, you can then create an additional column that compares these two values to figure out of they met your criteria or not.  One way to do that is to create a formula such as

=D1-C1

where column D is resolution date, and column C is created date.  In Google sheets, you can then change the formatting of that cell such as Format -> Number -> Duration.  This will show you in hours:minutes:seconds the time between the two.  So anything under 24 hours would meet that criteria here.

Sorry there does not appear to be a better way to manage this for existing issues in Jira.  I hope this helps.

Andy