The Case function in a calculation

A recent post on the TechNet forum, plus a succession of typically helpful answers, provided a very useful tutorial in how best to use the Case function to calculate a marked up cost, with the markup being based on the value of Cost.

Here’s the original formula.  “Why won’t it work?” was the cry for help:

Case (
Cost < “5”  ;   Cost * “4”   ;
Cost > “5.01” and Cost < “15”  ; Cost * “3.5” ;
Cost > “14.99” and Cost < “25”  ;  Cost * “3”  ;
Cost > “24.99” and Cost < “40”  ;  Cost * “2.75” ;
Cost > “39.99” and Cost < “60”  ;  Cost * “2.5” ;
Cost > “59.99” and Cost < “100” ;  Cost * “2.25” ;
Cost > “99.99” and Cost < “200”  ; Cost * “2” ;
Cost > “199”; Cost * “1.6”)

Well, with the field “Cost” defined as a number, and the calculation result defined as a number, the first step is to remove the quotes, which makes Filemaker see the numeric values as numbers, rather than as text.

So, V2:

Case (
Cost < 5 ;   Cost *4   ;
Cost > 5.01 and Cost < 15  ; Cost * 3.5 ;
Cost > 14.99 and Cost < 25  ;  Cost *3  ;
Cost > 24.99 and Cost < 40  ;  Cost * 2.75 ;
Cost > 39.99 and Cost < 60  ;  Cost * 2.5 ;
Cost > 59.99 and Cost < 100 ;  Cost * 2.25 ;
Cost > 99.99 and Cost < 200  ; Cost * 2 ;
Cost > 199; Cost * 1.6)

This will now work, but it’s making much harder work of the calc than it needs to.  Filemaker only works through the Case statement until it meets a true condition, then it bails out – there’s therefore no need for all of these > and < evaluations.

So, enter V3:

Case  (
Cost  <  5  ; Cost * 4   ;
Cost  < 15 ; Cost * 3.5 ;
Cost  < 25 ; Cost * 3   ;
Cost  < 40 ; Cost * 2.75  ;
Cost  < 60 ; Cost * 2.5 ;
Cost  < 100 ; Cost * 2.25  ;
Cost  < 200 ; Cost * 2   ;
Cost * 1.6)

(Note the final  default value, 1.6, which applies to any value of Cost equal to or greater than 200 – no need for an evaluation there; if we get that far, that’s the value to use.)

The next efficiency is to use calculation variables, for the value of Cost, and the markup to be applied.

So, finally, V4:

Let (  [
$Cost = Cost ;
$Markup  = Case  (
$Cost <  5  ;  4 ;
$Cost < 15  ;  3.5 ;
$Cost < 25  ;  3 ;
$Cost < 40  ;  2.75 ;
$Cost < 60  ;  2.5 ;
$Cost < 100  ; 2.25 ;
$Cost < 200  ; 2 ;1.6)];
$Cost * $Markup)

But then came another interesting point, i.e. whether the Cost field itself is the right place for the calculation to happen.  In the case of a “net price” such as this, which may need to be overridden, the better way is to have a separate Markup field, defined as an auto-enter calc, with the initial value of the markup amount calculated as above.  The (new) Price field is then defined as a simple calc field, i.e.  Price = Markup * Cost.  On the Markup fields, the option “Do not replace existing value of field (if any)” should be ticked, to prevent historical and “override” data being replaced.

For a more comprehensive look at calc and auto-enter fields, see here.  Thanks, as ever, to the Technet contributors who combine to make it such a great resource.

Advertisements

2 Comments on “The Case function in a calculation”


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s