Measuring avg Resolution Days from assigned date to closed date

[Bobby Ervin]

Hi All,

I am looking to create a report in eazyBi that shows the average resolution days for my teams Jira tickets once it is assigned. I know I can calculate average resolution days from when a ticket was opened to when it was closed, but I am looking for this timer to start once the ticket is assigned. I have played around with creating a calculated measure, but can't seem to get the coding right. Thanks in advance!

FYI - I use Jira Server

[Oskars Laganovskis · Accepted solution]

Hi @Bobby Ervin,

The issue creation and closure are absolute dates regardless of the other context. However, the issue getting assigned to a specific team is a variable date that depends on both the issue and the assignee.
​Depending on your policy of issues being re-assigned while in progress, there are several options. Please see them below - they imply that the Assignee dimension is used in the report context in rows, columns, or pages.

​If you only permit an issue to be assigned to the team once - you might calculate the time between the issue creation and the first assignment to the team. The time from assignment to resolution on an issue level might then be as follows.
​

CASE WHEN
--check if the issue is resolved and relates to the current assignee
 [Measures].[Issues resolved]>0
THEN
--the actual claculation
 [Measures].[Total resolution days]
 -
--time until first assignment 
 DateDiffDays(
  [Issue].CurrentHierarchyMember.Get('Created at'),
TimestampToDate(
--defaultcontext resets the rest of dimensions
 DefaultContext(
--only the dimensions relevant to assignment are taken
   ([Issue].CurrentHierarchyMember,
    [Transition Field].[Assignee],
    [Assignee].CurrentHierarchyMember,
    [Measures].[Transition to first timestamp])
  ))
 )
END

​If you permit multiple re-assignments and look for the time since the last assignment to the team, then you might calculate the time between the last assignment and issue closure or resolution.
​

CASE WHEN
--check if the issue is resolved and relates to the current assignee
 [Measures].[Issues resolved]>0
THEN
--the actual calculation
 DateDiffDays(
--time since last assignment
  TimestampToDate(
--defaultcontext resets the rest of dimensions
   DefaultContext(
--only the dimensions relevant to assignment are taken
   ([Issue].CurrentHierarchyMember,
    [Transition Field].[Assignee],
    [Assignee].CurrentHierarchyMember,
    [Measures].[Transition to last timestamp])
  )),
--issue resolution date
   [Issue].CurrentHierarchyMember.Get('Resolved at')
  )
END

​
​These are calculations on the issue level. You need to wrap the relevant calculation within iteration through the Issue dimension to find the average for the set of issues.
​

Avg(
  Filter(
    DescendantsSet(
     [Issue].CurrentHierarchyMember,
     [Issue].CurrentHierarchy.Levels('Issue')),
    [Measures].[Issues resolved]>0
  ),
--numeric value for average - the calculation part of above expressions
  <use the code relevant to your policy>
)

​Regards,
​Oskars / eazyBI support

[Bobby Ervin]

@Oskars Laganovskis - thanks! I think the first code will be exactly what I need. Tickets can be assigned to multiple team members, but I am not worried about calculating how long it took to resolve a ticket since the most recently assignment, I want to look at how long it took to resolve a ticket once it was first assigned, regardless how many times it was assigned after that. 

That being said, we have multiple service desk within our organization so I have a Project filter on the Page to just look at our service desk. We denote which team the ticket is assigned to by using specific Components for each team so I can also add a filter to look at specific teams if need be. 

A couple things I want to clarify:

• is the first code only showing the time to resolution for the current assignee, i.e. whoever is looking at the report? I want to make sure the calculated member is taking the average of all tickets, not just a specific assignee.
• In order to calculate the average resolution days you mention that I need to wrap the relevant calculation to the issue dimension. Does that mean I need to take whichever calculation works for me and insert it in the <use the code relevant to your policy> portion of the avg calculation? Or do I need to define a new calculated measure and insert the previously defined measure into that equation? 

Thanks for the help!

[Oskars Laganovskis]

@Bobby Ervin 

The first code looks for the time since the issue is assigned to the Assignee (individual user or calculated member) applied in the report context.

You might have users of your team grouped in the calculated member e.g. "Team Alpha" and this calculated member selected in the page filter.

The third part is the "wrapping" - the code that performs iteration through the Issue dimension. It should be used around the primary expression according to your policy.

You might put the calculation on the issue level in a separate calculated measure and refer to it from within "wrap", or you might put the actual code within the "wrap"..

[Bobby Ervin]

Appreciate the quick response. I added both codes and it appears to be working as expected. Thank you so much for the help!

[Lucy Mae]

Breaking down the normal goal time in days can give important experiences into your group's presentation and assist with recognizing regions for development. Here are a few extra methods for estimating and investigating this information:

Separate the information by group or person: Assuming you have different groups or people dealing with cases, it tends to be useful to separate the information by group or individual to recognize any varieties in execution. This can assist you with distinguishing regions where extra preparation or assets might be required.

Search for exceptions: In any dataset, there might be anomalies or cases that take altogether longer than normal to determine. Recognizing these exceptions can assist you with understanding the purposes for the more drawn out goal times and do whatever it takes to forestall comparative postpones from here on out.

Contrast your presentation with industry benchmarks: Numerous enterprises have laid out benchmarks for normal goal times, which can give a valuable mark of correlation for your group's exhibition. In the event that your typical goal time is fundamentally higher than the business benchmark, it could be an ideal opportunity to audit your cycles and distinguish regions for development.

Use information perception devices: Information representation apparatuses like outlines and diagrams can make it simpler to distinguish patterns and examples in your information. Consider utilizing instruments like Succeed, Google Sheets, or Scene to make representations that can assist you with rapidly distinguishing regions for development.

Consistently survey and update your objectives: As your group's exhibition improves, it's essential to routinely audit and update your objectives for normal goal time. By setting aggressive yet attainable targets and consistently keeping tabs on your development towards those objectives, you can persuade your group and guarantee that you are ceaselessly working on your cycles.