Retirement dates....forecasting the future:

[agray123]

I have the following formula to calculate age and years of service (yrs service is shown)

WhileReadingRecords;
DateVar Birth:= {HR_RETIREMENT_ELIGIBLE.HIRE_DT};
DateVar Ann := Date (2004,06 ,30 );



if (Month(Ann) * 100) + Day (Ann) >=
(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

I am doing a select for:
{@Yrs Service} >= 35 or
{@Age} >= 60.00 and {@Yrs Service} >= 3

To give me numbers of employees that are eligible for retirement.


I would like to break this down into the next 5 years, with each year showing only those employees that are eligible to retire in that specific year. (subtracting the previous years eligibles?)

Currently, all I do is change the DateVar Ann := Date (2004,06 ,30 ); to 2005, 2006 etc....

I would like to be able to do this all in one report, so I can use the muliple pie chart option.

Thanks for any/all suggections!

 

[synapsevampire]

Since you want separate data elements for the report, build separate formulas for each year and increament your {Ann}.

You shouldn't have to hardcode the value of {ann}, you can derive it, for instance if you want 6/30/<nextyear>, use:

cdate(dateadd(&quot;y&quot;,1,currentdate),6,30)

In each subsequent formula, increment the 1.

-k

 

[agray123]

Thanks, now how Do I get rid of the 60 employees that show in 2004 in the 2005 totals? I want to show year by year how many are eligible to retire.

i.e in 2008 we would have 178 eligible, but some of thsoe were eligible in 2004,,5,6,7.

Do I have to create more formulas?

 

[IdoMillet]

To limit the cases projected for future years (beyond the first one), you could use a more precise condition. For example, for Year N + 1:
---------------------------------------
{@Yrs Service} = 34 or
({@Age} = 59.00 and {@Yrs Service} >= 2)
---------------------------------------

hth,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Here's a formula {@eligibility} that might work:

whileprintingrecords;
datevar year35date;
datevar year3date;
datevar age57;
datevar age60;
year35date := dateadd(&quot;yyyy&quot;,35,{Table.HireDate});
year3date := dateadd(&quot;yyyy&quot;,3,{Table.HireDate});
age57 := dateadd(&quot;yyyy&quot;,57,{Table.BirthDate});
age60 := dateadd(&quot;yyyy&quot;,60,{Table.BirthDate});

if year35date <
(if {Table.HireDate} < age57 then age60 else
if {Table.HireDate} >= age57 then year3date) then year35date else

if year35date >=
(if {Table.HireDate} < age57 then age60 else
if {Table.HireDate} >= age57 then year3date) then

(if {Table.HireDate} < age57 then age60 else
if {Table.HireDate} >= age57 then year3date);

This should give you one eligibility date per employee. Then you could create a formula for each year of the five year period, e.g.,:

if {@eligibility} in {@YearOne} then 1 else 0 //plug in your date range for @YearOne, etc.

-LB

 

[agray123]

placed this formula in the field:

whileprintingrecords;
datevar year35date;
datevar year3date;
datevar age57;
datevar age60;
year35date := dateadd (&quot;yyyy&quot;,35,{HR_RETIREMENT_ELIGIBLE.HIRE_DT};
year3date := dateadd(&quot;yyyy&quot;,3,{HR_RETIREMENT_ELIGIBLE.HIRE_DT});
age57 := dateadd(&quot;yyyy&quot;,57,{HR_RETIREMENT_ELIGIBLE.BIRTHDATE});
age60 := dateadd(&quot;yyyy&quot;,60,{HR_RETIREMENT_ELIGIBLE.BIRTHDATE});

if year35date <
(if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} < age57 then age60 else
if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} >= age57 then year3date) then year35date else

if year35date >=
(if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} < age57 then age60 else
if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} >= age57 then year3date) then

(if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} < age57 then age60 else
if {HR_RETIREMENT_ELIGIBLE.HIRE_DT} >= age57 then year3date);

and am getting a &quot;date required&quot; error at the first year35date := dateadd (&quot;yyyy&quot;,35,{HR_RETIREMENT_ELIGIBLE.HIRE_DT}; section....

 

[dgillz]

This means that the field {HR_RETIREMENT_ELIGIBLE.HIRE_DT} is not a date datatype.

Look at this field with the data explorer, making sure the top righ icon is depressed. To the right of the field you should see the data type and length. Post again with what datatype this is.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[lbass]

dgillz is right--try placing the dateadd formulas inside of date()--this would be the solution if the data type is datetime. If the data type is something else please let us know.

-LB

 

[agray123]

It si a date field.... I will use the date() and see ifthat works

thanks to all

 

[dgillz]

I do not believe this is a date....after all the error message is telling you it is looking for a date....if it was a date, you would never get that error message....

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[agray123]

the date() worked....then I used a formula like this:

if {@eligibility} = 2003 then 1 else
if {@eligibility} = 2004 then 2 else
if {@eligibility} = 2005 then 3 else
if {@eligibility} = 2006 then 4 else
if {@eligibility} = 2007 then 5 else
if {@eligibility} = 2008 then 6 else
if {@eligibility} = 2009 then 7 else
if {@eligibility} = 2010 then 8 else 0

after I created a year field for eligibilty

everything seems to work great. I cannot graph this because of the variable....is there any solution to this?

 

[agray123]

found it...changed while printingrecords to whilereadingrecords...thanks guys!

 

[agray123]

here is a question...I am getting differnet numbers from the second formula than I have from the first....significant differences....any ideas why?

 

[lbass]

What do you mean by the &quot;first&quot; and &quot;second&quot; formula? Are you referring to changing the formula from whileprintingrecords to whilereadingrecords? I think they might give you different results, and while I tend to think whileprintingrecords might be more accurate in this case, I'm not actually sure. Did you test the accuracy of the results by looking at a sample of cases and determining what you would expect to see? You might have to make some modifications to the formula.

-LB