
1. Re: Cannot get Case Function to work!!!
DavidJondreau Jun 2, 2011 7:16 PM (in response to jebber6685)Case ( test; result ; test ; result ; test ; default result ).
I'm not sure what your curly brackets are suppsed to be doing but they don't make sense in this calculation. Neither does using an If() nested inside a Case() here.
Try:Case (
IsEmpty ( Sell Date ) and IsEmpty ( Purchase Date ) ; "" ;
IsEmpty ( Sell Date ) ; Get ( CurrentDate )  Purchase Date) / 365 ;
( Sell Date  Purchase Date) / 365
)
What do you want to happen if Purchase Date is Empty but Sell Date is not?
DJ

2. Re: Cannot get Case Function to work!!!
Abhaya Jun 3, 2011 12:40 AM (in response to jebber6685)Hi Jebber
I got your problem.
So you know case statement that it excutes if condition true else it goes to default section.
So why you are taking it so complicated.
Just do it a simple way
e.g
Case(
condition;result;
condition ;result;
default result
)
Actually it executes till it does not match the condition and return after the default value.
so do above and follow David Jondreau comments.
I hope it will bring your solution.
Thanks

3. Re: Cannot get Case Function to work!!!
LaRetta_1 Jun 3, 2011 5:16 AM (in response to jebber6685)Did you not see this response? If formula problems
In that thread you requested, “I want this field to return the hold period even if the stock has not been sold and is still owned. “ The calculation I presented there will work if you remember to check ‘do not store calculation results’ in the calculation Storage Options. Also, in that calc, you called it Sale Date and not Sell Date so you will need to change the field name in either that calc or the one below to match the true field name.
“I want FM to return formula 1 if the Sell Date is empty. If not, then I want it to return formula 2. However, I also want FM to return nothing into the field if 2 fields are both empty.”
If you are replacing the Sell Date with the current date, it will ALWAYS produce a result so this request is illogical. Anyway, if you want to include the current date (which then would always produce a result), use the calculation I presented in the first link. If you want to skip the current date entirely and only produce a result if both Sale Date and Purchase Date have a value, then you can use:
Case ( Purchase Date and Sale Date ; Year ( Sale Date )  Year ( Purchase Date )  ( ( Month ( Sale Date ) + Day ( Sale Date ) / 100 ) < ( Month ( Purchase Date ) + Day ( Purchase Date ) / 100 ) ) )
What everyone seems to be missing is that division and multiplication comes before addition and subtraction unless one uses parenthesis to change the precedence (order of operations). It is known as PEMDAS easily remembered as “Please Excuse My Dear Aunt Sally”. Order is:
Parenthesis or brackets
Exponent (power)
Multiplication
Division
Addition
SubtractionSo in the calculation attempts presented, it will only divide the Purchase date by 365 (because that would come first) which will produce the ‘strange’ number you are getting (such as 732279). That is a date displayed as number (which is what dates are; number of days from 1/1/0001).
Also, dividing by 365 is not accurate and will be more greatly inaccurate as the span between the two dates increases. Even 365.25 (which is at least better) won’t produce dependable results in all cases. Both calculations I presented will hold true no matter the length of span between the two dates. The second calculation does not need to be set to 'do not store' in the calculation storage options (like the first one does) because it does not include Get ( CurrentDate ).

4. Re: Cannot get Case Function to work!!!
jebber6685 Jun 4, 2011 11:35 AM (in response to jebber6685)Thank you all for your assistance. This was tough for me since I am not that familiar with the exact syntax between Scripting and Calculations.
Anyways, this simple Case formula solved my problem and returns what I want. From there I set up Conditional color coding of the fields;
Case(IsEmpty( Sell Date ) and IsEmpty( Purchase Date );Get ( CurrentDate )  Get ( CurrentDate );IsEmpty( Sell Date ) and Purchase Date > .001;(Get ( CurrentDate )  Purchase Date) / 365;Sell Date > .001;(Sell Date  Purchase Date) / 365)
Also, I understand the inaccuracy of the / 365, but for my purposes I do not care about absolute accuracy. Just a good ball park of what fraction of a year my investment holding period is with the key being around 1 year. I made the field show up in yellow ( caution ) if the hold period is within 1 month of holding for 1 year ( between 11 and 12 months ). That way, if I am considering selling that stock, I may want to reconsider since I am so close to holding for more than one year.
Now if I hold a stock for, say, 20 years the actual number value returned ( 20.4 ) may not be really accurate. But it does not matter at that point. Just as long as I am within +/ .5 years.
Thanks again for all your help.
Jeff

5. Re: Cannot get Case Function to work!!!
disabled_JackRodgers Jun 4, 2011 2:20 PM (in response to jebber6685)Assuming the calcualtion is inside the Investment Time Period field:
Case (
IsEmpty ( Sell Date ) and IsEmpty ( Purchase Date ); "" ;
IsEmpty ( Sell Date ); (Get ( CurrentDate )  Purchase Date) / 365;
(Sell Date  Purchase Date) / 365
)
If that is not the case I have further ideas.
You can also use round, int and truncate to get rid of the decimals.

6. Re: Cannot get Case Function to work!!!
LaRetta_1 Jun 4, 2011 6:56 PM (in response to jebber6685)Well, I'm not sure why a calculation which might be 'close' would ever be used over a calculation which will always produce exactly accurate results in all circumstances but that is your decision ...
As for Jack's calculation (which was also David's calculation except a few parenthesis were added to make it valid fitting mathematical rules) of:
Case (
IsEmpty ( Sell Date ) and IsEmpty ( Purchase Date ); "" ;
IsEmpty ( Sell Date ); (Get ( CurrentDate )  Purchase Date) / 365;
(Sell Date  Purchase Date) / 365
)... but there is no need for the test in blue (first line in this calc) if you have the default checked of 'do not evaluate if all referenced fields are empty' since Get ( CurrentDate ) is not considered a field. It would produce empty results without that test anyway. :^) Remember to set the calculation to unstored in Storage Options.

7. Re: Cannot get Case Function to work!!!
LaRetta_1 Jun 4, 2011 7:01 PM (in response to jebber6685)Oh, and since you insist on going with 365 then this calculation will decrease the evaluations even further:
( Min ( Sell Date ; Get ( CurrentDate ) )  Purchase Date ) / 365