- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- calc datediff in seconds, then convert to mins the...

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

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

Former Member

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 07-19-2007 6:21 AM

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

- SAP Managed Tags:
- SAP Crystal Reports

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

07-20-2007
1:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

07-20-2007
12:38 AM

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:= 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,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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

07-19-2007
11:28 AM

Post Author: synapsevampire

CA Forum: Formula

Please post your formulas.

-k

Ask a Question

Related Content

- Leverage SAP Analytics Cloud File Server and Python to Automate Data Integration for External API in Technology Blogs by SAP
- Need Groovy Script to convert the 1486146877214 to date format in yyyy-MM-dd HH:mm:ssZ in Technology Q&A
- May i know how to convert the SAP B1 MS SQL crystal report to HANA in Technology Q&A
- Generative AI: Some thoughts on using Embeddings in Technology Blogs by SAP
- SAP ASE database migration to SAP Hana Cloud in Technology Blogs by SAP

Top Q&A Solution Author

User | Count |
---|---|

101 | |

13 | |

13 | |

11 | |

11 | |

7 | |

6 | |

5 | |

4 | |

4 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.