Calculate age on Financial year end 1

[n2nuk]

Hi there,

I need to produce a report that returns the financial year end date based on the client date of birth. For example if a client turns 25 on 15/05/2002 the financial year end would be 31/03/2003. (UK finance years)

Any ideas greatly appreciated.

Thanks

 

[lbass]

Are you saying the financial year varies for each client? And that for some reason it would start on day 1 of the month prior to the month containing the birthdate?

-LB

 

[n2nuk]

The financial year end date would be same for any clients that turned 25 in 2003, so anyone who turns 25 in 2003 would have thier financial year end date as 31/03/2004. Anyone who turns 25 in the year after would have finanical year end date of 31/03/2005.

Thanks

 

[lbass]

THe following formula would give you the enddate of the fiscal year for a particular date.

date(year({table.date}+275),3,31)

But what is it you are trying to determine? Are you trying to identify the fiscal year endate for the year in which someone turns 25?

If so, you could use:

datevar x := {table.birthdate};
date(year(x+275)+25,3,31)

-LB

 

[n2nuk]

Hi Lbass,

I have used the first piece of code you posted "date(year({table.date}+275),3,31)" and this has worked a dream.

I am producing a report to idenfiy clients that can be archived or deleted off the live database.

When a client reaches 25 they need to be marked for archiving from the first day of the next fiscal year. Records are archived for 7 years years. After 7 years the clients records are deleted.

I have produced an end date to mark the 7th year anniversary by using code based on the start of the fiscal year.

I have a number of formulas.

Ageat25:
dateadd("yyyy",25,{user.DOB})

archive_startdate: (your code)
date(year({@Ageat25}+275),4,01)

archive_enddate:
dateadd("yyyy",+7,{@archive_startdate})

status:
if {@archive_enddate} < CurrentDate then "to be deleted" else
if {@archive_enddate} >= CurrentDate then "Archived"


I would like to filter the report by status to produce 2 lists of userids. However when I use the status code in my select statement I get an error:

"Date must be between year 1 and year 9999"

the report lists 40K plus records, I suspect I may have a record with an invalid formatted DOB or missing dob, is there any way to trap this?

Many thanks


start date


dateadd("yyyy",+3,{@archive_startdate})

 

[fisheromacse]

how about inserting a group on Status.
then you would have 2 lists (or 1 for each status type if more than 2) in 1 report.

 

[lbass]

What do you want to happen to clients who have a null birthdate?

-LB

 

[n2nuk]

I have tried to group the records on status but this produces the same error message.

I'd want to flag any records with missing or incorrectly formated DOB so I can report them to our info team for data correction.

Thanks

 

[lbass]

Replace {user.dob} with:

if isnull({user.dob}) then
date(9990,9,9) else //allow for seven years up to 9997
{user.dob}

Use this in your formulas instead of user.dob. Then change your status formula to:

if {@archive_enddate} > date(9990,1,1) then
"To Be Fixed" else
if {@archive_enddate} < CurrentDate then
"To Be Deleted" else
if {@archive_enddate} >= CurrentDate then
"Archived"

-LB