Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
waszcma1
New Contributor III

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
IDValueStart dateTask
row001Group A01/01/2016 09:00INC00001
row002Daniel W.01/02/2016 09:20INC00001
row003Group B01/03/2016 15:00INC00001
row004Group C01/03/2016 16:00INC00001
row005Peter S.01/05/2016 10:00INC00001
row006Group B01/06/2016 09:00INC00001
row007Alan T.01/07/2016 09:20INC00001
row008Group D01/08/2016 09:00INC00001
row009Group A01/09/2016 09:00INC00001
row010Daniel W.01/10/2016 09:20INC00001

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.

1 Solution

Accepted Solutions

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

5 Replies

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

waszcma1
New Contributor III

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

waszcma1
New Contributor III

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_IDValuemetric startmetric enddefinitonIncident
1Service Desk19/11/2015 13:20:4030/11/2015 11:24:32Assigment groupinc00001
2USER 00126/11/2015 16:32:4230/11/2015 11:24:32Assigned toinc00001
3Service Desk tier 230/11/2015 11:24:3210/12/2015 11:30:54Assigment groupinc00001
4USER 00230/11/2015 11:28:3210/12/2015 11:30:54Assigned toinc00001
5Service Desk tier 310/12/2015 11:30:5405/01/2016 11:09:06Assigment groupinc00001
6USER 00305/01/2016 09:11:1205/01/2016 11:09:06Assigned toinc00001
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_IDValuemetric startmetric enddefinitonIncident
1Service Desk19/11/2015 13:20:4030/11/2015 11:24:32Assigment groupinc00001
2USER 00126/11/2015 16:32:4230/11/2015 11:24:32Assigned toinc00001
3USER 00230/11/2015 11:24:3210/12/2015 11:30:54Assigned toinc00001
4Service Desk tier 230/11/2015 11:24:3210/12/2015 11:30:54Assigment groupinc00001
5Escalation group10/12/2015 11:30:5405/01/2016 09:11:12Assigment groupinc00001
6Service Desk tier 305/01/2016 09:11:1205/01/2016 11:09:06Assigment groupinc00001
7Service Desk tier 205/01/2016 11:09:0608/01/2016 14:10:04Assigment groupinc00001
8USER 00205/01/2016 11:35:3808/01/2016 14:10:04Assigned toinc00001
9Service Desk tier 308/01/2016 14:10:0411/01/2016 09:42:39Assigment groupinc00001
10Service Desk tier 211/01/2016 09:42:3911/01/2016 10:06:49Assigment groupinc00001
11USER 00211/01/2016 09:52:3311/01/2016 10:06:49Assigned toinc00001
12Service Desk tier 311/01/2016 10:06:4911/01/2016 11:31:15Assigment groupinc00001
13Service Desk11/01/2016 11:31:1527/01/2016 18:00:14Assigment groupinc00001
14USER 00111/01/2016 14:50:1227/01/2016 18:00:14Assigned toinc00001
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

waszcma1
New Contributor III

Re: How to calculate response metric

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 definitionValueM_AGS.startdispatch_time
Assignment Group (incident)Service Desk19/11/2015 13:20:400
Assigned to DurationUSER 00126/11/2015 16:32:427.1333
Assignment Group (incident)Service Desk tier 230/11/2015 11:24:320
Assigned to DurationEscalation group30/11/2015 11:24:320
Assignment Group (incident)Escalation group10/12/2015 11:30:540
Assignment Group (incident)Service Desk tier 305/01/2016 09:11:120
Assignment Group (incident)Service Desk tier 205/01/2016 11:09:060
Assigned to DurationUSER 00205/01/2016 11:35:380.01842
Assignment Group (incident)Service Desk tier 308/01/2016 14:10:040
Assignment Group (incident)Service Desk tier 211/01/2016 09:42:390
Assigned to DurationUSER 00211/01/2016 09:52:330.00687
Assignment Group (incident)Service Desk tier 311/01/2016 10:06:490
Assignment Group (incident)Service Desk11/01/2016 11:31:150
Assigned to DurationUSER 00111/01/2016 14:50:120.13815

So this is exactly solution of my problem

Now I need add big loop for all tasks

Thank you!

Community Browser