Average time calculation per day if tickets with same field

Questions & Answers
, , , ,
1

Hi,

I have report generated with sum of issues downtime between Open to Re-test transition status as per below queries

CoalesceEmpty(Sum(
Filter(
DescendantsSet(
[Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues created] > 0
),
DateDiffWorkHours(
[Measures].[Issue created date],
([Measures].[Transition to status last date], [Transition Status].[Re-Test]),
‘default’,
‘07:00 - 17:00’
)
),0)

Issues filter that I am doing in dimension as ‘Resolving Team’

Problem is that we can have multiple issues reported for day and that may be resolved by same Resolving Team in same time in a day(which means issues are due to same root cause), so I wanted to overlap/average the time between the tickets/Issues for the day based on resolving team.
Note: Above criteria(in script) for business hrs should be applicable still for my request.

Example: total five issues reported for a day
consider 3 issues resolved by same resolving team ‘ABC’
1st Issue start and end time - 07 AM to 12PM - 5hrs downtime
2nd Issue - 09:00 to 15:00 - 6hrs of downtime
3rd Issue - 8:00 to 14:00 - 6hrs of downtime
In total downtime should be 8hrs

And Other two resolved by 'DEF’Team
1st Issue start and end time - 08 AM to 12PM - 5hrs downtime
2nd Issue - 09:00 to 14:00 - 6hrs of downtime
In total downtime should be 6hrs

Let me know if we have solution for my request :slight_smile:

2

Could anyone suggest on my request please?

3

Hi @kalaiarasan18,

To accurately calculate the averaged downtime without overlapping time, it would require a more complex calculation that considers each issue’s specific start and end times and compares them with the rest of the issues to find the overlapping periods. This level of analysis is not feasible with calculated measures in the report.

That kind of situation might be better handled with scripted fields in Jira that would start and stop the “clock” for downtime.

Best,
Zane / support@eazyBI.com