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

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

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.