Need Case Help

[steigrafx]

I have a calculated field that adds 21 years to a child's birth date in order to ascertain the date on which they turn 21, then subtracts from that the date of a parent's death, resulting in the number of years that the law would have required the parent to support that child (that is, until the child's age of majority -- 21). However, if the parent's life expectancy is less than the result, then the parent's life expectancy replaces the result (the parent can't support the child any longer if the parent is deceased!). Here's my dilemma.

If the difference is equal to or greater than 21, I want the result to be zero or the field to be left blank, since after age 21 the parent has no legal obligation to support the child. My calculation is returning negative numbers when the difference is greater than 21. Here is my calculation:

Case (

not IsEmpty ( Child_1 DOB ) and (Date (Month (Child_1 DOB) ; Day (Child_1 DOB) ; Year (Child_1 DOB) +21) - DOD) / 365.25 > Decedent Life Expectancy ; Decedent Life Expectancy ;

not IsEmpty ( Child_1 DOB ) ; (Date (Month (Child_1 DOB) ; Day (Child_1 DOB) ; Year (Child_1 DOB) +21) - DOD) / 365.25

)

I tried adding another "test" to each line:

not IsEmpty ( Child_1 DOB ) and (Date (Month (Child_1 DOB) ; Day (Child_1 DOB) ; Year (Child_1 DOB) +21) - DOD) / 365.25 < 21 and (Date (Month (Child_1 DOB) ; Day (Child_1 DOB) ; Year (Child_1 DOB) +21) - DOD) / 365.25 > Decedent Life Expectancy ; Decedent Life Expectancy ;

Yet the calculation doesn't ignore results greater than 21. Instead, I get a negative number as the result.

Any help would be appreciated.

Thanks,

Kevin

[Lee Smith]

I'm not sure I follow what you want as a result, but maybe something like this will work

Let ( [

    db = Year (DOB) ;

    dd = Year (DOD - 1)



] ;

    Case ( dd - db > 21 ; "de"; dd - db)

)

[eos]

Could you give an example with some real-life numbers? The description confuses me a bit… (DOD vs Decedent Life Expectancy)

[steigrafx]

Thanks for the reply, Lee...not quite sure how to implement that, though.

And eos, believe me, it confused me to write it! Here's an example.

Thanks,

Kevin

KaiserCalc.zip

[eos]

Thanks for the sample file, it makes things a bit clearer.

In your file, the parent died after child 3's 21th birthday. In such a case, DOB + 21 - DOD must automatically yield a negative number.

This number 1) can't be greater than the DLE, and 2) can't be equal or greater 21, so 3) it is returned as the calc result.

This cannot be a rare case (rather the opposite, I assume), so you need to find a rule for handling it.

If this is a real-world solution, I also suggest you set up a (no pun intended) child table where each record represents one

of your current rows; this way you can define and debug a calculation in one field instead of eight.

Here's your file, with the calc changed only for Child 3.

KaiserCalc_eosModified.fp7.zip

[Vaughan]

Max ( 0 ; n ) will return 0 if n is negative, but will return n if it is greater than 0.

[steigrafx]

Wow, thanks, eos, I really appreciate the time and effort you've put into this. But the result for child 3 always returns 21 years of dependency, when it should return 0 years.

That's my fault since I should have explained it better. Years of dependency is not the total years that the child depended upon the parent, it is the number of remaining years that the child would depended upon the parent, had the parent lived. So, once a child reaches the age of 21, the parent is no longer responsible for the child and the child's years of dependency should be zero. The older the child, the fewer years of dependency remain.

Is there an easy fix for that?

Thank you so much, and thank you, too, Vaughan.

Kevin

[eos]

So, once a child reaches the age of 21, the parent is no longer responsible for the child and the child's years of dependency should be zero. The older the child, the fewer years of dependency remain. Is there an easy fix for that?

So if the parent died after the child's 21 birthday, the calculated result is negative and should be converted to 0. 0 is also the desired result if the difference >= 21.

The only remaining possibility is that 0 < difference < 21. In this case you must check if LDE or difference is smaller.

Here's a revised version of the formula for Child 3 which does just that (also corrected the first check for filled-in fields).

KaiserCalc_eosModifiedv2.fp7.zip

[steigrafx]

Perfect! Thank you so much, eos. I don't think you're a novice. That was quite complicated, if you ask me (I'm the novice!).

Thanks again, and have a wonderful weekend.

Kevin