7 Replies Latest reply on Jun 4, 2011 7:01 PM by LaRetta_1

    Cannot get Case Function to work!!!

    jebber6685

      Title

      Cannot get Case Function to work!!!

      Post

      Hello All,

      I am trying to get a Case Function to work but it does not and I do not know what is wrong. 

      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.

      I would think the case would work for this, but I cannot get it to return what I want.

      If I try a simple IF Function, I get the right answers but when the Sell Date and the Purchase Date fields are both empty I get a very large number. I am trying to get FM to return nothing if both the Sell Date and Purchase Date fields are empty.

      Sounds like a great use for the Case Function, but I cannot get it to work either. If I use the below;

      Case ( If ( IsEmpty ( Sell Date ); (Get ( CurrentDate ) - Purchase Date) / 365; (Sell Date - Purchase Date) / 365{ ; If ( IsEmpty ( Sell Date ) and If ( IsEmpty ( Purchase Date ); Investment Time Period: = ""} )

      The above gives me "The specific field cannot be found" and it highlights the { just after the 365 that I underlined above.

      I would also think that the Default portion of the Case would work, but when I put in this Case Function;

      Case ( If ( IsEmpty ( Sell Date ); (Get ( CurrentDate ) - Purchase Date) / 365; {;If ( IsValid ( Sell Date );(Sell Date - Purchase Date) / 365;"Test"})

      FM gives the response that same response,  "The specific field cannot be found" and it highlights the { just after the 365 that I underlined above.

      Can someone help me? Why will this Case function not work for me????

      Any help here would be greatly  appreciated.
      I have FMPro Advanced 11.0 v3OS - Windows Vista on desktop, Windows 7 on laptop

        • 1. Re: Cannot get Case Function to work!!!
          DavidJondreau

          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

            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

              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
              Subtraction

              So 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

                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 in-accuracy 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!!!

                  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

                    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

                      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