Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases.
**IMPORTANT DETAILS**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Multiple if statements in expression

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Qlik_ULG

Creator

2018-02-09
09:53 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

776 Views

2 Replies

alis2063

Creator III

2018-02-09
12:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can you please provide the snapshot.

616 Views

Qlik_ULG

Creator

2018-02-12
08:30 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

=

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

________________________________________________________

616 Views