5 Replies Latest reply on Mar 4, 2014 8:46 PM by happyez

    Using Case when a calculation gives a question mark

    happyez

      Title

      Using Case when a calculation gives a question mark

      Post

           Hello everyone

            

           I've been reading up about Case, and thinking it was what I needed, found a problem.

            

           I have a calculation (CalcField = Field 1 / Field 2). When Field 1 has no data in it, CalcField comes up with a "?". When there is data in Field 1, there is no problem in presenting a result (3, 44 etc).

            

           'Case' seems to work if you know what the answer is, but I don't know.

            

           I've looked at previous questions and haven't been able to work out how they would apply to me.

            

           This is probably simple, and I will probably understand it when someone points it out!

            

           Thanks everyone

           Eric

        • 1. Re: Using Case when a calculation gives a question mark
          symbister

               Why are you using a Case statement? Case is designed to be used to 'choose' a result based on a number of tests.

               Are both Fields 1 and 2 defined as type number? and the Calc Field is set to give a number result?

               and is the field display wide enough for possible result - in Layout Mode, stretch it and make sure .... I'd check all this and read up a bit more on Case - it may not be what you want in this instance.

          • 2. Re: Using Case when a calculation gives a question mark
            happyez

                 Hi Symbister

                  

                 Yes, didn't think Case worked, but in other examples I read, it seemed people we're using Case. I think I misread it.

                  

                 Well, I look at the fields, and CalcField is calculating from Field 1 and Field 2 which are two calculation fields, not number fields (they themselves draw their info from two other Number fields)

                 But the calc fields for all of them are set to Number.

                 And it still works when Field 1 and Field 2 both have data it is drawing from.

                  

                 I'm happy to use If statements or anything else.

            • 3. Re: Using Case when a calculation gives a question mark
              philmodjunk

                   I don't see how an empty field 1 can produce that question mark An empty field 2, on the other hand will produce a divide by zero error and thus a question mark.

                   Perhaps this is what you had in mind?

                   If ( Not Isempty ( field2 ) and field 2≠ 0 ; Field 1 / field 2 )

                   which could also be set up as:

                   case ( field2 ) and field 2≠ 0 ; Field 1 / field 2 )

              • 4. Re: Using Case when a calculation gives a question mark
                happyez

                     Hi Phil

                     During the day I got this as possibly another idea. I just read a better explanation of the Let function, and it got me thinking. I'll try yours out as well, which is probably the way to go rather than all of this below! :)

                     It combines two different calculations. I simplified it before (just so I could explain it well as I am not a natural programmer!), but this is pretty much how it looks as to how I am trying to work it out

                     Let (
                     [
                     $_13 = Field1_2013 / AllFields_2013_TOTAL ; /* this is to get the % of this field within the total
                     $_14 = Field1_2014 / AllFields_2014_TOTAL ;
                     $_1314 = (( $_14 - $_13 ) / $_13 ) * 100 ] ; /* this is to get the percentage of change between both fields */
                     Case ( $_1314 ≥ 1 ; $_1314  ;  $_1314 = 0 ; ". ."   ;  $_1314 = "?" ; ". ." )

                     )

                      

                     Where I thought may work is that I say in the first Case instance "hey, if the final calculation that made $_1314 ends up with a number greater than 1, then do the calculation". If not, then in the field that this whole calc sits in, comes up with a 0, then instead put in ". .", and the same for the "?"".

                      

                     When I set it out in the field, I turned up with a blank. Am I on the right track?

                     Eric

                      

                      

                • 5. Re: Using Case when a calculation gives a question mark
                  happyez

                       I found if I changed the Case ($_1314 ≥ 1) to ($_1314 ≠ 0) it works!