MTTA average

[dwayne94]

I created a report in Crystal Reports that gave me the MTTA (Mean Time To Accept). I was able to group together tickets that have the MTTA in the desired time format DD:HH:MM:SS. Is the a way to calculate the AVERAGE of the grouped MTTA? When I exported the data to Excel with the MTTA already calculated, I validated the time format by this formula: Status History.Accepted.TIME minus dt_Creation Date then changed the cell format to DD:HH:MM:SS (custom). I got the same time format as was created in Crystal? How do I get the Average MTTA in CR? I was able to do it in Excel but no luck in Crystal?

 

[synapsevampire]

Please post specifics:

Crystal version
Database

What is the current formula for the MTTA?

Rather than describing requirements, try posting example data, and the expected output.

Will the mean be the mean of the totals you have, or the mean of all of the values, this can be different.

You can manually maintain any values using formulas, so it's going to be possible, but not without you describing the dtaa you have and what you need and where each will be displayed.

-k

 

[dwayne94]

This is the formula I used to get the MTTA in this time format DD:HH:MM:SS

DateTimeVar dt1:= {SLM_SE_ServiceEvents.dt_Creation Date};

DateTimeVar dt2:= {SLM_SE_ServiceEvents.Status History.Accepted.TIME};


If dt2 >= dt1 Then

(

NumberVar ds:= (Date({SLM_SE_ServiceEvents.Status History.Accepted.TIME}) - Date({SLM_SE_ServiceEvents.dt_Creation Date}))*86400;

// ds converts the difference in days, between

// the two DateTimes, to seconds

NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;

// hs converts the difference in hours to seconds

NumberVar ms:= (Minute(dt2) - Minute(dt1))*60;

// ms converts the difference in minutes to seconds

Numbervar ss:= Second(dt2) - Second(dt1);

NumberVar ts:= ds+hs+ms+ss;

// ts adds up the total difference in seconds

// between the two DateTime fields

ds:= Truncate(ts/86400);

// ds now finds the number of days in the total

// seconds difference

hs:= Truncate((Remainder(ts,86400))/3600);

// hs now finds the number of hours (ensuring

// that hs is between 0 and 23)

ms:= Truncate((Remainder(ts,3600))/60);

// ms now finds the number of minutes (ensuring

// that ms is between 0 and 59)

ss:= Truncate(Remainder(ts,60));

StringVar display:= ToText(ds,0,"") + ":" +

ToText(hs,0,"") + ":" + ToText(ms,0,"") + ":" +

ToText(ss,0,"")

// display will be the screen output of the formula

// in a text format DD:HH:MM:SS

)


Else

// this portion is used only when the beginning

// DateTime is greater than the Ending DateTime

(

NumberVar ds:= (Date(dt1) - Date(dt2))*86400;

NumberVar hs:= (Hour(dt1) - Hour(dt2))*3600;

NumberVar ms:= (minute(dt1) - Minute(dt2))*60;

NumberVar ss:= Second(dt1) - Second(dt2);

NumberVar ts:= ds+hs+ms+ss;


ds:= Truncate(ts/86400);

hs:= Truncate((Remainder(ts,86400))/3600);

ms:= Truncate((Remainder(ts,3600))/60);

ss:= Truncate(Remainder(ts,60));

StringVar display:= "-" + ToText(ds,0,"") + ":" +

ToText(hs,0,"") + ":" + ToText(ms,0,"") + ":" +

ToText(ss,0,"")

);

