Multiple if statements in expression

Qlik_ULG — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Multiple if statements in expression

alis2063 — Fri, 09 Feb 2018 17:27:27 GMT

Can you please provide the snapshot.

Re: Multiple if statements in expression

Qlik_ULG — Mon, 12 Feb 2018 13:30:35 GMT

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

________________________________________________________

topic Re: Multiple if statements in expression in QlikView

Multiple if statements in expression

Re: Multiple if statements in expression

Re: Multiple if statements in expression