Calculations with many variables

[buckbuck]

I have a situation where I am trying to write a calculation with many variables:

 

Buyer Country

Buyer State

Ship to Country

Ship to State

Before Date

After Date

 

such that part of the script looks like this;

 

( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and ( CONTACTS::CountryCompany  ≠  "United States" or CONTACTS::CountryCompany  ≠  "Canada" ) ; " Tax 1 ";
( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered > Date ( 4 ; 1 ; 2013 ) ) ) and ( CONTACTS::CountryCompany  ≠  "United States" or CONTACTS::CountryCompany  ≠  "Canada" ) ; " Tax 1 ";

( (  Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and ( ( CONTACTS::CountryCompany  =  "United States"or CONTACTS::CountryCompany  =  "Canada" ) and (ShipTo_ CountryCompany ≠ "Canada" ) )  ; " Tax 1 ";
( (  Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered > Date ( 4 ; 1 ; 2013 ) ) ) and ( ( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" ) and (ShipTo_ CountryCompany ≠ "Canada" ) )  ; " Tax 1 ";

( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and ( ( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Nova Scotia")); " HST 15% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Prince Edward Island")); " HST 14% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Ontario")); " HST 13% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "New Brunswick")); " HST 13% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Newfoundland")); " HST 13% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "British Columbia")); " HST 12% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Saskatchewan")); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Manitoba")); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Alberta")); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Quebec")); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and ( ( ( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Yukon") ) ); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Northwest Territories")); " GST 5% ";
( ( ( Date_Ordered > g_Tax Comparison_Date 1) and (Date_Ordered < Date ( 4 ; 1 ; 2013 ) ) ) and (( CONTACTS::CountryCompany  =  "United States" or CONTACTS::CountryCompany  =  "Canada" )) and ((ShipTo_ CountryCompany = "Canada")) and (ShipTo_ StateCompany = "Nunavit")); " GST 5% ";

 

 

All to determine the applicable tax regime on an order.

 

Can anyone point me towards a more elegant approach to this calculation?

My head is spinning now

Thanks

[eos]

Recommended way for getting a result with many variables:

 

1. You should try to put all of those factoids into their own table (where each record is a combination of country, territory and tax number)

2. Then look up the correct value by using a relationship from your order table to the lookup table (or, hell yes, SQL).

3. If necessary, use live data to fine-tune the result.

 

This is the basic idea, which in a non-ideal world doesn't always work out that simple; but there is a number of solutions offered in this board.

 

In many cases, though, it's OK to build your own lil' lookup table inside a calculation or script, but then you should use some techniques to simplify the expression; in a calculation, use Let(), in a script, Set Variable, to define simpler expressions to use subsequently, to express your logic as well as to build more complex expressions. This makes your logic more explicit and readable, and you can also reduce the number of disk accesses for the application by working in memory with often-used data stored in fields.

 

Your expression from above could be simplified as :

 

Let

( [

theCountry = CONTACTS::CountryCompany ; // needs to access field on disk only once

countryIsNorthAmerica = ( theCountry = "United States" or theCountry = "Canada" ) ;

shipToCanada = (ShipTo_ CountryCompany = "Canada") // same here

province = ShipTo_ StateCompany ; // and again

thePertinentDate = Date ( 4 ; 1 ; 2013 )  ; // define objects once

dateOrdered = Date_Ordered ;

comparisonDate = g_Tax Comparison_Date 1:

orderDateIsNewer = ( dateOrdered > comparisonDate ) ; // encapsulate complicated expressions, like those three

dateOrderedIsBetween = orderDateIsNewer and (dateOrdered < thePertinentDate ) ;

dateOrderedIsOutside = orderDateIsNewer and (dateOrdered > thePertinentDate )

] ;

 

case 

 

( // open case 1

 

not shipToCanada ;

 

case   // open case 2

 

(

 

dateOrderedIsBetween and not countryIsNorthAmerica ; " Tax 1 ";

dateOrderedIsOutside and not countryIsNorthAmerica ; " Tax 1 ";

 

dateOrderedIsBetween and countryIsNorthAmerica ; " Tax 1 ";

dateOrderedIsOutside and countryIsNorthAmerica ; " Tax 1 "

 

) ;

 

dateOrderedIsBetween and countryIsNorthAmerica ; // shipToCanada is implicit, now we need to differentiate between provinces

 

case // case 3

 

(

 

province = "Nova Scotia" ; " HST 15% "

province = "Prince Edward Island" ; " HST 14% ";

province = "Ontario" ; " HST 13% ";

province = "New Brunswick" ; " HST 13% ";

province = "Newfoundland" ; " HST 13% ";

province = "British Columbia" ; " HST 12% ";

 

// ********* make 5% the default case for Alberta, Manitoba, Nunavit, Northwest Territories, Quebec, Saskatchewan, Yukon

 

" GST 5% "

 

)

)

)

)

 

You see how those Canadian provinces are a great fit for a lookup table? You could replace the entire last part of the calculation by something like CanadianProvinces::taxRate.

 

btw, in your original calculation nothing ever would have been shipped to taxed for Canada, because one of the simpler expressions would have triggered first. I'm not sure if the correction above gives the correct result; you need to check that out in your system, and now you have a better way of tackling that.

 

It's still not simpler (and all those cases are confusing), but the briefer expressions let you better format the calculation, and this in turn gives you some visual clues as to where happens what - which in turns often lets you simplify it even further, because you discover a fact that was previously hidden. (Originally this what formatted with tab indentations, but they got lost when pasting)

[Wim Decorte]

Instead of cramming it all in one calc you could make this a "decision tree" script where you use IF and ELSE IF nested appropriately.  Much easier to troubleshoot.

[buckbuck]

In consideration of the If Ellse approach I have attached here is a 'result stream'. Using the If Else function I do not understand how to script my calculation to achieve the ' Text Result'?  Any help much appreciated.

 

[eos]

In consideration of the If Ellse approach I have attached here is a 'result stream'.

 

There's nothing attached to your post, if that's what you're saying. Otherwise you need to give us more information as to what your problem is.

[buckbuck]

Here is the attachment - I hope. I wanted to send an excel doc but I do not have permission (?). Thank you

Sheet1.pdf

[Lee Smith]

Most file types have to be zipped. So, either zip your excel file, Or put it into a folder, and zip that. For more information on how to do this, read this link.

 

 

How to add an attachment to a reply

[eos]

I would recommend to actually post the database, so we could look at the cause rather than the symptoms. 

[buckbuck]

Hello eos

 

It is not really possible to post the database as it is too broad. The setup is a CONTACTS, INVOICE, LINES, PRODUCT scenario with all the expected relationships. The Invoice has a Buyer set (name, address, city ect) and a ShipTo set for those ocassions. My goal is to structure a calculation that determines the applicable taxes. It might be easiest to just select them from a value list and a calc for the amount but I thought automation would be elegant. As you can see from the pdf there are a lot of permutations. Thanks

[Rick Whitelaw]

Tax rates change. I would have a tax rate table and use auto enter or a lookup. The provincial and territorial abbreviations are unique (ON, QC etc.) and could be used as a match field.