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

# Finding second highest date

### 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

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

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

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

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 ) )" ) )
)

• ###### 5. Re: Finding second highest date

Cool.

• ###### 6. Re: Finding second highest date

@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.

Bob

• ###### 7. Re: Finding second highest date

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

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.