How to group by non-existing future date

[buck149]

Crystal XI/Oracle 10g

I am doing a report counting eligible retirement for the next 10 years. My database is updated monthly and each update is represented by an "as of id" which is a numeric field like "200709". This field will also be used as user input paramter.

What I want to do is to create a formula based on this parameter as the starting date and cover the next 10 year.

I have no clue of how to do it.

I need this formula also for the calculation of the eligibility of retirement (something like this formula - dob >= 55)

 

[buck149]

I want to group by this formula on yearly basis and all retirement calculation will be also based on this formula for each every future year.

 

[MrBillSC]

I'm not 100% sure I understand your request. Assuming the start year month is a parameter called {?StYrMo} you will need a formula to calc the end year month:
// formula @EndYrMo
{?StYrMo} + 1000

Then you will need a formula to calc the Year / Month of employees retirement date. So assuming it is age 55:
// formula @RetYrMo
((DatePart("yyyy",{table.birthdate})+ 55) * 100) +
DatePart("m",{table.birthdate}))

Then in your record select formula:
{@RetYrMo} in [{?StYrMo} to {@EndYrMo}]

You will then need a formula for your group at retirement year.:
// formula @RetYrGroup
If Remainder({@RetYrMo},100) <= Remainder({?StYrMo},100) then
((Truncate({@RetYrMo}/100) * 100) +
Remainder({?StYrMo},100)) else
(((Truncate({@RetYrMo}/100) + 1) * 100) +
Remainder({?StYrMo},100))

I haven't tested this.
MrBill

 

[Madawc]

Do a difference formula. something like

DateDiff("yyyy", {birth.date}, {database.date})

Display this next to unselected records till you have it doing what you want. Then select with something like

 @RetireWhen >= 55
and @RetireWhen < 66

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

 

[Madawc]

PS. @RetireWhen could also be used for grouping.

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