Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Pavol Procka
    Average answer time for emails replied
    Topic posted June 21, 2017 by Pavol ProckaSilver Crown: 22,500+ Points 
    110 Views, 9 Comments
    Average answer time for emails replied

    Hi everyone,


    I wonder if anyone has a report they can share that would do the following:

    I am looking for a quick way how to find out how many e-mails were replied yesterday from specific Qs and what was the average answer time, or even better, how many were replied within 8, 12, 24, 48 and 72 hours.

    Many Thanks




    • Carl Elliott

      You can start with the Agent Transaction report, ID 49.  Use the sent transaction and it will show you how many responses have been sent.  You can change the Agent out to queue and on the 2nd level you can add a date_diff column to calculate the time.  You could add some columns to count and group them into specific time frames. 

    • Pavol Procka

      Hi, thanks for the tip. Unfortunately I am quite new to reporting and as such am mostly working with the method of trial and error at the moment.

      After struggling a bit with your instructions, I actually managed to find a report that offered me the aggregated SLA columns, just needed to tweak it to my specs.

      I so far ended up with a report that shows almost all I need. Btw., turned out what I wanted was responses per languages, not per Qs.

      I still have 2 issues I am struggling to resolve:

      1. To add a column showing the Average Response time for each language. Did not manage to find an expression, I guess it needs to be coded manually?

      2. Currently, the report is showing what I need per language, however, I would need to add another grouping:

                        a) I would need the report to show the numbers it is showing for accounts that have MV priority value 80+ only

                        b) and separately, for each language all the others (MV priority < 80)

      Ideally both on one page. I am not even sure whether it is possible to "add" such a split at this stage.

      I am attaching my report's definition, I would be very grateful if anyone took the time to look through hit and suggest a solution.

      Many thanks


    • Carl Elliott

      You can use the calculations from the Incident Performance or Agent Performance reports.  Here is one of the calculations, it does have an extra check for inc-performance type if your report includes more than one type record.  You don't have to put the time format in the calculation you can use the format option in the report controls for the column.  

      to_number( to_char( sum( if( inc_performance.intv_type = 1, date_diff( inc_performance.time_end, inc_performance.time_start ) ) ) / if( sum( if( inc_performance.intv_type = 1, 1 ) ) <> 0,  sum( if( inc_performance.intv_type = 1, 1 ) ) ) ) )

      To split out your other variable put in a column before the response time that looks something like this:  if(MV_priority < 80,'Less 80','Greater 80') This will give you a split of data with these two values. 


    • Pavol Procka

      OK, I think I just over-combined here.

      I managed to get the Rows and Columns I need. However my report is off by a few incidents in each Q. The problem is, that I cannot figure out how to get them all in. What I need is "All emails that were responded to yesterday". I believe I screwed by using the  inc_performance.

      Unfortunately, I now arrived at a spot where I am not sure what to do next. Basically, I would need to have exactly the same incidents as those that are from the attached report (all_responded.xml)

      However, the report I need (Pavol.xml) also needs to contain the aggregate SLA values (percentage responded in 8,12,24 etc hrs)

      Any suggestions?

    • Pavol Procka

      Here the report I am working on.

    • Pavol Procka

      I am also attaching a picture of what I would like to end up with. It is a screenshot, cause at the moment I only have a raw (it is the one I attached the definition of earlier here)report showing all responded incidents with the Qs and then use a Pivot table in Excel on the results to get what is on the screenshot.

      Any help would be greatly appreciated!

    • Danette Beal

      Hi Pavol,

      Make sure to check out the analytics cookbook, you shouldn't have to resort to trial and error! 

      Good Luck!

      Danette, Community Manager

    • Pavol Procka

      Hi Danette,

      I agree with you, unfortunately I also do not have the budget to afford the Paid Trainings and as such - as a complete newbie to reporting - I have to learn a lot with either Ad-Hoc training for separate tasks (we do have a senior admin here who had the training and knows his way around), however it is not his main job to train me or the Trial and Error method.

      I am unable to access most of the Cookbook's resources as they are on external websited (e.g. Youtube), where my access is very limited.

      I can of course watch them from home, but then I unfortunately do not have a way to test/try what I have seen immediately, can only do so the next day at work, where I do not have the video with me.


    • Carl Elliott

      Ok I am not going to write the report for you but I will give you some more pointers.  

      1. You have too many Inc-performance types.  Do you want to measure Create to Response or Queued to Response?  You have Queue to Response (type 12) in your calculations but then you have create to response in your filter.   Decide which one you want to measure and include only one Inc-performance Type in your filter, then take it out of your columns.  You don't need it there if you only select one record type in your filter.  If you pick type 1 there is only one record if you pick type 12 there could be multiple records. 

      2. Are you measuring Relative time or actual time.  You are calculating relative time in your hourly calculation but you are using actual time in your total response.  If you want to use relative time all you need is the field Inc-performance.Rel_time.   Use this formula  sum(inc_performance.rel_time) / count (distinct Inc_performance.i_id)  for the total.  Then use the Right-click Column Format option to set the format. 

      3. Your hourly calculations are off because you are only using the top-end.  After the first one you need a > and a < in each calculation.   That way your totals will add up to 100%  And use the Rel_time or you have to calculate the actual time with the start and end times. 

      4. I gave you this earlier but put this in as your first column in the row to split out the two groups.  if(MV_priority < 80,'NORM','HV'),  Do you need the rollup?