Timestamp interpretation and manipulation

[#1]

Hello all, I have an excel file which has log activity for helpdesk. We have two fields - call creation time and call end time - which I would like to do manipulations on. The format of the field is as in this example "02/08/2010 11:54 AM". Since the data is being imported from excel, I'm not sure if it coming in as text or time. I'm having trouble finding the right function to interpret the field. These are things I want to do:

1) Determine the month from the create time so I can do anaysis on calls in a particular month
2) Determine the year from the create time
3) Determine the quarter from the create time
4) Subtract the create time and end time to analyze the response time for calls

I'm not able to figure out how to use the timestamp# function or others to do this. Please help.

Regards

[vgutkovsky · Accepted solution]

1. month(timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'))
2. year(timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'))
3. if(month(timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'))<4,1,if(month(timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'))<7,2,if(month(timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'))<10,3,4)))
4. timestamp#(endtimefieldname,'MM/DD/YYYY hh:mm tt') - timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt')

Cheers,

[#3]

Thanks, that worked. Now can you help me take this two steps forward:

1) I want to summarize response time (completion timestamp - create timestamp) into different segments in a bar chart, to clearly understand the service level being provided. So I need to know how many calls were answered in the following agreed time slots: <1hr, <3hr, <5hr, <1day, <3days, >3 days. What is the best way for doing this?

2) I also want to create a chart which compares calls answered by each of the 5 technicians in the month that just got completed (i.e. Aug) with the previous month (i.e. Jul). Each call is answered by 1 technician, so a row of data has technician name, other parameters, creation and completion date.

Plz do not attach a file with an example since I may not be able to open it in the Personal Edition.

Thanks again.

[vgutkovsky]

1. You would need to use some sort of rounding function (like ceil() for example) to put all the response times into the groups you described. Most likely you would do this with nested IFs in your script and create a separate field for the groups. 1/24 would be your segment since the smallest level of detail you care about is an hour. For example:

   ceil(timestamp#(endtimefieldname,'MM/DD/YYYY hh:mm tt') - timestamp#(creationfieldname,'MM/DD/YYYY hh:mm tt'),1/24) * 24 as [# of Hours]

   Once you have this rounded up to the nearest hour, it should be simple to use this new field to create a second field that would be 1 hour, 3 hours, 5 hours, etc. Then you would use this second new field as the dimension of your chart.
2. That chart would have Technician as a dimension and something like the following 2 expressions, assuming you want to compare the volume of calls: (1) count({<Month={"=month(today())"}>} distinct CallId) (2) count({<Month={"=month(addmonths(today()),-1)"}>} distinct CallId)

Regards,

[#5]

Thanks again. 1) seemed to work, though I'll have to play around with it a bit. Since ciel() is returning # of hours, is there a function to get # of minutes (and not with # of hr * 60). Also, how can I subtract non-working days from this calculation, if the creation timestamp is on Friday and the completion is on Tue

#2 didn't work. My graph has "Technician" as dimension. Previously, I just had count(Technician) as the Expression, which gave me grand total for each technician. But now putting this formula still gives me the grand total for each technician: count({<Month={"=month(today())"}>} distinct [Request ID])

Its not reading the month field. In my load script, I am identifying the month for each record by:
month(timestamp#(trim([Created Time]),'DD/MM/YYYY hh:mm tt'))as Month

So adding the second Expression also has no impact.

Thanks in advance

[vgutkovsky]

If you mean that you want to round it up to the nearest minute, use 1440 instead of 24.

The reason the second expression isn't working is because I wasn't sure how your fields were called. If should work if you change it to something this: count({<[Request Date]={">=monthstart(today())"},[Request Date]={"<=monthend(today())"}>} distinct [Request ID]). For the previous month, do the same logic with the addmonths() function added.

Regards,

[#7]

Not working yet, so I'm putting the statements here ... plz advice. Also, do note that I have only upto August data. Sept data will come in October.

count({<Date={">=addmonths(today(),-2)"},Date={"<=addmonths(today(),-1)"}>} distinct [Request ID])
but this gives me no output.

I've even tried the following just to get the data from last month:
count({<Month={"=month(today())-1"}>} distinct [Request ID])
but this gives me the count for all the months!

From the Script,
month(timestamp#(trim([Created Time]),'DD/MM/YYYY hh:mm tt'))as Month,

[vgutkovsky]

If your data is delayed by a month, you need to count between addmonths(today(),-2) and addmonths(today(),-1) using the set analysis formula I gave you. Using just month()+1 won't work because that ignores year. You need to use dates unless you have a month-year field.

Regards,