Jump to content
Sign in to follow this  
Jredeemer14

Sales tax calculation

Recommended Posts

Hello, I'm trying to make a calculation for sales tax for an appliance business. We live on the border of michigan and indiana so there are different sales tax scenarios. I need a sales tax field to know how to calculate based on the following scenarios:

If an item is being delivered is being delivered to indiana (IN), then subtotal sould be multiplied by .07 (Indiana sales tax). if not, it should be multiplied by .06 (MI sales tax). I have something like this but it doesn't work. Any help?

if isempty(Delivery?)

and

Customers::City = IN

then

(Subtotal )*.07

Delivery is either yes or empty in case you are wondering. Any help would be appreciated, thanks - Jeff

else

(Subtotal )*.06

Share this post


Link to post
Share on other sites

The sales tax rate should be stored in a perferences table or a state table. Then the rate can be looked up or auto-entered into a sales tex rate field in the invoice.

Share this post


Link to post
Share on other sites

That would work, except that if the customer is from Indiana and they pick it up at our store, we only charge 6% sales tax as opposed to if we deliver it and they are from indiana, then we 7%. If the customer lives in michigan, regardless it is 6%. So it is a little bit of a dilema.

Share this post


Link to post
Share on other sites

You were close:

Subtotal * Case( IsEmpty(Delivery) and Customers::City = IN ; .07 ; .06 )

Share this post


Link to post
Share on other sites

That has to be close, but I keep getting an error saying the specified table cannot be found and highlights "and Customers"

Subtotal * Case( IsEmpty(Delivery?) and Customers::State = IN ; .07 ; .06 )

Share this post


Link to post
Share on other sites

If the rule is "Item is delivered to Indiana then .07", then I think the formula should be:

Subtotal * Case( IsEmpty(Delivery?) and Customers::State = "MI" ; .06; .07 )

I like to keep each test separate, so I'd write it this way:

Subtotal *

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; .07; .06);

.06)

Also, if the field is named "Delivery?" (with the question mark), it might be good to remove the question mark just for simplicity's sake.

Share this post


Link to post
Share on other sites

Subtotal *

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; .07; .06);

.06)

That works like a charm! Thanks a bunch, I have another similar calculation that involves the same fields that I tried modifying the calculation above to work, but no luck.

I have another field that I called "Sales Tax State" which will tell me what state I need to pay taxes to. I only pay Indiana sales tax for people who are from indiana and have it delivered to indiana so I need the sale type of calculation, but the output just has to say either MI or IN (for mi sales tax or in sales tax), so I tried something like this, but it didn't work, any help would be appreciated:

If( Not(IsEmpty(Delivery?));

If(Customers::State = "IN"; IN; MI);

MI)

Share this post


Link to post
Share on other sites

I believe that the problem is the same one you had with my original calc: each "IN" needs to have quotes around it, and "MI" does too. Also I copied and pasted your code example, and didn't read your requirements closely. Sorry about that, it should be:

Subtotal * Case( Delivery? = "Yes" and Customers::State = "IN" ; .07 ; .06 )

If you want to separate each condition explicitly, I'd do it this way rather than use nested IF statements:

Subtotal * 

Case( 

 Customers::State = "MI" ; .06 ; 

 IsEmpty(Delivery?) ; .06 ; 

 .07 )




or 




Subtotal * 

Case( 

 Customers::State = "MI" ; .06 ; 

 Delivery? = "Yes" ; .07 ; 

 .06 )




The other calc would be: 




Case( 

 Customers::State = "MI" ; "MI" ; 

 Delivery? = "Yes" ; "IN" ; 

 "MI" )

Share this post


Link to post
Share on other sites

If you want to separate each condition explicitly, I'd do it this way rather than use nested IF statements

Well, I say To-MAY-to, you say To-MAH-to

let's call the whole thing off!

:)

David

Share this post


Link to post
Share on other sites

There's more here that just a nuance. The Case() function stops evaluating when it finds the first test that returns true. So it's not only more readable and easier to maintain than nested If()'s - it's also more efficient.

In fact, in applications where speed is of the essence, you'd be well advised to order your tests according to their statistical probabilities (the most frequent cases first), so the number of required evaluations is reduced overall.

Speaking of good practice - I agree with John that hard-coding tax rates into the calculation formula is not.

Share this post


Link to post
Share on other sites

Here's how I understand it (and I did think about this for quite a while before making my flip comment):

In this case, if you evaluate the first condition in the top level IF statement, then the second IF statement will only get evaluated when the first one is true. If it's false, then only the one evaluation takes place. So I don't see an added burden.

I grant that using a NOT construct adds an extra evaluation, and perhaps that was what you meant?

And I agree that a separate table makes all this much better...

David

Share this post


Link to post
Share on other sites

if you evaluate the first condition in the top level IF statement, then the second IF statement will only get evaluated when the first one is true.

You are correct (except it's the other way around: the second If() statement will be evaluated only when the first one returns FALSE).

Still, I'd hate to be the one who has to re-arrange the order of tests in multiple nested If'()s structure. I don't think it's good practice, and I wouldn't recommend it to anyone - especially not to beginners.

Share this post


Link to post
Share on other sites

Reminds me of The Great Parentheses Debate.

@David, I probably wouldn't have said anything more on the subject if the OP hadn't asked another question. Since he did, I took the opportunity to revise my original calc as well as answer the new question. In doing so, I wanted to present the answer the way I would normally write it. I'm sorry if it came across as hostile to your solution; that wasn't my intention.

To some extent it comes down to personal style (see above link for dissent). That said, I agree with Michael that nested IF functions are more difficult to create and revise; worth considering since the OP self-identified as a beginner. And since we've come this far... I wasn't going to say anything... but whenever I see this:

Not(IsEmpty(field))

I am compelled to rewrite it as:

not IsEmpty( field )

Share this post


Link to post
Share on other sites

Personally, my mind balks with case statements, except when I am enumerating a list of exclusive options. Using it to nest clauses just seems weird to my strictly hierarchical brain, which expects that we'll deal in one statement with whether the Delivery box is checked and THEN decide what the sales tax is based on the Delivery... Which was probably why I went all fruity earlier (remember, the tomato is a fruit!). [bTW, I am NOT saying I think I'm right!]

Fitch: I didn't take it personally at all.

{David}

(Parenthetically speaking, here)

Share this post


Link to post
Share on other sites

Using it to nest clauses just seems weird

It does seem weird in this case - whether you're using Case() or nested If()'s. IIUC, you're speaking of 'branching' rather than 'nesting'. Your calc, written out in plain human terms goes:

Is there delivery? If yes, I need to see to which state. If it's Indiana, then tax is .07, otherwise it's .06. But it there's no delivery, then tax is .06 again.

But when you read this, it becomes very clear that tax is .07 in one case only, out of 4 possible:


Delivery:          T         T         F         F

State = Indiana:   T         F         T         F

Tax:              .07       .06       .06       .06

Which could be stated simply as:

Tax is .06, unless there's delivery and it's to Indiana - then it becomes .07.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.