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.
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.
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 )
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
$_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?
I found if I changed the Case ($_1314 ≥ 1) to ($_1314 ≠ 0) it works!