Jump to content
Sign in to follow this  
4Justme2

Age at the time of a specific event (that does not recalculate)

Recommended Posts

I need help with a formula to capture a person's age at the time of a specific event and have that specific age remain in history without recalculating. Is there anyone out there that can help me?

Share this post


Link to post
Share on other sites

How do you want the "age" displayed?

Share this post


Link to post
Share on other sites

Try =

Year ( DateOfEvent ) - Year ( DateOfBirth ) - ( DateOfEvent < Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( DateOfEvent ) ) )

Share this post


Link to post
Share on other sites

How do you want the "age" displayed?

i.e. someone born 1 year ago I want the result to = 1

i.e. someone born 3 months ago I want the result to be .03

Try =

Year ( DateOfEvent ) - Year ( DateOfBirth ) - ( DateOfEvent < Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( DateOfEvent ) ) )

Not sure if I did something wrong with this but the result I got was -1998. The DateOfBirth for this particular person was 7/14/1998, DateOfEvent 9/3/2012.

I entered the following calculation: Year ( DateOfEvent ) - Year ( _PEOPLE::DOB ) - ( DateOfEvent < Date ( Month ( _PEOPLE::DOB ) ; Day ( _PEOPLE::DOB ) ; Year ( DateOfEvent ) ) )

Any ideas what my problem is with this? I was hoping for a result of 14 (or more accurately 14.08 which includes mos)

Share this post


Link to post
Share on other sites

A result of -1998 indicates that the Date of Event is empty.

I was hoping for a result of 14 (or more accurately 14.08 which includes mos)

Try =

Let ( [

m = 12 * Year ( DateOfEvent ) + Month ( DateOfEvent ) - 12 * Year ( DateOfBirth ) - Month ( DateOfBirth ) - ( Day ( DateOfEvent ) < Day ( DateOfBirth ) )

] ;

Div ( m ; 12 ) & SerialIncrement ( ".00" ; Mod ( m ; 12 ) )

)

Note that this will return "14.01" in your example - which IMHO is the correct result.

Share this post


Link to post
Share on other sites

A result of -1998 indicates that the Date of Event is empty.

Hmmmm, the field is polpulated so maybe I have something else wrong with that field? I wonder if it has anything to do with the fact that the person and date both is coming form the related parent table... ???

I tried implementing the revised calc above:

Let ( [m = 12 * Year (DateOfEVent ) + Month ( DateOfEVent ) - 12 * Year ( _PEOPLE::DOB ) - Month ( _PEOPLE::DOB ) - ( Day ( DateOfEVent ) < Day ( _PEOPLE::DOB ) )] ;Div ( m ; 12 ) & SerialIncrement ( ".00" ; Mod ( m ; 12 ) ))

But am still getting bogus result. Would you mind taking a look at the attached?

AgeAtDateOfEvent.zip

Share this post


Link to post
Share on other sites

You don't have the Do Not Replace checkbox unchecked in the auto enter field.

Share this post


Link to post
Share on other sites

Why don't you use a calculation field? True, it will be unstored because it references a related field, but it will recalculate if the DOB is ever modified - which is a good thing, IMHO.

Share this post


Link to post
Share on other sites

You don't have the Do Not Replace checkbox unchecked in the auto enter field.

Good point, missed that.

Why don't you use a calculation field? True, it will be unstored because it references a related field, but it will recalculate if the DOB is ever modified -which is a good thing, IMHO.

That's an excellent idea--I'll definitely make that change. But did you notice that my calculation is sitll coming out wrong? In the very last column on the first record... Julio's age is calculating as 2012.09.. he's just a hair over 37. Can anyone tell what it is I've done wrong?

Share this post


Link to post
Share on other sites

Your "calculation" is not wrong - it was correct at the time it was performed (when the record was created). Now it's stored and will not be re-evaluated again.

Share this post


Link to post
Share on other sites

Your "calculation" is not wrong - it was correct at the time it was performed (when the record was created). Now it's stored and will not be re-evaluated again.

My issue wasn't that it won't re-evaluate, it was the initial result of 2012.09. The result should be an age. If the calcualtion is correct what is it that I have wrong that the result doesn't provide me with an age?

Share this post


Link to post
Share on other sites

It's a matter of timing. Currently, the calculation evaluates before the relationship is established, so the related DOB is not yet available. I suggest you make the following experiment: allow entry to the field, clear the field contents and re-enter the DateOfEvent. You will see that as the field's calculation re-evaluates, it will produce the correct result.

Of course, all this won't be necessary if you change the field's type to Calculation (or at least uncheck the 'Do not replace...' option).

Share this post


Link to post
Share on other sites

This also produces incorrect result 2012.10 if the birth date is empty. You might want to wrap with:

Case ( PEOPLE::DOB ; ... your calc ... )

In fact, they both should be trapped although I would assume there is always an activity date.

Case ( PEOPLE::DOB and DateOfEvent ; ... your calc ... )

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.