Jump to content
Sign in to follow this  
Serenity

Complex Calc Help

Recommended Posts

Hi,

I am building a duty calculator and have attempted it but it's stack full of multiple fields and multiple case statements. I am unfamiliar with let statements/custom functions but wondering if they would be of value to use, and if so, how.

When the following protocol is assesed it is on the "fee"

EXAMPLE 1

fee = 15,000 (rate 1 applies)

15,000/100 = 150; 150*1.5=$225

EXAMPLE 2

fee = 156,000 (rate 4 applies)

2350 + ((56,000/100)* 3.25) = $4170

[color:purple]RATES

[color:purple]Not more than $20,000

$1.50 for every $100.00 or part $100.00

More than $20,000 but not more than $50,000

$300.00 + $2.25 for every $100.00 or part $100.00 over $20,000

More than $50,000 but not more than $100,000

$975.00 + $2.75 for every $100.00 or part $100.00 over $50,000

More than $100,000 but not more than $250,000

$2350.00 + $3.25 for every $100.00 or part $100.00 over $100,000

More than $250,000 but not more than $500,000

$7225.00 + $3.50 for every $100.00 or part $100.00 over $250,000

More than $500,000 but not more than $700,000

$15975.00 + $4.00 for every $100.00 or part $100.00 over $500,000

More than $700,000

$23975.00 + $4.50 for every $100.00 or part $100.00 over $700,000

So I was thinking this sort of thing




[color:red]FiguresDutyMultiplier[/color]

Case(FiguresFee  ≥ 20000; Int(FiguresFee/100); FiguresFee>20000 and FiguresFee  ≤ 50000; FiguresFee-20000)



[color:red]Duty[/color]

Case(FiguresFee <=20000; 1.5*FiguresDutyMultiplier; 



FiguresFee >20000 and FiguresFee<=50000; 300+2.25*FiguresDutyMultiplier;1)

TIA

Edited by Guest

Share this post


Link to post
Share on other sites

Well case statements terminate when they evaluate to true so you could really just do:

Case( FiguresFee <=20000 ; do x ;

FiguresFee <=50000 , do y;

FiguresFee <=100000 ; do z ;

FiguresFee <=250000 ; do next one etc...

)

Share this post


Link to post
Share on other sites

The Let() function allows you to create temporary values you can access later in the calculation.

If you're worried that this table will be changing, or the formula, I would suggest breaking them out to make modifications easier:


Let( [ 

         /* determine the rate to use */

         values = Case( amount > 700000; List( 700000; 23975; 4.5 );

                       amount > 500000; List( 500000; 15975; 4 );

                       amount > 250000; List( 250000; 7225; 3.5 );

                       amount > 100000; List( 100000; 2350; 3.25 );

                       amount > 50000; List( 50000; 975; 2.75 );

                       amount > 20000; List( 20000; 300; 2.25 );

                     /* default */   List( 0; 0; 1.5 )

                         );



        baseAmount = GetAsNumber( GetValue( values; 1 ) );

        fixedCharge = GetAsNumber( GetValue( values; 2 ) );

        incrRate = GetAsNumber( GetValue( values; 3 ) )

      ];



  /* compute the duty */

  fixedCharge + (incrRate * Int( (amount - baseAmount) / 100 ))

)

List()/GetValue() was new to 8.5, if you're only using 8.0 you can work around it using MiddleValues().

Needless to say, if you're not comfortable with what is going on here you might not want to use it in your solution, as you're going to have to fix the bugs in it. :

Share this post


Link to post
Share on other sites

It's not a good idea to hard-code actual rates into the calculation formula. Rates may change, and it should be possible for the user to change them without requiring any developer privileges or skills. More importantly: if you change the rates in the calculation formula itself, all your existing records are going to recalculate and you will lose your entire history.

The rates should be looked up from a related table, where each record has a LowLimit, Base and Extra fields. The relationship should match Fee to Rates::LowLimit, and the lookup should be using the option to 'copy next lower' if no exact match.

Once you have looked up the LowLimit, Base and Extra figures, the calculation can be simply:

Base + Extra * Ceiling ( ( Fee - LowLimit ) / 100 )

Share this post


Link to post
Share on other sites

Thanks, I'll review your responses and try to implement them.

Comment - the rates are generally in place for a couple of years, and this database is only required till settlement which will be within the next month so it varying isn't an issue. :

Edited by Guest

Share this post


Link to post
Share on other sites

It requires no more effort, possibly even less, to do it right.

Share this post


Link to post
Share on other sites

There is no need to read an imaginary attitude into my post - or to get personal at all. I have pointed out that hard-coding data into calculations is not good practice. Your reply implies there might be some advantage in departing from such practice in your case. I don't think so - and that's all I said.

Share this post


Link to post
Share on other sites

I think you're TOO POLITE, Comment.

Hardcoding data into calculations SUX BIG TIME. But let Brainiac find out for themself. We all did.

LOL I'm kidding of course. (Not about the SUX BIG Time though.)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

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