Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_ULG
Creator
Creator

Multiple if statements in expression

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?

2 Replies
alis2063
Creator III
Creator III

Can you please provide the snapshot.

Qlik_ULG
Creator
Creator
Author

=

(// ++++++++++++++++++++++=++++++++++++++++++++++++++++ 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

________________________________________________________