Find the first/last date that meets a condition

Sunday 20 March 2016 @ 11:17 pm

A customer had a number of different appointment types in their data. They wanted Crystal Reports to calculate, for each client, the most recent date for several different appointment types. They asked why this formula didn’t work:

If {APP.TYPE} like "Assessment"
 then Maximum( {APP.DATE} , {CLIENTS.CLIENT_ID} )

The expected this syntax would give them the date of the last Assessment, and they were close.  But in Crystal you have to create the conditional column (formula) first  and then you apply the maximum function to that column.  The conditional formula would look like this:

//Formula called {@AssessmentDateMax}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}

This creates a column of dates where only rows that have the type of “Assessment” will show a date. You can then create a summary (Insert > Summary) that calculates the maximum value of this column for each client group. Or you can write the following formula to calculate the same summary inside a formula field:

Maximum( {@AssessmentDateMax} , {CLIENTS.CLIENT_ID} )

If you want to find the most recent date for a second appointment type, like “Intake”, you would need a separate formula and a second summary.

So that is how you calculate the last date.  To find the first date you need to change the formula. If you calculated the minimum of the {@AssessmentDateMax} formula shown above you would get a blank. That is because all of the records that are not assessments would have a blank value (date zero), and that would be the lowest date value in the column. The formula below will work correctly with the minimum function, but will not work if you want to do a maximum:

//Formula called {@AssessmentDateMin}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}
 else Date (2050,1,1)

If you are willing to do a bit more work there is a way to create a single formula that you can use with both the minimum and maximum functions.  It requires that you create a separate {@NullDate} formula, which has the data type of “Date” but that contains a null value.  Null values are skipped over by the Crystal summary engine so they don’t count toward the minimum or the maximum. Here are the three steps to create a {@NullDate} formula:

1) Create a new formula, name it {@NullDate} and save it with nothing but the following function:

  CurrentDate

2) Use it in the ELSE line of a formula like this and save that formula:

//Formula called {@AssessmentDate}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}
 else {@NullDate}

3) Now go back into the {@NullDate} formula, delete the function and save it empty.

This generates a Null date.  You can now use {@AssessmentDate} with both Minimum and Maximum functions and in both cases the summary function will ignore records that are not assessments. You can use the same {@NullDate} in the ELSE of several different date formulas if needed.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server