6 Replies Latest reply on Aug 13, 2012 11:28 AM by JefferyBrown

# Summarizing Average Days Between Dates

### Title

Summarizing Average Days Between Dates

### Post

How can I summarize the following:

Database Table

Visitor (Text Field)

Visited (TimeStamp Field)

Example Records...

Record 1

Visitor = John

Visited = 08/01/2012 12:00 PM

Record 2

Visitor = Mark

Visited = 08/03/2012 10:00 AM

Record 3

Visitor = John

Visited = 08/05/2012 2:00 PM

Record 4

Visitor = Mark

Visited = 08/07/2012 12:00 PM

Record 5

Visitor = John

Visited = 08/06/2012 12:00 PM

Record 6

Visitor = Mark

Visited = 08/10/2012 10:00 AM

I want the following summary result when table is sorted by “Visitor”

John

Average Days Between Visits 2.5

Mark

Average Days Between Visits 3.5

• ###### 1. Re: Summarizing Average Days Between Dates

This method requires that you find the records you want for your report, then sort them first by Visitor, then by Visited.

Define this calculation field (unstored), named cDaysBetweenVisits:

Let ( r = Get ( RecordNumber ) ;  If ( r > 1 ; GetAsDate ( Visited ) - GetAsDate ( GetNthRecord ( Visited ; r - 1 ) ) ) )

Define a summary field, sAverageDays as the Average of cDaysBetweenVisits.

Note: cDaysBetweenVisits will be empty for the first visit record for a given visitor in your found set. sAverageDays omits empty values from it's average computation so you will need at least 3 visits before you get the sum of two "days between" divided by 2 visits and thus a computed average.

• ###### 2. Re: Summarizing Average Days Between Dates

Entered everything correctly, but the results are incorrect. Please see image and further advise...

• ###### 3. Re: Summarizing Average Days Between Dates

What method are you using to get the horizontal line in your screen shot? (Looks odd)

Can you post a copy of the actual calculation you are using (copy and paste to the forum from specify calculation dialog).

I did neglect a detail here, the calc should also return blank if the visitor in the previous record is different, but that does not explain the negative value in the third record.

Let ( r = Get ( RecordNumber ) ;  If ( r > 1 and GetNthRecord ( visitor ; r - 1 ) = Visitor ; GetAsDate ( Visited ) - GetAsDate ( GetNthRecord ( Visited ; r - 1 ) ) ) )

Ginning up a demo file to look for other issues. Stay tuned for a down load link so you can use it to compare to yours...

• ###### 4. Re: Summarizing Average Days Between Dates

Not sure which horizontal line you are referring... It is a list template with an added sub summary and grand summary. The latest calulation produced no results...

Here's what I used for the calculation:

Let ( r = Get ( RecordNumber ) ;  If ( r > 1 and GetNthRecord ( Visitor ; r - 1 ) = Visitor ; GetAsDate ( Visited ) - GetAsDate ( GetNthRecord ( Visited ; r - 1 ) ) ) )

Here is screen capture:

• ###### 5. Re: Summarizing Average Days Between Dates

Creating the demo suggests that you may not have made your calculation an unstored calculation. Calculations using GetNthRecord must be unstored to make sure that they re-evaluate when you modify the found set by sorting, finding or omitting records.

Here's a demo file you can examine: https://dl.dropbox.com/u/78737945/AverageBetweenRecordsDemo.fp7

• ###### 6. Re: Summarizing Average Days Between Dates

Yes, correct... I did not see the extra button for storage options... Upon clicking and checking "Do not store..." all is well.

Thank you!