13 Replies Latest reply on Apr 22, 2016 3:26 AM by mathepac

# Using the CASE function

I am trying to get the case function to work and it does not seem to evaluate the second part of my test.  Not sure what I am doing wrong.  Here is my formula:

Case(Taxable = "yes" and Sale Date ≥ 04/01/2016 ; (Selling Price+DELIVERY  HANDLING - LESS TRADE) * .029; Taxable = "yes" and Sale Date <= 04/01/2016 ; (Selling Price- LESS TRADE) * .029; Taxable = "no" and Sale Date ≥ 04/01/2016 ; 0.00; Taxable = "no" and Sale Date <= 04/01/2016 ; 0.00)

It doesn't want to look at the Sale Date.  It is just evaluating it on Taxable.  Any suggestions?  I am not good at writing formulas.

Cathy

• ###### 1. Re: Using the CASE function

Try Sale date ≥ Date(1;4;2016)

Generally you should not hardcode stuff anyway.

so you should write it as

Set Variable [\$milestone; date(1; 4; 2016)]  // emergency solution; better is to pass the date as a parameter

Set Variable [\$dayClause; yourTable::Sale Date ≥ \$milestone]

Case (

.... and \$DayClause; ...;

.... and not \$DayClause; ....;

)

undSoWeiter

1 of 1 people found this helpful
• ###### 2. Re: Using the CASE function

You need to format the dates using the Date function

Case (

Taxable = "yes" and Sale Date ≥ Date ( 4; 1; 2016 );

(Selling Price+DELIVERY  HANDLING - LESS TRADE) * .029;

Taxable = "yes" and Sale Date <= Date ( 4; 1; 2016 );

(Selling Price- LESS TRADE) * .029;

Taxable = "no" and Sale Date ≥ Date ( 4; 1; 2016 ); 0.00;

Taxable = "no" and Sale Date <= Date ( 4; 1; 2016 ); 0.00

)

But Case is not the right choice here, though it will work to an extent. Use a couple of Ifs instead, much nicer

If (

Taxable = "yes";

(

Selling Price

+ If ( Sale Date ≥ Date ( 4; 1; 2016 ); DELIVERY  HANDLING )

)

* .029;

0

)

This assumes 4/1/2016 means April 1 2016

• ###### 3. Re: Using the CASE function

Thanks so much.  It worked.  Learn something new every day.

• ###### 4. Re: Using the CASE function

I really appreciate this.  I did not know I needed to format the date.  But it worked.

• ###### 5. Re: Using the CASE function

next step: reading the post AND the post time.

• ###### 6. Re: Using the CASE function

alecgregory wrote:

[…]

But Case is not the right choice here, though it will work to an extent. Use a couple of Ifs instead, much nicer

It doesn't become true just because you keep repeating it …

• ###### 7. Re: Using the CASE function

I know !

But in this case(ha!) If is clearly better! The If approach explains the business logic in a linear way that's much easier to follow than the Case equivalent.

Right off the bat we are showing that, at the highest level, the calculation is distinguishing between items that are taxable and items that are not taxable. That's a nice simple chunk of information to digest.

Following on from that we can see that we have one basic calculation for taxable items. As we read through that calculation we see that there is a single variation on this calculation based on the date the Sale took place. If it was on or after April 1 2016 we include Delivery Handling in the calculation, if it was before April 1 2016 we do not.

Finally, if the item wasn't taxable we see that we should return zero.

If I'm a developer coming to that cold, it's very quick to work out what's going on with the calc. If I'm coming to the Case version cold, I would get there in the end, but it would take longer.

• ###### 8. Re: Using the CASE function

alecgregory wrote:

If I'm a developer coming to that cold, it's very quick to work out what's going on with the calc.

I prefer:

Case (

Taxable = "yes" ;

Let ( [

delivery =

Case (

Sale Date ≥ Date ( 4 ; 1 ; 2016 ) ;

DELIVERY  HANDLING

) ;

theAmount = Selling Price + delivery - LESS TRADE

] ;

theAmount * .029

) ;

0

)

or even better to reverse it – otherwise the 0 may get lost in all that:

Case (

Taxable ≠ "yes" ;

0 ;

Let ( [

delivery =

Case (

Sale Date ≥ Date ( 4 ; 1 ; 2016 ) ;

DELIVERY  HANDLING

) ;

theAmount = Selling Price + delivery - LESS TRADE

] ;

theAmount * .029 // which rate IMO shouldn't be hardcoded …

)

)

You can avoid nested If()s with Case(), but if you must nest, then If() isn't a magic bullet …

• ###### 9. Re: Using the CASE function

The formatting/indenting style may not be to everyone's taste but, unsurprisingly, I still prefer mine from a technique point of view. No need for a tricky to parse Let (nothing wrong with Lets in general, but they do require a certain amount of parsing in your head and I don't think it's worth it here). My version has only 5 fields or variables, each appearing once. Yours has 7, two of which appear twice.

I agree that the tax rate probably shouldn't be hardcoded. I have a feeling we're not going to agree on much else.

• ###### 10. Re: Using the CASE function

Case (

/Logically you should first be checking

Taxable = "no" ; 0 ;

//Now we already know it is taxable so we only need to check ONE date.

Sale Date ≥ GetAsDate ( "04/01/2016" ) ; (Selling Price+DELIVERY  ANDLING - LESS TRADE) ;

//Now we know its taxable and the date is < 4/1/2016 so we can use the default

)

//and now we can multiply by the tax rate

• .029

Stating the obvious:

0 * .029 = 0 tax

and the other two produce the desired amount.

This simplification involved turning around the Tax check, knowing a Case function stops calculating at the first true result, and realizing that any previous test that failed also applies in the negative to all subsequent tests.

Example: taxable = "no" failed so it means it is taxable.

• ###### 11. Re: Using the CASE function

I have to agree, KISS. IF is the simple and elegant solution here and hard-coding has no place a long-term production solution, it smacks of amateurism temporary fixes.

It is always good practice to set default / zero or null values in calculation fields or to "initialise them"  in procedural language terms.

• ###### 12. Re: Using the CASE function

alecgregory wrote:

I have a feeling we're not going to agree on much else.

Let's agree to be nice to each other.

• ###### 13. Re: Using the CASE function

Will do, thanks.

mathepac