BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Urgent Need to calculate age in Crystal Reports XI
1 members found this topic helpful
Goto page 1, 2  Next
 
Search this topic... | Search Crystal Reports... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Author Message
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Wed Jul 16, 2008 8:47 am 
Post subject: Urgent Need to calculate age in Crystal Reports XI

I am having difficulty calculating the age of a person at the time of their death in Crystal Reports XI. I have tried various methods that have been posted on different sites, but none of them work 100% of the time.

What I have is a birthdate and deathdate that is stored in the database as datetime fields: mm/dd/yyyy hh:mm:ss. I need to be able to display the age as the number of years and months...for example: 41y/8m.

Please help if you can!

Thanks!
Back to top
Chamsin
Principal Member
Principal Member



Joined: 05 Jun 2008

Posts: 334
Location: Colorado, USA


flag
PostPosted: Wed Jul 16, 2008 10:08 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Try this:

//Age in Years and Months
NumberVar DoBVar := IIF((100 * Month({@Date of Death}) + Day({@Date of Death})) < (100 * Month({@Date of Birth}) + Day({@Date of Birth})), 1, 0);

NumberVar MthVar := (DateDiff("m",{@Date of Birth},{@Date of Death}) - DobVar) MOD 12;

NumberVar YrsVar := DateDiff("yyyy",{@Date of Birth},{@Date of Death}) - DobVar;

StringVar MthYrs := ToText(YrsVar,0) + "y/" + ToText(MthVar,0) + "m";

MthYrs;
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Wed Jul 16, 2008 11:16 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Hi,

Thanks for your response. It appears that in your formula you are showing the date of death and date of birth as formula fields rather than database fields, but I do not see what the actual formulas should be for each. Am I missing something here?
Back to top
Chamsin
Principal Member
Principal Member



Joined: 05 Jun 2008

Posts: 334
Location: Colorado, USA


flag
PostPosted: Wed Jul 16, 2008 11:21 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Sorry ... I should have explained that. I wanted to test the formula before posting it, but had no birth date or death date DateTime fields. I wrote formulas to create them.

Just replace the formulas with your raw (unmodified with a formula) DateTime fields that match.
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Wed Jul 16, 2008 11:31 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

That's what I thought I should do, but I didn't want to assume. Thanks for the clarification. I am going to give it a try now and let you know. Thanks!
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Wed Jul 16, 2008 11:59 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Hi,

I have tried this, and it is calulating correctly about half of the time. This is the same problem I was running into before I sent this. I'm not sure why, but about half the time it is one month less than it should be and about a handful of the records is one year short. The rest are accurate. I can't figure out why these types of formulas are working some of the time, but not all of the time.
Back to top
Chamsin
Principal Member
Principal Member



Joined: 05 Jun 2008

Posts: 334
Location: Colorado, USA


flag
PostPosted: Wed Jul 16, 2008 12:24 pm 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Now I am REALLY curious and am trying to test a couple things.

In the meantime, could you create a simple list of two date pairings that work, about 3 pairings that are one month short, and about 3 pairings that are one year short ... and post them back here. That may help us to pinpoint the issue.

Part of the logic in the formula is to round down to the nearest month. Perhaps we need to fix the formula where it is improperly doing the round down.
Back to top
Neinta
Forum Member
Forum Member



Joined: 22 May 2008

Posts: 10



PostPosted: Wed Jul 16, 2008 12:38 pm 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

I was running into a similar problem trying to calculate if someone was over a certain age. I was able to get it to work using the below formula. I tested it with my company's data and it worked for me.

Quote:

WhilePrintingRecords;

// DOD = Date of Death
// DOB = Date of Birth

//Years
//0.25 accounts for leap year
shared numbervar Y := (DOD- DOB)/365.25;

//Months
//30.4375 = 365.25/12 average number of days in a month

shared numbervar M := Remainder((DOD - DOB),365.25)/30.4375;

// check for Months = 12
if truncate(M) = 12
then truncate(Y)+1 & "y" & "/" & 0 & "m"
else truncate(Y) & "y" & "/" & truncate(M) & "m"




** Edited formula


Last edited by Neinta on Wed Jul 16, 2008 1:04 pm, edited 1 time in total
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Wed Jul 16, 2008 12:51 pm 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Sure. I'm am attaching a small list below, which shows examples where the month is correct and examples where it is incorrect.

When I got to pulling the examples where the year was off, I found it was because it had not actually been because of a full 12 months in those cases. Based on that, I do not believe the year to be calculating incorrectly at all. I will take a closer look and let you know for sure, but for now, it just looks like the month is off sometimes.

Thanks!
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Thu Jul 17, 2008 9:35 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Good Morning,

Did you successfully receive the information I sent yesterday? If so, did what I send explain the situation adequately?

Thanks!
Back to top
Chamsin
Principal Member
Principal Member



Joined: 05 Jun 2008

Posts: 334
Location: Colorado, USA


flag
PostPosted: Thu Jul 17, 2008 10:16 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Yes, thank you; your Excel file was perfect. I was pulled into some meetings and had to step away from working on this. I would like to find an answer for myself, because in searching for an answer I learned that the method of calculating age by averaging the number of days in a year is only mostly accurate - it will miscalculate some records.

The best tool for calculating is the DateDiff function. However its issue is that it will round up, instead of down. At issue are comparative dates like:

Birth 6/15/2000
Death 6/10/2008

Since the person did not live to or past their 8th birthday, their age was 7 years 11 months.

The formula I first sent was supposed to handle this issue, but it evidently has a flaw in in. I will continue working it. Perhaps some other forum members can add their insights.
==============================================
Since no one has added another post, I will just add to my own from earlier.

