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

    Summarizing Average Days Between Dates

    JefferyBrown

      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
          philmodjunk

          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
            JefferyBrown

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

            • 3. Re: Summarizing Average Days Between Dates
              philmodjunk

              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
                JefferyBrown

                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
                  philmodjunk

                  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
                    JefferyBrown

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

                     

                    Thank you!