8 Replies Latest reply on Nov 19, 2013 3:43 AM by Insight

    Finding second highest date

    Jon1009

      Title

      Finding second highest date

      Post

      I have setup a calculation which finds the Max() date house would I go about finding the previous date before the max?

      Cheers jon

        • 1. Re: Finding second highest date
          philmodjunk

          I could script it.

          Sort your records by date in descending order.

          Go To Record ( 2 )

          Will put you on the second most recent date.

          • 2. Re: Finding second highest date
            raybaudi

            hmmmmm...

            He asked for a calculation and your script doesn't guarantee the second most recent date ( think about 3 records with the same Max date )

            • 3. Re: Finding second highest date
              philmodjunk

              Good point. This script could, of course step down through the sorted records until the date changes.

              Some self join relationships based on date might also work in a straight calculation.

              Table::ID x Table2::ID

              Table::ID x Table 3::ID AND
              Table::MaxDate ≠ Table 3::DateField

              Sort this second relationship by date, descending and define MaxDate as Max ( Table2::DateFIeld )

              Now a refrenced to Table 3::DateField from the context of Table should return the second largest date.

               

              • 4. Re: Finding second highest date
                raybaudi

                That could be made w/o any additional relationship.

                1) Create in the related table a calculation field ( cDateAsNumber ), result number and calc:

                    Date

                2) Create in the main table a calculation like this, result date:

                Let([
                v = TrimAll ( Substitute ( List ( Related::cDateAsNumber ) ; [  Max ( Related::cDateAsNumber )  ; "" ] ; [ ¶ ; " " ]) ; 1 ; 1 ) ;
                v = Substitute ( v ; " " ; ";" )
                ];
                If ( v ; Evaluate ( "GetAsDate ( Max(" & v & "; 0 ) )" ) )
                )

                • 6. Re: Finding second highest date
                  Insight

                       @raybaudi

                       Just came across your script for finding the second newest date. I tested it and it works fine

                       Question:

                        

                       How would you adjust it to get the first (not second) highest date.

                        

                       I have a facility actions required data bases

                       Sections - Activities needed to be done - Tasks assigned to the activities

                        

                       Taskes related to a particular activity come for the "Tasks Required" table to the choice of task to do.

                        

                       New record in the "Task Audit" table (main table) adds a record of a task being carried out by individual.

                       And the date that was carried out.

                       Back in the tasks to be done table I want to reference the tasks audit table and find out the last date (newest date) the task was done in the task audit trail table.

                        

                       I used the 

                       Max ( Audit of Tasks::Date Done to Numbers ) to get highest number and created a relationship back to this table x relationship

                       I then made a relationship from the task audit table back to the table of Tasks to be done.

                       I then tried your script but it gives me the second highest date a related task was done in the tasks audit trail table.

                       Also if there has only been one entry of the task in the "Task Audit Trail" table it doesnt show any date from your script.

                       Any help or advice would be appreciated.

                        

                       Thanks in advance

                       Bob

                        

                        

                        

                        

                  • 7. Re: Finding second highest date
                    raybaudi

                         If in the relationship graph you sort the tasks audit table descending by that date field, all you need to do is to place that date field in the related table and change the behavior to not let enter it in browse mode.

                    • 8. Re: Finding second highest date
                      Insight

                           Sorry that doesn't seem to work...

                           See screen grab attached

                           I think I might need start a 'new post' looking for an way to get the highest date of the related record.

                           Any further advise ?

                           Bob