calc datediff in seconds, then convert to mins then calc median in a group

[Former Member]

Post Author: jfitz

CA Forum: Formula

Hi

I am trying to create a report which shows the MEDIAN of the time difference between two times. I am using the datediff function - doing the calc in seconds but wanting to display in mm:ss. I then want to apply a summary of median for this field in Group 1/Group 2 Header.

I have tried two ways and have the following problems (my knowledge of Crystal reports is limited so realise there may be other ways of doing this):

1. Doing the datediff in 'seconds', then converting to minutes using a formula:

Either: datetimevalue('Time Diff'}/86400), or the formula outlined in KB C2010260. But neither of these allows a summary of median.

2. Doing the datediff and then converting to minutes and doing the median calc in the same formula: datetimevalue(median('TimeDiff'},)/86400)

This works fine for Group 1 but is giving me an error for Group 2 - 'there must be a group that matches this field' (I have triple checked the spelling in my formula.

- any help is much appreciated - thanks

[Former Member]

Post Author: jfitz

CA Forum: Formula

Sorry number 2 should read:

Doing the datediff in one formula and then converting to minutes and doing the median calc in the same formula:

Journey Time seconds = datediff('s',{Date 1},{Date 2})

Then in Group Header 1:

datetimevalue(median(@Journey Time seconds},{RMI.Jurisdiction})/86400)

and in Group Header 2:

datetimevalue(median(@Journey Time seconds},{RMI.incident_date})/86400)

the formula in Group 1 works however Group 2 gives an error message of 'there must be a group that matches this field'

from the design page: Group #2Name: RMI.incident_date (DateTime)

[Former Member]

Post Author: jfitz

CA Forum: Formula

Formulas as requested:

1.

Either:

calc datediff in seconds:

Journey Time seconds = datediff('s',{Date 1},{Date 2})

then convert to mins:

datetimevalue(@Journey Time seconds}/86400)

then insert summary - does not allow median as an option

Or

Use the formula from KB C2010260 (I've left the comments in):

DateTimeVar dt1:= {RMI.T6};

DateTimeVar dt2:= {RMI.T7};

If dt2 >= dt1 Then

(

NumberVar ds:= (Date(dt2) - Date(dt1))*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:= dshsms+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,00,"") + ":" +

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

ToText(ss,00,"")

// display will be the screen output of the formula

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

)

display

then insert summary - again does not allow median as an option

2.

Doing the datediff and then converting to minutes and doing the median calc in the same formula:

In Group Header 1:

datetimevalue(median(@Journey Time seconds},{RMI.Jurisdiction})/86400)

In Group Header 2:

datetimevalue(median(@Journey Time seconds},{RMI.incident_date})/86400)

the formula in Group 1 works however Group 2 gives an error message of 'there must be a group that matches this field'

from the design page: Group #2Name: RMI.incident_date (DateTime)

Hope I have covered everything. thanks

[Former Member]

Post Author: synapsevampire

CA Forum: Formula

Please post your formulas.

-k