5 Replies Latest reply: Mar 2, 2016 5:19 PM by Marek Waszczak

# How to calculate response metric

Hi,

Could some one help me to find the solution how to calculate  response time from the data like this:

An example of the records for one Incident INC00001

 Metric Instance table ID Value Start date Task row001 Group A 01/01/2016 09:00 INC00001 row002 Daniel W. 01/02/2016 09:20 INC00001 row003 Group B 01/03/2016 15:00 INC00001 row004 Group C 01/03/2016 16:00 INC00001 row005 Peter S. 01/05/2016 10:00 INC00001 row006 Group B 01/06/2016 09:00 INC00001 row007 Alan T. 01/07/2016 09:20 INC00001 row008 Group D 01/08/2016 09:00 INC00001 row009 Group A 01/09/2016 09:00 INC00001 row010 Daniel W. 01/10/2016 09:20 INC00001

What I am trying to achieve is show the response/dispatch time per each group.

The problem is that I have limited data to the fields shown above

So the needed output is

Group A - 00:20 , 00:05

Group B - 02:10

Group C -

Group D -

Latter on with more records the result should be

Group A - avg(response time)

...

Group X - avg(response time)

The goal is to measure response time per single team especially if many teams were handled the same case.

Group C and Group D is not in the scope because there was no person assigned from they group so they can be skipped or counted as a zero

