Check overdue appraisal records as at 31 August annually 1

[kettie]

I am using Crystal Reports V.10.

In our organisation, every employee is to have a performance appraisal completed by 31 August annually.

In our database, the due date for the next appraisal is stored as a date field {Apr_Next_dt}, ie 2012,06,07

For instance I may have

Employee Name
J Blogs (2011,08,24)
J Citizen (2012,06,01)
D Ball (2012,08,01)
R Sail (2012,09,01)
T Blewin (2013,08,15)

I am trying to write a formula which will identify the number of overdue appraisals.

An overdue appraisal is any appraisal date that falls prior to the period 1/9 (previous year) to 31 August (current year) (i.e. 1/9 - 31/8).

So, if the next appraisal date {Apr_Next_dt} is before 1/9 of the previous year, it would be classified as overdue.

If the next appraisal date is in the period 1/9 of the previous year to 31/8 of the current year, the appraisal is not overdue.

See below example - these would be the results I would expect if I ran the report today and the dates were being compared to the period 1/9/11 - 31/08/2012)

J Blogs (2011,08,24) [1] (overdue because due prior to 1/9/11)
J Citizen (2012,06,01) [0] (not overdue as in period 1/9/11-31/8/12)
D Ball(2012,08,01) [0] (not overdue as in period 1/9/11-31/8/12)
R Sail(2012,09,01) [0] (not overdue as post 31/8/12)
T Blewin(2013,08,15) [0] (not overdue as post 31/8/12)

These would be the results if I ran the report on 1/9/12 (and the period becomes 1/9/12 to 31/8/13)

J Blogs (2011,08,24) [1] (overdue)
J Citizen (2012,06,01) [1] (overdue)
D Ball(2012,08,01) [1] (overdue)
R Sail(2012,09,01) [0] (not overdue)
T Blewin(2013,08,15) [0] (not overdue)

I would appreciate any assistance on offer to help me write this formula. As this report will be published on Enterprise, I want the report to automatically determine which year we're in (regardless of the date the report is run), rather than hard coding the year into the report and having to republish annually.

Thanking you all in advance

 

[pmax9999]

Hi kettie

Create a formula to determine the start of the reporting period (the most recent 1 September) as follows:

//{@Report_Start_Period}
If Month(CurrentDate) <= 8
Then Date(Year(CurrentDate)-1, 9, 1)
Else Date(Year(CurrentDate), 9, 1)

Any amployee where the employee's {Apr_Next_dt} is after {@Report_Start_Period} will be overdue, so if the report is only required to select the overdue employees, use a data selection formula of:

{Apr_Next_dt} > @Report_Start_Period}

Hope this helps.
Pete

 

[kettie]

Thank you Pete
That formula worked perfectly and I have been able to complete my report successfully.

Kettie