Calculating Average Time between two Dates

[GMAN33]

Hi Gang,

I have a record history of when this record is sent out and also when it is received. This is located in one table. I can see the history table as follows

"Sent" 12-03-2002
"Received" 03-02-2003

I have thousands of records like this grouped by particular site locations. How would I go about creating a formula that will calculate the AVERAGE time it takes to get a record received from when it was originally sent out.

Any help would be great.

Thank you

 

[Madawc]

I assume you have sent out and received data on the same detail line. You first need to find the difference between them in days, i.e.
DateDiff ("d", {sent out}, {received})

Using Crystal 8.5, I would then create a running total to find the average of the days different. There may be a way to wrap it up in a single formula, but running totals allow median, mode, weighted average etc.

You can hide the detail lines and just show the totals.

Madawc Williams
East Anglia, Great Britain

 

[Naith]

Place a formula in your details section which does this:

DateDiff('d',{SentDate},{ReceivedDate})

Place another formula in your report footer:

Sum({TheDateDiffFormula}) / Count({ID})

or

Sum({TheDateDiffFormula},{Your.Group}) / Count({ID},{Your.Group})

placed in the group footer if you want an average by group.

Naith

 

[kenhamady]

I am going to guess (based on the way he shows the data) that these are 2 different records, which makes it a bit more complicated. If so:

Are there always a complete pair or could there only be a SENT with no RECEIVED (or vice versa)?
How do you know which SENT goes with which RECEIVED?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[GMAN33]

Thank you all for the reply.

They are the same record. Their is a specified order of events within the database in which this record needs to follow. The record first has to be "Sent" before it can be "Received". The statuses have the same unique number (record #) assigned to them with only the status, date and a hidden unique status code 02=sent, 04=received, 06=final, 08=closed. These status codes never change. But not all records will have a received status assigned to them yet.

So I guess I would like to subtract the sent date from the received date for each record and then at the group level, do the overall average of time it takes to get from sent to received for each group.

So it would look like

Record 1
(02)SENT 01-01-2003
(04)RECEIVED 01-03-2003
2 days
Record 2
(02)SENT 12-04-2002
(04)RECEIVED
? days
Record 3
(02)SENT 02-22-2003
(04)RECEIVED 02-24-2003
(06)FINAL 02-24-2003
2 days
GROUP LEVEL------Average days from Sent to Receive: 2 Days

I hope this helps and thanks again

 

[kenhamady]

It still sounds like Status 02 and status 04 are 2 separate records with a field called status, not two different status fields in the same record (row). What are the actual field names in the table for all of the statuses?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Madawc]

You need to turn Record1, Record2 etc. into groups, since they contain more than one detail line.

Use a summary or running total to get the minimum and maximum, i.e. send and receive. Then use my formula at the Record Group Footer level.

One complication, how do you distinguish between sent/not-yet-received and sent/received the same day? Does this mean 1 day or 0 days? You might need to set a flag.

Madawc Williams
East Anglia, Great Britain

 

[kenhamady]

What I would do is add the table the report twice, and call the second copy received. Linke the two instances of this table by Record Number. Put in criteria that the first table status has to be 02 and the second table status has to be 04. This puts both in the same record. This assumes that you only want records with both statuses, but now you can use the DateDiff techniques outlined above.

If you need to show records that aren't received, things get more complicated.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[GMAN33]

Thanks again for the responses. The status field contains a history of the individual Records. This status is stored in one table differently from the record. So it is a one to many.
Each site is grouped, within these sites there are these records. These records can contain one of many different statuses. I have these statuses listed in the details section. So what I would like to do is at the group footer of the sites is give the overal average time it takes these combined records to go from sent to receive. These records can also have additional statuses as mentioned above.

Hope this is becoming more clearer...I know how difficult it is to explain.

Thanks

 

[kenhamady]

Part of the problem is that you are using the term record to mean a history (parent) record. To a Crystal or databse person, a record is a single row in the report or table, so each of your statuses is also record. You have several status records for each History record. When the two tables are combined in a report, every status line is a record.

You need to link a second copy of the status table to the history table. In the new copy select only the sent values. This way the SENT value is available in every record. Then write a formula that says if the original status is received calculate the difference between sent and received, otherwise return a zero. If you sort on this formula in Descending order, all of the differences for each group will be at the top.

Last you can use a running total that evaluates once per group to average one difference per history record. This is prety complex but it is the simplest way to do what you want that I can think of.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com