Multiple tests in Case Function Each with 3 parameters - SOS!

[lizzie]

Greetings

Me a.g.a.i.n!

I need to identify the incentive value for sales consultants but each test has three parameters - a date range, sales total and GP percentage total.

I have tried to do an "and" to bind these three tests as one but the calculation fails!

Any help would be greatly appreciated!

Mucho gracias in advance!

The code is as follows:

Case (

// for incentives paid between August and October on Silver Price List

// for targets of 550000 at 14 15 16

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 550000 and Nett Sales Less Support < 750000 and

Total GP Sales Percentage ≥ 14 < 15 ;

550 ;

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 550000 and Nett Sales Less Support < 750000 and

Total GP Sales Percentage ≥ 15 < 16 ;

1100 ;

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 550000 and Nett Sales Less Support < 750000 and

Total GP Sales Percentage ≥ 16 ;

1650 ;

""

)

Edited January 18, 2008 by Guest

[Genx]

If( 

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 550000 and Nett Sales Less Support < 750000 ;

  Case( 

    Total GP Sales Percentage ≥ 16 ;

    1650 ;

    Total GP Sales Percentage ≥ 15;

    1100 ;

    Total GP Sales Percentage ≥ 14 ;

    550 

  )

)

[lizzie]

Genx -

You ARE "da domb". . .

Thank you so much!

I thought about including a "case function" within an "if function" but I was obviously doing something wrong...

Really appreciate the solution! Feel a bit bashful now as the solution was quite simple!

[lizzie]

Ok.. hit another wall:

Tried to combine 2+ of those statements together...

i.e. I have to repeat the same calculation to include values for >=750,000 < 1,000,000 and >= 1,000,000. . .

That was initially why I used the Case statement as the leading function as it can take multiple tests...

Thus, I want to do the following:

If(

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 550000 and Nett Sales Less Support < 750000 ;

Case(

Total GP Sales Percentage ≥ 16 ;

1650 ;

Total GP Sales Percentage ≥ 15;

1100 ;

Total GP Sales Percentage ≥ 14 ;

550

)

)

or

If(

Start Date ≥ 1/8/2007 and End Date ≤ 31/10/2007 and

Nett Sales Less Support ≥ 750000 and Nett Sales Less Support < 1000000 ;

Case(

Total GP Sales Percentage ≥ 16 ;

2250 ;

Total GP Sales Percentage ≥ 15;

1500 ;

Total GP Sales Percentage ≥ 14 ;

750

)

)

Willing to assist me? :

[Søren Dyhr]

Why use calculations for this, when a multicriteria and a lookup next higher does the same, only thing is to make an extra table with the thresholds.

I learned the lesson very early in my filemaker career, that building large matrixes for discounts becomes slower the more conditions the calc contains.

--sd

[comment]

I agree. It's not only a question of speed: these values are bound to change over time.

[Søren Dyhr]

Yes, I forgot that!

--sd

[lizzie]

Ok - without sounding like I'm too wet behind the ears, what exactly do you mean by a "multicriteria and a lookup next higher"?

Are you suggesting that I create a table with the values?

Apologies - I'm just not getting the picture per se...

[Genx]

Are you suggesting that I create a table with the values?

Yes.

[lizzie]

I have been playing around with a new table and am now battling with the relationship part. The table lists the criteria but sales will never hit 550,000, 750,000 or 1,000,000 exactly on the dot.

Thus, I originally considered making the relationship one which matches on date range, sales less support and GP percentage. Since these will never match, this relationship fails.

I will continue to prod away but any advice would be great!

[Søren Dyhr]

Not every relation need to be exact matching, take a look in the relations def. < > ≥≤ and X beyond =

To see how ranges work take a look here:

http://www.newcenturydata.com/downloads/filter.zip

--sd