Next Date After Maximum Date 1

[talibm]

Hi All, I am attempting to create a report that displays the most recent assessment date (Maximum) and the next date after the most recent assessment date. I used the Running total to create the field. I summarized the activity service date. Type of summary Maximum. I used this formula to Evaluate. {ACTIVITIES.SECONDARYCODE} ="H0001". H0001 is the service code for assessment. My report is grouped by client number and start date. I would appreciate any and all help. I have tried Next, NextValue, Minimum, If statements but i can't seem to get this to work. I can get the maximum date, but I havn't had any success get the next date of service for the clients. Thanks
talib

 

[Madawc]

Use DateAdd to add a day to the Maximum Date.

DateAdd ("d", 1, {your.date})

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[talibm]

Thanks Madawc for your suggestion. I didn't phrase my question correctly. What I am trying to find is the next date that a service was provided after the assessment (maximum) date. The client has an assessment (Maximum) date and then counseling services began. I need the date of the first counseling service date after the assessment service date. The directors use this to chart the time between assessment and counseling. I hope this is a better explanation of what I am trying to do. Thanks talib

 

[lbass]

Create a formula like this:

//{@AssessmentDate}:
if {ACTIVITIES.SECONDARYCODE} = "H0001" then {table.date}

Then go to report->selection formula->GROUP and enter:

{table.date} >= maximum({@AssessmentDate},{table.groupfield}) and
{ACTIVITIES.SECONDARYCODE} <> "H0001"

Then go to report->sort records and add {table.date} as your sort field, ascending. Place {table.date} in your group header, and it will display the first date on or after the assessment date that is a non-assessment activity.

-LB

 

[talibm]

Thanks LB it worked like a charm! One last question if it's not too much. The quesetion is how to obtain the elapsed time between these dates. I will continue to work on this. Thanks again for sharing your knowledge. talib

 

[lbass]

If you have sorted the date ascending, you can use the following formula in the group header:

datediff("d",maximum({@Assessment},{table.groupfield}),{table.date})

This will only be correct in the group header, however.

-LB

 

[talibm]

LB you're the Man! Thanks ever so much. talib