Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am building a large complex expression to calculate some total figures, but it seems as if a null value, or 0.00 amount breaks the expression down the line. I am not sure where. Each expression follows the same pattern but adds on another line each time.
I'll try explain it in pseudo code.
I have my base calculation which sums some columns to get a total figure. they are the base costs.
then add some additional charges to these costs. (a simple if charge type =1, then rate)
then calculate an additional charge when can either be a flat amount, or a percentage of the previously calculated total + additional costs
then calculate a discount on the new final amount, which again can be a flat fee or percentage based on the new total.
the finally a complete total, which is just a total minus the last charge
I have tried building it step by step, but generally fall at certain hurdles. I can calculate the additional charges separately but when I try to add them together, it somehow breaks. I think my if statements may be out, or not have enough.
Any advice on how to build this?
Can you please provide the snapshot.
=
(// ++++++++++++++++++++++=++++++++++++++++++++++++++++ Total Charges
((sum(word_count_100))*rate_count_100) +
((sum(word_count_rep))*rate_count_rep) +
((sum(word_count_perfect))*rate_count_perfect) +
((sum(word_count_95_99))*rate_count_95_99) +
((sum(word_count_85_94))*rate_count_85_94) +
((sum(word_count_75_84))*rate_count_75_84) +
((sum(word_count_no_match))*rate_count_no_match) +
((sum(word_count_50_74))*rate_count_50_74)
+
// ++++++++++++++++++++++=++++++++++++++++++++++++++++ charges to be added
(
if([othercharges_Charge_Type] = '7', ([othercharges_Charge_Rate]* [Proof_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '6' , ([othercharges_Charge_Rate]* [multilingual_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '8' , ([othercharges_Charge_Rate]* [Certification Hours]) / Target_Language_COUNT , '0')
)
)
calculate total charges
_______________________________________________________
=
(// ++++++++++++++++++++++=++++++++++++++++++++++++++++ Total Charges
((sum(word_count_100))*rate_count_100) +
((sum(word_count_rep))*rate_count_rep) +
((sum(word_count_perfect))*rate_count_perfect) +
((sum(word_count_95_99))*rate_count_95_99) +
((sum(word_count_85_94))*rate_count_85_94) +
((sum(word_count_75_84))*rate_count_75_84) +
((sum(word_count_no_match))*rate_count_no_match) +
((sum(word_count_50_74))*rate_count_50_74)
+
// ++++++++++++++++++++++=++++++++++++++++++++++++++++ charges to be added
(
if([othercharges_Charge_Type] = '7', ([othercharges_Charge_Rate]* [Proof_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '6' , ([othercharges_Charge_Rate]* [multilingual_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '8' , ([othercharges_Charge_Rate]* [Certification Hours]) / Target_Language_COUNT , '0')
)
)
//++++++++++++++++++++++++++++++++++++++++++++++ add PM Fee
+
( /
IF([PM FEE as %] = '0' , [PM Fee Rate] / Target_Language_COUNT)
+
IF([PM FEE as %] = '1' ,
(
=
(// ++++++++++++++++++++++=++++++++++++++++++++++++++++ Total Charges
((sum(word_count_100))*rate_count_100) +
((sum(word_count_rep))*rate_count_rep) +
((sum(word_count_perfect))*rate_count_perfect) +
((sum(word_count_95_99))*rate_count_95_99) +
((sum(word_count_85_94))*rate_count_85_94) +
((sum(word_count_75_84))*rate_count_75_84) +
((sum(word_count_no_match))*rate_count_no_match) +
((sum(word_count_50_74))*rate_count_50_74)
+
// ++++++++++++++++++++++=++++++++++++++++++++++++++++ charges to be added
(
if([othercharges_Charge_Type] = '7', ([othercharges_Charge_Rate]* [Proof_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '6' , ([othercharges_Charge_Rate]* [multilingual_hours]) / Target_Language_COUNT , '0')
+
if([othercharges_Charge_Type] = '8' , ([othercharges_Charge_Rate]* [Certification Hours]) / Target_Language_COUNT , '0')
)
)
//++++++++++++++++++++++++++++++++++++++++++++++ multiply by PM Fee %
/
100 * [PM Fee Rate]
)
)
included rate to be added on
________________________________________________________