How to calculate response time for Group A (it can happen that ticket come back to the same team which already worked on the case and escalate to the next group?

This is only example for one Incident I have hundreds thousands of them and if I start using metric instance table with incidents details I will exeed millions records.

• ###### Re: How to calculate response metric

I don't understand completely the logic how the data are stored but I think you will need to check within a sorted load the previous records and create an additional field "End date" and a flag-field to differ the resulted usage- and garbage-records (deleting them within a further load). This could be achived with Peek() or Previous() ?

- Marcus

• ###### Re: How to calculate response metric

there are two tables one incident (contains: incident number, open, close, assigment group, assigned to, source ... etc.)

But there is only one record per incident. To see details what happen between open and close i have to use metric instance table where are many metric definition like assigment group, assigned to, status etc.

Unfortunately all metric definition are stored in the same table (metric_instance) so first thing I have preload metric_instance table with definition (assigment group and assigned to only)

There is a "value" field which contains assigment group name in case for metric definition Assigment group, or person for metric definition Assigned To.

Metric instance is connected with incident by field: incident number

• ###### Re: How to calculate response metric

This is really difficult task so i will try once again explain what is a problem.

There is one fact table called "incident" incident contains information about

• inc number, start date, end date, assignment group, assigned to, source, country, customer, ... and more

But one Incident can be handled by many assigment groups and by many people. Incidents table have always the latest  group and person who worked on the case. (updating only one record per incident)

There is another table called "metric_instance" which contains fields like:

• Metric definition: the metric definition for which this metric instance was gathered.
• Value : For a “Field value duration” metric this is the value of the table field for which duration is calculated. For example, for the “Assigned to Duration” metric, the Value is the name of the person assigned to the incident. For other metrics, the value can be any value calculated by the metric.
• ID : Identifies the specific record for which the metric is gathered. For example, the specific incident.
• Duration : Time duration for a Field value duration metric.

Duration is only measure the time between metric start and metric end

I need calculate difference between metric start from the row: M_ID: 2  and metric start from the row: M_ID: 1

So if we analyze handling process of an incident  in theory it should follow the steps:

An example

 M_ID Value metric start metric end definiton Incident 1 Service Desk 19/11/2015 13:20:40 30/11/2015 11:24:32 Assigment group inc00001 2 USER 001 26/11/2015 16:32:42 30/11/2015 11:24:32 Assigned to inc00001 3 Service Desk tier 2 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigment group inc00001 4 USER 002 30/11/2015 11:28:32 10/12/2015 11:30:54 Assigned to inc00001 5 Service Desk tier 3 10/12/2015 11:30:54 05/01/2016 11:09:06 Assigment group inc00001 6 USER 003 05/01/2016 09:11:12 05/01/2016 11:09:06 Assigned to inc00001 Case solved

So incident was assigned to Service Desk group, than USER 001 from Service Desk start work on the case

USER 001 decided escalate the case to the tier 2, in Tier 2 User from this group after some time decided also escalate case to the higher tier 3. USER 003 from the Tier 3 solved and closed the case.

Each action is stored in separate record.

Another problem is the order is not always like in this example sometimes case is handled like  we called ping-pong case

See example:

 M_ID Value metric start metric end definiton Incident 1 Service Desk 19/11/2015 13:20:40 30/11/2015 11:24:32 Assigment group inc00001 2 USER 001 26/11/2015 16:32:42 30/11/2015 11:24:32 Assigned to inc00001 3 USER 002 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigned to inc00001 4 Service Desk tier 2 30/11/2015 11:24:32 10/12/2015 11:30:54 Assigment group inc00001 5 Escalation group 10/12/2015 11:30:54 05/01/2016 09:11:12 Assigment group inc00001 6 Service Desk tier 3 05/01/2016 09:11:12 05/01/2016 11:09:06 Assigment group inc00001 7 Service Desk tier 2 05/01/2016 11:09:06 08/01/2016 14:10:04 Assigment group inc00001 8 USER 002 05/01/2016 11:35:38 08/01/2016 14:10:04 Assigned to inc00001 9 Service Desk tier 3 08/01/2016 14:10:04 11/01/2016 09:42:39 Assigment group inc00001 10 Service Desk tier 2 11/01/2016 09:42:39 11/01/2016 10:06:49 Assigment group inc00001 11 USER 002 11/01/2016 09:52:33 11/01/2016 10:06:49 Assigned to inc00001 12 Service Desk tier 3 11/01/2016 10:06:49 11/01/2016 11:31:15 Assigment group inc00001 13 Service Desk 11/01/2016 11:31:15 27/01/2016 18:00:14 Assigment group inc00001 14 USER 001 11/01/2016 14:50:12 27/01/2016 18:00:14 Assigned to inc00001 Case solved

The goal is start to measure response time per each Assignment group. So it is need in order to benchmark Team performance

• ###### Re: How to calculate response metric

I think if you sorted your data to the incident-id and the metric start and used then a within an if-loop a peek/previous - see link above - function to access the previous record you could calculate your response-times which would look like:

if(Incident = previous(Incident); [metric start] - previous([metric end]) as ResponseTime

- Marcus

• ###### Re: How to calculate response metric

M_DISPATCH:

RowNo() as M_AGS.RowNumber,

M_AGS.value,

M_AGS.start as M_AGS.start,

if(Previous(M_AGS.flag)=1 and Previous(M_AGS.flag)<>M_AGS.flag, M_AGS.start -Previous(M_AGS.start) ,0) as M_AGS.dispatch_time,

M_AGS.flag, // 1 for Assigment group metric definition, 0 for assigned to metric definition

M_AGS.end as M_AGS.end

the result is:

 Metric definition Value M_AGS.start dispatch_time Assignment Group (incident) Service Desk 19/11/2015 13:20:40 0 Assigned to Duration USER 001 26/11/2015 16:32:42 7.1333 Assignment Group (incident) Service Desk tier 2 30/11/2015 11:24:32 0 Assigned to Duration Escalation group 30/11/2015 11:24:32 0 Assignment Group (incident) Escalation group 10/12/2015 11:30:54 0 Assignment Group (incident) Service Desk tier 3 05/01/2016 09:11:12 0 Assignment Group (incident) Service Desk tier 2 05/01/2016 11:09:06 0 Assigned to Duration USER 002 05/01/2016 11:35:38 0.01842 Assignment Group (incident) Service Desk tier 3 08/01/2016 14:10:04 0 Assignment Group (incident) Service Desk tier 2 11/01/2016 09:42:39 0 Assigned to Duration USER 002 11/01/2016 09:52:33 0.00687 Assignment Group (incident) Service Desk tier 3 11/01/2016 10:06:49 0 Assignment Group (incident) Service Desk 11/01/2016 11:31:15 0 Assigned to Duration USER 001 11/01/2016 14:50:12 0.13815

So this is exactly solution of my problem