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

    Using the CASE function

    cathyhampshire1

      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
          siplus

          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
            alecgregory

            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 )

                                - LESS TRADE

                      )

                           * .029;

                 0

            )


            This assumes 4/1/2016 means April 1 2016



            • 3. Re: Using the CASE function
              cathyhampshire1

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

              • 4. Re: Using the CASE function
                cathyhampshire1

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

                • 5. Re: Using the CASE function
                  siplus

                  next step: reading the post AND the post time.

                  • 6. Re: Using the CASE function
                    erolst

                    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
                      alecgregory

                      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
                        erolst

                        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.

                        Not with your formatting 

                         

                        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
                          alecgregory

                          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
                            ch0c0halic

                            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

                            (Selling Price- LESS TRADE)

                            )

                            //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
                              mathepac

                              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
                                erolst

                                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
                                  mathepac

                                  Will do, thanks.

                                   

                                   

                                  mathepac