Average of time

[Caspermay14]

Hi,

I am really new to Crystal Reports. Please help.

I need to calculate the average of Call duration by Summary
The input is Excel Sheet. I need to feed this in Crystal Reports and then calculate the Average of Call Duration by Summary

Date Audio Length Regarding
9/14/2010 14:56 Password Manager: Password Reset Question (sync issue -ldap)
9/14/2010 8:03 Dell Skycap: Keyboard (calling dell) (logged as Dell CS2: Keyboard)
9/14/2010 8:00 BlackBerry: Activation Request
9/14/2010 10:24 Windows XP: Driver - Printer (print driver) (logged as Laser Printer: Install / Setup)
9/14/2010 8:41 Outlook Client: View Question
9/14/2010 5:35
9/14/2010 15:56 Laser Printer: Not working properly
9/14/2010 2:24
9/14/2010 6:42 Password Manager: Account Locked (logged as Login Gate Reader: Login Error)
9/14/2010 11:59 BlackBerry: Activation Request
9/14/2010 1:25
9/14/2010 17:13 Login Documentum: Unable to Login
9/14/2010 10:57 Rogue PC Pre-Authorization
9/14/2010 14:23 Login Windows XP: Unable to Login
9/14/2010 7:42 Password Manager: Password Reset Question
9/14/2010 19:47 Windows XP: Security Patch
9/14/2010 8:55 Password Manager: Account Locked (not accepting challenge answers)
9/14/2010 8:30 CATTS: Routing problem
9/14/2010 7:53 Exchange (Outlook): Mailbox Unavailable (logged as Login Outlook Client: Unable to Login)
9/14/2010 19:33 CSD Calls: Help Desk Case Status Inquiry
9/14/2010 34:52:00 Windows XP: Workstation Locked
9/14/2010 11:39 Outlook Client: Not Launching (out of sync)
9/14/2010 10:59 Internet Explorer: User needs access to Streaming Media on the Internet (expense report training)
9/14/2010 7:51 Windows XP: Profile - Error
9/14/2010 16:13 Kiosk: Express Bag Drop
9/14/2010 9:34 Windows XP: Missing Shortcut (outlook)
9/14/2010 16:20 Outlook Client: Not Launching (logged as Login Outlook Client: Unable to Login)
9/14/2010 34:11:00 Login Windows XP: Tree or Server Cannot be Found
9/14/2010 9:14 Toshiba: Install Drivers
9/14/2010 10:29 ATP: CS2 Ticketing Printer
9/14/2010 8:17 LMS Ground Ops: Online Course/Testing not working at SWA location
9/14/2010 9:50 Password Manager: Account Locked (logged as Password Manager: Password Reset Question)
9/14/2010 7:20 Windows XP: Performance: Running slow
9/14/2010 17:04 Windows XP: Install Application (presenters toolkit)
9/14/2010 14:57 Exchange (Outlook): Mailbox Unavailable
9/14/2010 22:27 CSD Calls: General Operational Question (whcich files to move to home drive prior to pc refresh)
9/14/2010 13:43 Laser Printer: Replace
9/14/2010 9:58 Windows XP: Uninstall Application (screen saver)
9/14/2010 8:25 Password Manager: Password Reset Question (sync issue)
9/14/2010 8:30
9/14/2010 12:40 Login SWALife: Authorization Failed - Not a Password issue
9/14/2010 25:36:00 Visio: Install

 

[IanWaterman]

Asuuming your audio length is a string you can convert to seconds, but string length must be consistent you have a mix of 5 and 7 characters.

using left, and mid you can convert hours and mins to seconds, this will work for both 5 and & but ignore seconds

@seconds

//hours
(tonumber(left({audiolen}, 2))*3600) +

// mins

(tonumber(mid({audiolen},4, 2))*60)

You can then average this formula and convert back to HH:MM

@avg time

local numbervar hours;
local numbervar mins;

hours:= truncate(average({@seconds})/3600);
mins:= truncate(remainder(average({@seconds}),3600)/60);

totext(hours,0,"")&":"&totext(mins,"00",0,"")

Ian

 

[Caspermay14]

Ian,

I am very new to Crystal reports and this is my first report. The info you provided is really helpful, but I need more details.

1) Should I be converting the time format to String within Crystal Reports or within excel Sheet?
2) I am told to ignore the text within parenthesis in Summary field and any row that has blank in Summary field. What does that mean? Again, if I have to parse it, is it in report level or within the tool

Thanks a ton!

 

[IanWaterman]

Probably easiest to convert time field to a string in Excel.

Not for me to really interpret your specification, please request clarification. However, I would guess that your select statement should filter out records where the summary field is null or is like '*(*' ie contains brackets.

Ian