Complex If Logic

[#1]

I attempting to create a complex if statement to handle 'Split Accounts.' Accounts where a margin % goes to one party and another % goes to another party. I have created logic to do this independent but combining them has provided to be a hurdle. Any assistance would be much appreciated. This statement will be an expression in a pivot table. See below for details.

This statement sums Margin cost for all orders. Then takes certain 'split' accounts and divides the margin in half. This worked fine until our commission rules changed for a split account.

=(sum(extended_price-(commission_cost*qty_shipped)))-(sum(IF(match(salesrep_id,
3208,3219,3293,3212, 3364,3295),
(extended_price-(commission_cost*qty_shipped))/2)))

This statement assigns a 20% margin to salesrep_id 1024. KenSpilt20 is a local variable taking the margin amount from the split account.

=if(salesrep_id=1024, sum((extended_price-(commission_cost*qty_shipped)))+kenSpilt20)

Similar to the last statement this one assigns 80% to salesrep_id 1023. KenSpilt80 is a local variable taking the margin amount from the split account.

=if(salesrep_id=1023, sum((extended_price-(commission_cost*qty_shipped)))+kenSpilt80)

Each statement works individually. My goal is to combine the 2 last statements into the first statement. Something similar to this... This equation does not work because multiple if logic exists. I am unsure of how to create a if statement combining all the logic together.

=(sum(extended_price-(commission_cost*qty_shipped)))-(sum(IF(match(salesrep_id,
3208,3219,3293,3212, 3364,3295),
(extended_price-(commission_cost*qty_shipped))/2)))
+(if(salesrep_id=1024, sum((extended_price-(commission_cost*qty_shipped)))+kenSpilt20))
+ (if(salesrep_id=1023, sum((extended_price-(commission_cost*qty_shipped)))+kenSpilt80))

Thanks for any help or a different approach to this.

[#2 · Accepted solution]

Do the conditions in the "if" statement need to be in the front-end for users to modify? If not, what if you used an Excel file as a data source with the appropriate split rates for each sales rep ID?

Your excel file would be something like this:

Sales Rep ID | Commission Rate
12345 | 0.20 |
67890 | 1.00 |

Then your formula in the expression could simply multiply by commission rate, without an IF statement.

[#3]

Wes

Thanks I did not think about this. An inline table or Excel sheet. This would 'assign' the correct margins to the correct accounts.

I will post a solution once I develop one.

Thanks.

[#4]

Creating an inline table as a placeholder worked. Your above example worked.

Then I used the same local variable as mentioned above. Then added to the total Margin.

=(sum(extended_price-(commission_cost*qty_shipped)))-(sum(IF(match(salesrep_id, 3209...,),
(extended_price-(commission_cost*qty_shipped))/2)))+sum(kenSpiltMargin*commission)

Now I need to figure out how not to add the total for the split account... but this is a separate issue....

Thanks for your help Wes.

[#5]

Just as a side note I find Stefan WALTHER nested If Generator a big help when trying to create complex IF statements,

http://www.qlikblog.at/tools/Nested-If-Generator.html

Mind you could be that I am just lazy !!

Gav.