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

    How to calculate response metric

    Marek Waszczak



      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.

        • Re: How to calculate response metric
          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

          • Re: How to calculate response metric
            Marek Waszczak

            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
              Marek Waszczak

              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
                  Marcus Sommer

                  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
                  Marek Waszczak

                  Ok so as you adviced




                  RowNo() as M_AGS.RowNumber,



                  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!