Statistics: calculating avarage answer times for tickets
The suits from management in my company want statistics showing the avarage time it takes to handle tickets in the help desk. That is the avarage time span from ticket creation to the ticket is closed. I do that by firing the following sql-statement in the Select Box (Admin Area): select sec_to_time(avg(unix_timestamp(change_time) - unix_timestamp(create_time))) from ticket where unix_timestamp(create_time) > unix_timestamp('2004-05-01 00:00:00') and unix_timestamp(create_time) < unix_timestamp('2004-05-31 00:00:00') and ticket_state_id = 2 The example shows the avarage answer time for may 2004 (the result is in: hh:mm:ss) I am intersted in knowing if anybody is dealing with the same kind of statistics and hearing if there is a better way to retrieve the same information. Elsewise I just want to share my endeavour with whoever is interested. Regards Stig
On Friday, June 04, 2004 4:37 PM
Stig Graasbøl Rasmussen
The suits from management in my company want statistics showing the avarage time it takes to handle tickets in the help desk. That is the avarage time span from ticket creation to the ticket is closed.
I do that by firing the following sql-statement in the Select Box (Admin Area):
select sec_to_time(avg(unix_timestamp(change_time) - unix_timestamp(create_time))) from ticket where unix_timestamp(create_time) > unix_timestamp('2004-05-01 00:00:00') and unix_timestamp(create_time) < unix_timestamp('2004-05-31 00:00:00') and ticket_state_id = 2
The example shows the avarage answer time for may 2004 (the result is in: hh:mm:ss)
I guess the example does show the average duration of a closed ticket's life spawn from its categorical beginning in may (means some specific month, yes) until it was last modified the same month. This is not what you want, is it? What about a ticket having been created on 04-28 and having been closed on 05-01? Or the one having been 'living' from 01-03 until 07-24? And the numerous ones layered between them?
I am intersted in knowing if anybody is dealing with the same kind of statistics and hearing if there is a better way to retrieve the same information.
There have been some people around willing to work on the development of some statistical ideas, yes. If I'd find the time... With kindest regards, Robert Kehl -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Tel. +49 (0)6172 4832388
MessageStig You seem to have the technical extraction ok assuming that change_time is a reliable indicator of a ticket close event. You would naturally want to be sure that nobody had changed the system clock during this period. ;) However, before the suits get ready to "do" something with this statistic, allow me to make an impassioned plea: "Average" is very unlikely to be a valid measure of "answer times", and unless you first analyze what the data distribution is, using "average" would be no more reliable than reading chicken intestines. You will be VERY sensitive to outliers and abnormal events. First study the distribution and check for skew and kurtosis. Other measures less sensitive to skew and variation are: 95- percentile, i.e the time taken for 95% of cases to be handled The percenage of cases that were solved within a target timeframe. median (50 percentile) These are far better measures than "average" P.S. I wrote an article on this for S-Business magazine that appeaed in the Q1 edition. Kind regards, Matthew H. Loxton ----- Original Message ----- From: Stig Graasbøl Rasmussen To: 'otrs@otrs.org' Sent: Friday, June 04, 2004 8:37 AM Subject: [otrs] Statistics: calculating avarage answer times for tickets The suits from management in my company want statistics showing the avarage time it takes to handle tickets in the help desk. That is the avarage time span from ticket creation to the ticket is closed. I do that by firing the following sql-statement in the Select Box (Admin Area): select sec_to_time(avg(unix_timestamp(change_time) - unix_timestamp(create_time))) from ticket where unix_timestamp(create_time) > unix_timestamp('2004-05-01 00:00:00') and unix_timestamp(create_time) < unix_timestamp('2004-05-31 00:00:00') and ticket_state_id = 2 The example shows the avarage answer time for may 2004 (the result is in: hh:mm:ss) I am intersted in knowing if anybody is dealing with the same kind of statistics and hearing if there is a better way to retrieve the same information. Elsewise I just want to share my endeavour with whoever is interested. Regards Stig ------------------------------------------------------------------------------ _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting für Ihr OTRS System? => http://www.otrs.de/
participants (3)
-
Pental -
Robert Kehl -
Stig Graasbøl Rasmussen