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.
Entered everything correctly, but the results are incorrect. Please see image and further advise...
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...
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:
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
Yes, correct... I did not see the extra button for storage options... Upon clicking and checking "Do not store..." all is well.