Field Calculation IF statement

[Himitsu]

I have been at it and I know my understanding of if statements isn't that good. But what I am tring to do is produce a kind of school year calculation based on stopping date. I have a field that contains when the student stopped. I also made two more calculation fields that pull the date into year and month. What I need is a field that states:

if( stopped_month >=4; stopped_year & "-" & stopped_year + 1)

but it only produces a number 1. What I need it to do is have a school year starting from april to end of march the next year. So if a student stopped 2004/5/12 then I need the calculation to produce 2004-2005. Any ideas?

Thanks

Ryan

[Himitsu]

Oh... I got it. I just had to put the third part, second result of the calculation. Now it works fine as:

If ( stopped_month ≥ 4; stopped_year & "-" & stopped_year + 1; stopped_year　- 1 & "-" & stopped_year)

Sorry for anyone's time.

[comment]

Ah, I see you've got it. Anyway, you can eliminate the extra fields and go directly to:

Case (

Month ( StoppedDate ) > 3 ;

Year ( StoppedDate ) & "-" & Year ( StoppedDate ) + 1 ;

Year ( StoppedDate ) - 1 & "-" & Year ( StoppedDate ) )

[Himitsu]

comment, I have seen a lot of case statements on many calculation. Are they a cleaner way to do an if statement?

[comment]

No. It's just that Case() does the same thing as If() - and more. You can have only one test with If(), and two results. With Case() you can have many tests, and many results. So I just use Case() for everything.

Choose() is supposed to be faster, but I try not to use it in examples, because it can be confusing. For example:

Case ( test is true ; "true" ; "false" )

is easy to grasp, but when you use Choose() it becomes:

Choose ( test is true ; "false" ; "true" )

[Himitsu]

So would you then just put multiple case statments or one with as many tests with as many results inside the statement? Because that part of the statement is what gets me? I understand that there is an if() and a case() and that you have a condition(test) and if it is met then pass it the result and the last part after the ; would be like the else statement. But how would you run a case with "a" test with result or "b" test with result and then the else result?

[PeterHW]

The Case() function makes for cleaner code as you can 'step through' the tests more easily. In your example, this would be a typical use of Case()

Case(

a=true;"A is true";

b=true;"B is true";"default"

)

Note that the final statement ("default") is optional : Filemake evaluates all statements and exits the function on the first true test, or the optional default if none of the statements is true. If you do not specify the default and none of the statements is true, it returns empty.

HTH,

Peter

[comment]

Another way to look at it is to imagine a function such as:

If (

a=true ;"A is true";

Else If (

b=true;"B is true";

Else (

"default"

)))

That is exactly what Peter's Case() example does.

[LaRetta]

Excellent narrative guys; one of my favoriate subjects Case(), Choose() and If(), but just for clarification ...

Filemake evaluates all statements and exits the function on the first true test, or the optional default if none of the statements is true.

Only in version 7. Prior versions do not short-circuit on these functions. This is important to note, I think. You can substantially improve speed by applying branch prediction to your Case(), If() and OR constructs. So, with 7, place your most-likely true first - in all evaluations. In prior versions you can afford to be sloppy in this regard because the entire calculation must be evaluated before FM applies (sometimes) the first test result.

And Choose() is significantly faster still because it doesn't even really evaluate - it just DOES. It is well worth understanding thoroughly and using whereever possible ...

LaRetta

[comment]

Choose() does need to evaluate the "test", i.e. its own index. So I would think there should be no advantage to Choose() over a single-test Case() or If().

Branch prediction is advisable, but not always possible, e.g. grading a bell-curved population.

[PeterHW]

Thanks, Laretta, that's indeed a worthwile addition. There was something in the back of my head screaming for attention when I wrote "exits on first true test ..." so I quickly tested this before posting to make sure, but of course only in 7

Peter

[LaRetta]

Correct, Comment, Choose() would have no advantage over ONE Case() or If() test. But any more than one and the difference can become noticable particularly when applied over a large volume of records. With 5 tests in a script calculation, I saved 26 seconds when applied to 100,000 records using Choose() over Case(), even with the Case() optimized by branch prediction. 26 seconds is a long time when a User is sitting there waiting for results.

Any Choose() call will only evaluate a maximum of two expressions (in FM7), where the Case() may require many more, depending upon the data (and whether branch prediction is applied). Here's the abstract comparison:

Case( x1, y1, x2, y2, x3, y3, x4, y4, ... )

1) Solve expression x1

a) It was true: solve and return y1

It was false: Solve: Case( x2, y2, x3, y3, ... )

- Solve expression x2

A) It was true: solve and return y2

: It was false: Solve, Case( x3, y3, ... )

etc.

Choose( n, y0, y1, y2, y3, y4, y5, ... )

1) Solve n to a number

2) Solve and return y[n]

In a normal work-place scenario, branch prediction is usually possible. By restructuring one Case() calc and putting the 60% records first, 30% second, 5% third, etc. (compared to the reverse order), I saved over 4 minutes on a 400,000-record lineitem script. This is no small peanuts!!

So, by simply keeping these two concepts in mind (using Choose whenever possible or attempting to predict the data when using Case(),If() or OR, we can produce (sometimes much) faster results. And that is ALWAYS important in my book and I felt it was worth mentioning.

LaRetta

[comment]

[Himitsu]

Ok... I guess I feel that a case would be best. But I have the same situation and I want to run mulitple tests with results. Let's say I have

Case ( student status = "Stopped" and Month ( Get ( CurrentDate ) ) ≥ 4 and ≤ 10;Teacher 01;Teacher 02

)

Now I know there is a problem... because after the ≥4 and... it wants to close it... so how would I get the result from this case? If the student's status is stopped and it is from April to October, then it puts in the field teacher 01 else teacher 02....

[Himitsu]

Ok, what I figured out somethings... I had to repeat the and statement.. so I did the

Month ( Get ( CurrentDate )) >4 and Month ( Get ( CurrentDate ) )<10

and it worked. I think I am liking this case statement other than what if I want nothing entered in the field... I couldn't get "" to enter in nothing if all else failed... so if I ran

case(

a=2;2

b=3;3;""

)

that didn't seem to anything but run the two tests and not update or make the field empty if the tests failed... could I enter a setField command as the default?

[comment]

I am not sure I understand - there's a bug here using higher ASCII characters. Please use >= for "greater than or equal to", and <= for "less than or equal to". Or click "More Graemlins" and insert the characters from there.

Meanwhile I'll take a guess at your problem:

Case (

student status = "Stopped"

and

Month ( Get ( CurrentDate ) ) >= 4

and

Month ( Get ( CurrentDate ) ) <= 10 ;

Teacher 01 ;

Teacher 02

)

[comment]

case(

a=2;2

b=3;3;""

)

If a and b are fields, and the formula is in a calculation field (NOT auto-enter calculation in a number field), then it will update when a or b are modified.

If the default value is blank, you don't need to specify it:

Case (

a = 2 ; 2 ;

b = 3 ; 3

)

is sufficient.