Try the following formula - it evaluates month issues separately from year issues (or rather, the age in months is dependent on the anniversary day, and the ages in years is dependent upon the anniversary month):

Local BooleanVar CfDayValues := (DateTime (1920, 01, Day ({@Date of Death}))) < (DateTime (1920, 01, Day ({@Date of Birth})));
Local BooleanVar CfMoValues := (DateTime (1920, Month ({@Date of Death}), 01)) < (DateTime (1920, Month ({@Date of Birth}), 01));
//The month comparison is dependent upon the anniversary of the day.
//The year comparison is dependent upon the anniversary of the month.
Local NumberVar CfDay := IIF (CfDayValues, 1, 0);
Local NumberVar CfMonth := IIF (CfMoValues, 1, 0);

Local NumberVar AgeMonths := (DateDiff('m', {@Date of Birth}, {@Date of Death}) - CfDay) Mod 12;

Local NumberVar AgeYrs := DateDiff('yyyy', {@Date of Birth}, {@Date of Death}) - CfMonth;

Local StringVar YrsMos := ToText(AgeYrs, 0) + 'y/' + ToText(AgeMonths, 0) + 'm';

YrsMos;

I have not tested this on every possible combination of dates, but it seems to correctly calculate the ones that were failing last time, especially those from your scenario in the Excel file.
Back to top
foxy
Principal Member
Principal Member



Joined: 22 Oct 2007

Posts: 178
Location: North West, UK


flag
PostPosted: Fri Jul 18, 2008 10:27 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Could the errors you are facing possibly be due to differing date formats, i.e. US and UK format? I knwo that I have run into problems with this before in Crystal.
_________________
Foxy
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Fri Jul 18, 2008 11:38 am 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Hi,

This is a good question, but no the date formats are completely the same.

Thanks for responding.

KEB
Back to top
Chamsin
Principal Member
Principal Member



Joined: 05 Jun 2008

Posts: 334
Location: Colorado, USA


flag
PostPosted: Fri Jul 18, 2008 2:22 pm 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

OK ... I believe this is the solution that will work, along with some explanation of the issues:

Calculating a person’s age in only months and years can be a problem if precision is necessary since some methods will round a number up and other methods utilize the average number of days in a year to account for leap year. Both of these will cause some results to be incorrect.

A key issue is whether or not the anniversary of a person’s birth has been passed in the second date, whether death or current date. Capitation and HEDIS reports require accurate ages in years and months. The following table shows how the calculated age can change in relationship to the birth anniversary date.

Month Scenario
(dependent on day)
Death: 8/25/---- cf. Birth: 12/1/---- is 8 months
Death: 8/1/---- cf. Birth: 12/25/---- is 7 months

Year Scenario
(dependent on month)
Death: 8/1/2008 cf. Birth: 12/1/2000 is 7 years
Death: 12/1/2008 cf. Birth: 8/1/2000 is 8 years

To correct this, if the death day is less than the birth day, the month value will be lowered by one; if the death month is less than the birth month, the year value will be lowered by one.

A second issue can occur if the birth day was February 29 (on a leap year) and the death day was February 28 (on a non leap year). The first argument in the “CfDayValues” variable tests for this.

A third issue can occur if the birth day was the 31st in a 31-day month and the death day was the 30th in a 30-day month. The core methodology for handling the key issue above will miscalculate this scenario. The first argument in the “CfDayValues” variable also tests for this.

Local BooleanVar CfDayValues := IF ((Month ({@Date of Birth}) * 100) + Day ({@Date of Birth})) IN [131, 229, 331, 531, 731, 831, 1031, 1231] AND ((Month ({@Date of Death}) * 100) + Day ({@Date of Death})) IN [228, 430, 630, 930, 1130] THEN False
ELSE
(DateTime (1920, 01, Day ({@Date of Death}))) < (DateTime (1920, 01, Day ({@Date of Birth})));
Local BooleanVar CfMoValues :=
(DateTime (1920, Month ({@Date of Death}), 01)) < (DateTime (1920, Month ({@Date of Birth}), 01));
Local NumberVar CfDay := IIF (CfDayValues, 1, 0);
Local NumberVar CfMonth := IIF (CfMoValues, 1, 0);

Local NumberVar AgeMonths := (DateDiff('m', {@Date of Birth}, {@Date of Death}) - CfDay) Mod 12;
Local NumberVar AgeYrs := DateDiff('yyyy', {@Date of Birth}, {@Date of Death}) - CfMonth;
Local StringVar YrsMos := ToText(AgeYrs, 0) + 'y/' + ToText(AgeMonths, 0) + 'm';

YrsMos;
Back to top
kebbusobj
Forum Member
Forum Member



Joined: 16 Jul 2008

Posts: 22



PostPosted: Mon Jul 21, 2008 2:37 pm 
Post subject: Re: Urgent Need to calculate age in Crystal Reports XI

Sorry that I am just now replying...I have been having issues with my ISP for the last few days. I was just now able to try this formula out, and it appears to have resolved the rounding issue. You are BRILLIANT! Thank you so much for your help!!! I really appreciate it.

I have another issue that I have posted and that I have not received any feedback on. I wonder, with your obvious experience, if you might be able to offer some opinion on that issue. Would you be willing, and do you have the time, to take a look at problem I am facing? If so, the topic subject is "SubReport in Crystal Reports XI keeps adding sections".

Thanks again for all your help with this!!

KEB
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0501 seconds using 17 queries. (SQL 0.0029 Parse 0.0356 Other 0.0116)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo