Dynamic sorting of details within group 1

[LewiSys]

Hi,
This sounds really simple but I can't seem to find the solution.
My report is for inventory items. Those items have a field called 'status'. The status can be either 'active' or 'inactive'. There is also a field 'status_date' which is the date that the status field changed.
The report groups on 'status'.
My issue: How can I sort the detail records in the status group ascending or descending based on the status?
If the status = 'active', sort by status_date ascending (oldest dates listed first).
If the status = 'inactive', sort by status_date descending (newest dates first).

Thanks in advance.

** I am using Crystal 2011

 

[IanWaterman]

Create a formula
@sort
If Status = 'Active' then DateDiff("d",dateadd('d',-365,currentdate),StatusDate) // gives date diff from date in past increases as dates get older
else
DateDiff("d",StatusDate,dateadd('d',365,currentdate))// gives date diff from date in in future increases as dates get earlier

Replace relevant parts with your fields Currentdate is a valid crystal function
Sort on this formula set as ascending

Ian

 

[LewiSys]

Thanks Ian. That formula worked like a charm in the Record Sort Expert.