How to sumarize number of "Work Hours" between two DateTime Values

[Solojer]

Hi all -- Following MCuthill suggestion, I followed Ken Hamady's solution:

http://www.kenhamady.com/form13.shtml

to calculate the number of "Work Hours" between two DateTime Values.

This worked, however, now Crystal won't let me summarize the resulting value of the field. Do you know how I can do this?

Thanks!

 

[lbass]

This probably should not have been a new thread. Please show the content of the formula you are using.

-LB

 

[Solojer]

I wasn’t sure if I should start a new thread as this is a different (but related) problem. Next time, I’ll keep it all to one thread – thanks for the advice!

I’ll try to be as detailed as I can about the problem I’m currently having:

In the report I have 1 group, which is case_details.case #.

If the value of {@Next_Date} is blank, {@Time Difference} will show 0, otherwise, it will calculate the value.

Because I want to summarize {@Time Difference}, I right click on it, but the “Insert>Summary” is not showing. I tried to create a field in GF1called {@Average time difference} with the value

average ({@Time Difference},{case_details.case #})

but I got a message that {@Time Difference} cannot be summarized.

Any help would be very appreciated!

Here is a breakdown of the report

Section: Report Header

formula:{@Holidays}

//[b]{@Holidays}[/b]

BeforeReadingRecords;

DateVar Array Holidays := [

Date (2011,12,25),

Date (2011,12,31)

];

0

Section: Details

Formula: {@Bump_hour}

//[b]{@Bump_hour}[/b]

if (time ({case_details.Date}) > TimeValue("17:00"))

    then datetime (date({case_details.Date})&TimeValue("17:00"))

else if (time ({case_details.Date}) < TimeValue("08:00"))

    then datetime (date({case_details.Date})&TimeValue("08:00"))

else {case_details.Date}

Formula: {@Business Days}

//[b]{@Business Days}[/b]

//Main formula

//WhilePrintingRecords;

Local DateVar Start := date({case_details.Date});   // place your Starting Date here

Local DateVar End := date(next({case_details.Date}));  // place your Ending Date here

Local NumberVar Weeks;

Local NumberVar Days;

Local Numbervar Hol;

DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1

- (Start - dayofWeek(Start) + 1)) /7 ) * 5;

Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +

(if DayOfWeek(Start) = 1 then -1 else 0)  +

(if DayOfWeek(End) = 7 then -1 else 0);  

Local NumberVar i;

For i := 1 to Count (Holidays)

do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and

     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days – Hol

Formula: {@Next_Date}

//[b]{@Next_Date}[/b]

if next({case_details.case #})={case_details.case #} then

 if {case_details.Action ID}= "EMRCV" then next({case_details.Date})

Formula: {@Time Difference}

//[b]{@Time Difference}[/b]

if length(totext({@Next_Date}))<>0 then (

//    EvaluateAfter ({@Business Days});

    NumberVar Days := {@Business Days};  // The field that calculates your business days

    TimeVar SetStart := TimeValue( "8:00");      // The start your work day

    TimeVar SetEnd   := TimeValue("17:00");      // The end your work day

    TimeVar StartTime := TimeValue({@Bump_Hour});// The data field that holds your Start Time

    TimeVar EndTime   := TimeValue(next({@Bump_Hour}));  // The data field that holds your End Time

    //These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"

    //They will convert afternoon times to PM.  Of course, this won't work if your workday is over 12 hours.

    //If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;

    //If EndTime   < (SetEnd - 43200) then EndTime   := EndTime   + 43200;

    Days * ((SetEnd - SetStart) / 3600)

    -  ((SetEnd     - EndTime)  / 3600)

    -  ((StartTime - SetStart)  / 3600)

)

 

[lbass]

The problem is your NextDate formula, which requires evaluation across a sequence of records. What is the point of this formula? What is the logic for using {@NextDate}? Maybe illustrate the issue it addresses by showing some sample data.

-LB

 

[Solojer]

My intent with the Next_Date formula was:
1. evaluate if the next action ID is for the same case#. If it is, then...
2. ...evaluate if the action ID = "EMRCV". If it is, get the date of the following action ID so I can calculate the difference between the two times.

here's some sample data:
SEQ Case # Action ID Date
1001 1234 OPEN 06/17/2011 08:47:00 AM
1002 1234 FWD 06/17/2011 08:47:10 AM
1003 1234 EMRCV 06/17/2011 08:47:15 AM
1004 1234 EMSNT 06/17/2011 08:50:14 AM
1005 1234 EMRCV 06/17/2011 08:54:15 AM
1006 1234 EMSNT 06/17/2011 08:59:14 AM
1007 1235 OPEN 06/17/2011 08:57:00 AM
1008 1235 FWD 06/17/2011 08:57:10 AM
1009 1235 EMRCV 06/17/2011 08:57:15 AM
1010 1235 PHONE 06/17/2011 08:53:14 AM

I want to show the time difference between 1003 and 1004, 1005 and 1006, and 1009 and 1010. This is working in my current report. What isn't working is when I try to summarize the average of the difference between 1003 & 1004 (0.0497) and 1005 & 1006 (0.083), which should give me 0.06635 or 3minutes 58 seconds.

 

[Solojer]

One minor modification -- 1010 would more accurately read as follows:

SEQ Case # Action ID Date
1010 1235 PHONE 06/17/2011 08:59:14 AM

 

[lbass]

Okay, well you handle this with variables:

whileprintingrecords;
numbervar timediff := {@yourdiffformula};
numbervar sumdiff := sumdiff + timediff;
numbervar cnt;
if timediff <> 0 then
cnt := cnt + 1;

Then in the footer, use:

whileprintingrecords;
numbervar sumdiff;
numbervar cnt;
if cnt <> 0 then
sumdiff/cnt

This assumes that timediff = 0 in rows you don't want to evaluate, and that the timediff is never 0 legitimately in those you do.

If this is at some group level, you would need to add a reset formula in the group header:

whileprintingrecords;
numbervar timediff;
numbervar sumdiff;
numbervar cnt;
if not inrepeatedgroupheader then (
timediff := 0;
sumdiff := 0;
cnt := 0
);

-LB