Count of breached tickets with deviated hours

Questions & Answers
1

Hello,

I would like to create a report that counts the number of tickets that breached and exceeded the SLA Goal by 8 hrs, 16 hrs, 30 hrs…etc. How can I create this report? So for example if the “Time to Resolution” shall be 20 hrs and two tickets were resolved in -3hrs then a count of 2 shall appear under “<8hrs”.

2

Hello @simona,

Welcome to the eazyBI community!

The calculation depends on the available data from the SLA solution you are using.
In the case of Jira Service Management, we import the measure “ elapsed hours” that could be used within the calculation.
Please read more about standard JSM custom field measures here - SLA measures.

The calculation of issues that breached SLA by a certain amount implies two conditions:

  1. issue breached SLA
    AND
  2. issue breached SLA by a specific amount of time.

The basic expression for issues breaching SLA under 8 hours might look as follows.

Sum(
  Filter(
    DescendantsSet(
      [Issue].CurrentHierarchyMember,
      [Issue].[Issue]),
    ([Measures].[Time to resolution Issues],
     [Time to resolution Breached].[Breached])>0
    AND
   ([Measures].[Time to resolution Elapsed hours]
--deduct SLA hours
-20
)<
--category
8
  ),
--numeric value for the sum
  [Measures].[Time to resolution Issues]
)

However, this will execute the calculation of issue relation to a specific breached category even if the issue has not breached the SLA.

Therefore, you might use the primary filter to see if the issue breached SLA and move the secondary - categorization filter - into the numeric part for the SUM.
The updated calculation might look as follows.

Sum(
  Filter(
    DescendantsSet(
      [Issue].CurrentHierarchyMember,
      [Issue].[Issue]),
--primary filter - breached issues      
    ([Measures].[Time to resolution Issues],
     [Time to resolution Breached].[Breached])>0),
--numeric value for sum - executed on already filtered set of issues
--secondary filter - category of SLA exceeded
  CASE WHEN
   ([Measures].[Time to resolution Elapsed hours]
--deduct SLA hours
    -20
    )<
--category
    8
  THEN
   [Measures].[Time to resolution Issues]
  END
)

The option for 8-16 hours exceeded might look as follows.

Sum(
  Filter(
    DescendantsSet(
      [Issue].CurrentHierarchyMember,
      [Issue].[Issue]),
--primary filter - breached issues      
    ([Measures].[Time to resolution Issues],
     [Time to resolution Breached].[Breached])>0),
--numeric value for sum - executed for already filtered issue set
--secondary filter - category of SLA exceeded
  CASE WHEN
--use upper limit including SLA hours
   [Measures].[Time to resolution Elapsed hours] < 36
    AND
--lower limit including SLA hours
   [Measures].[Time to resolution Elapsed hours] >= 28
  THEN
   [Measures].[Time to resolution Issues]
  END
)

Nesting of conditions and only executing heavier calculations on already filtered sets significantly improves calculation performance.

Regards,
Oskars / support@eazyBI.com