How to calculate response metric

[waszcma1]

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.

[marcus_sommer]

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

[waszcma1]

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

[waszcma1]

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

[marcus_sommer · Accepted solution]

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

[waszcma1]

Ok so as you adviced

M_DISPATCH:

Load

RowNo() as M_AGS.RowNumber,

M_AGS.value,

task.sys_id,

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

Resident M_AGS order by task.sys_id,M_AGS.start;

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

Now I need add big loop for all tasks

Thank you!