Evaluating Date Problem 1

[gscheepers]

Hi everyone,

I have a problem with our personnel systems reports. Here's a scenario:

When a person goes off sick the dates get entered into a START and a END date when the person will be returning.

When they choose the dates for running a report ie start date 01/11/2002 end date 20/11/2002, the report does not return everyone that's been off sick during that period as someone's sick leave started on ie 24/11/2002 but ENDED on 05/11/2002.

Currently the report evaluates the date range like this:

if {?Directorate}='All' then
{ABSENCE.First_day} in {?Start Date} to {?End Date}
else
({DIRECTORATE.Dir} = {?Directorate} and
{ABSENCE.First_day} in {?Start Date} to {?End Date});

Your help would be grately appreciated!

Thanks, Gerhard Scheepers

"Great souls have wills; feeble ones only wishes..."

 

[SmpTraining]

This is a common reporting problem and in many other cases I've worked with the solution is not simple. Consider these questions:

Do you consider the absence to be the total number of days between the absence start and end?

- This may sound an odd question but sometimes a person is absent for a month but may have had holidays booked for a week in the middle thus taking 7 days off the number of days 'absent'.

Do you consider non-working days absences? What if someone only works 2 days a week and is absent for a month - how many days are they absent?

Let me know if any of these issues affect you. If not, we should find a solution quite quickly
Steve Phillips, Crystal Consultant

 

[gscheepers]

Hi Steve,

Thanks for your response. This should not have any effect on the report as the number of work days are worked out by the application and populated against a field in the table ({ABSENCE.Duration})

Thanks so far! Gerhard Scheepers

"Great souls have wills; feeble ones only wishes..."

 

[SmpTraining]

You still have the same problem. Say someone is off for a month, they only work 2 days a week and they had a holiday for the last week.

The system has calculated they were off for a total number of 6 days (the last 2 didn't count as they were on holidays)

They were off from 15 Dec 2001 - 15 Jan 2002.

You run a report for Jan 2002.

What should you see?

The fact that the system calculates everything only AFTER they've returned and all the little details have been looked at really complicates the ability of your report to be accurate.

You can only accurately report on COMPLETED absences that fall WHOLLY within the reporting period.

Am I missing anything? Steve Phillips, Crystal Consultant

 

[gscheepers]

I see what you mean...

From what you where asking, it should only display the days that the person where off for in January and not look at the time he was off for in December.

Any ideas?
Gerhard Scheepers

"Great souls have wills; feeble ones only wishes..."

 

[SmpTraining]

Nothing good I'm afraid. When some clients realise the problem they change the way they record absences.

If you know you will only ever report on absences for whole months, never let absences span this period. At the end of the month, end all absences and begin a new one for all those still off.

Another solution is to give a 'best guess'. You can do this by looking at the number of days absent field (only available for completed absences) and proportioning this against the number of days the absence fell within the period being reported on.

So for my last example, we would know they were off for 6 days for a period spanning a month, half of that time fell within the reporting period, therefore they were off for 3 days in the reporting period (best guess).

To calculate this you need formuals to calculate

The number of days the absence spans (30 days or so)
The number of days absent (6 - as held in the Duration field)
The proprtion of absence days falling in the reporting period (50%)

Given all this you get 50% of 6 is 3.

If that isn't bad enough. What are you to do about absences that still haven't ended!! Sorry to bring all this up but the solution is not always easy! Steve Phillips, Crystal Consultant

 

[gscheepers]

The report will only be run on a monthly basis.

One of my main problems at this point would be for the employees that meets this criterion to be displayed on the report.

Thanks for your help so far... Gerhard Scheepers

"Great souls have wills; feeble ones only wishes..."

 

[SmpTraining]

OK, you need to change your selection to the following assuming your absence last date is as shown.

if {?Directorate}='All' then
{Absence.First_day} <= ?{?End Date} and
( IsNull(Absence.Last_day} or
{Absence.Last_day} >= {?Start Date} )
else
({DIRECTORATE.Dir} = {?Directorate} and
{Absence.First_day} <= ?{?End Date} and
( IsNull(Absence.Last_day} or
{Absence.Last_day} >= {?Start Date} );
Steve Phillips, Crystal Consultant

 

[gscheepers]

Thank you ever so much!!!

That's a HUGE step forward (at least I think!)

Gerhard Scheepers

&quot;Great souls have wills; feeble ones only wishes...&quot;

 

[SmpTraining]

Thanks for the thanks !! And good luck with the other bits. Steve Phillips, Crystal Consultant