Here are the results below after I export into Excel. I have 22 tickets with the MTTA (formula MTTA@) from Crystal Reports and the Excel Formula (History Accepted minus Creation date) then change the cell format to DD:HH:MM:SS. In the Excel formula column, I was able to calculate the average of the DD:MM:HH:SS with the formula(=Average(B2:A2) to get 0:13:43:00 but I could not do the same thing with the MTTA2 column. I tried the same formula and got #DIV/0!

A B C D
Creation Date History Accepted MTTA2 Excel Formula
8/4/07 12:12 AM 8/5/07 12:19 PM 1:12:7:45 1:12:07:45
8/4/07 8:47 AM 8/5/07 12:19 PM 1:3:32:41 1:03:32:41
8/4/07 9:22 AM 8/4/07 3:24 PM 0:6:2:41 0:06:02:41
8/4/07 12:00 PM 8/4/07 3:27 PM 0:3:26:51 0:03:26:51
8/4/07 12:51 PM 8/4/07 3:33 PM 0:2:42:4 0:02:42:04
8/4/07 12:51 PM 8/4/07 3:43 PM 0:2:52:4 0:02:52:04
8/4/07 1:10 PM 8/4/07 4:03 PM 0:2:52:51 0:02:52:51
8/4/07 2:24 PM 8/4/07 4:10 PM 0:1:45:47 0:01:45:47
8/4/07 2:26 PM 8/4/07 4:15 PM 0:1:48:29 0:01:48:29
8/4/07 2:45 PM 8/4/07 4:15 PM 0:1:29:51 0:01:29:51
8/4/07 3:18 PM 8/4/07 4:22 PM 0:1:3:39 0:01:03:39
8/4/07 3:32 PM 8/4/07 3:46 PM 0:0:14:5 0:00:14:05
8/4/07 3:56 PM 8/4/07 4:23 PM 0:0:26:19 0:00:26:19 8/4/07 4:07 PM 8/4/07 4:34 PM 0:0:26:23 0:00:26:23
8/4/07 4:31 PM 8/4/07 4:44 PM 0:0:13:6 0:00:13:06
8/4/07 4:42 PM 8/4/07 4:42 PM 0:0:0:9 0:00:00:09 8/4/07 5:14 PM 8/4/07 5:16 PM 0:0:1:52 0:00:01:52
8/4/07 6:02 PM 8/6/07 8:56 AM 1:14:53:35 1:14:53:35
8/4/07 6:56 PM 8/4/07 6:58 PM 0:0:1:42 0:00:01:42
8/4/07 8:17 PM 8/4/07 8:23 PM 0:0:6:10 0:00:06:10
8/4/07 8:28 PM 8/10/07 12:28 PM 5:16:0:16 5:16:00:16
8/4/07 8:55 PM 8/6/07 10:32 AM 1:13:37:45 1:13:37:45
#DIV/0! 0:13:43:00


Basically I would like to know if there is a way to calulate the MTTA average for a group in Crystal Reports becuase I will use a cross tab in CR to show the 22 tickets and the average MTTA of the 22 tickets.

 

[synapsevampire]

First let me address your formula by showing you my FAQ:

faq767-3543

To get an average, I would add two variables to your formula (I'd probably rewrite your formula, but if it ain't broke...):

NumberVar Allts:= ts;
NumberVar cnt:= cnt+1

Then after all of the MTAAs are computed, you can use a formula in the report footer or outer group footer of:

whileprintingrecords;
NumberVar Allts:= ts;
NumberVar cnt:= cnt+1
numberVar dur := ts/cnt;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(sec,"00");
hhmmss

-k

 

[dwayne94]

I appreciate your quick response but I am lost...You said for me to do the following: To get an average, I would add two variables to your formula (I'd probably rewrite your formula, but if it ain't broke...):

NumberVar Allts:= ts;
NumberVar cnt:= cnt+1

Why would I rewrite the formula if it is giving me the MTTA? How could I get the average MTTA without having to rewrite the formula?

Dwayne -

 

[synapsevampire]

There are two variables supplied.

Did you add them to your formula?

They will then supply a total seconds, and the total count, so dividing gets the average.

The reason why I would rewrite is that you overcomplicated the matter, but your output is accurate, so it isn't required.

Let me know if you try the solution offered and have any difficulties.

-k