Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Help With Complex "If ElseIF ElseIF" Statement

Hi All,

I need some help with this as its not as easy as I first thought it might be.

The scenario is that our customers will receive a rebate on there spend as long as they spend more than there agreed threshold.

An example of this: If customer 1001 spends more than £100,000 then they will receive 2% rebate of there spend, however if they spend more than £200,000 then they receive %3 rebate.

What I Want to Achieve

We want to be able to show automatically the percentage of rebate that each customer will receive for each invoice and then calculate the rebate amount for each invoice.

Problems

We have a maximum of 4 tiers of rebate however not all customers have 4 tiers, some have 1 flat rate, some have 2 tiers some 3 and some all 4.

The percentage and threshold is different for some  customers but not all e.g. customer 1001 has a different threshold and percentage rate to customer 1006

What I Have In Mind

To resolve the problems above I figured it would be best to map in an excel sheet (Test Attached) that contains columns of:

The Customer Number (KEY), Threshold 1 , %Tier 1 , Threshold 2, %Tier 2, Threshold 3, %Tier 3 ,Threshold 4, %Tier 4

I need help figuring out how to test the customer spend against each threshold however break if the next threshold is NULL.

<pseudocode>

var = 0

IF (customer spend < threshold 1)THEN(Break)

ELSE (

IF (customer spend > threshold 1 & < threshold 2) THEN (SET var=%Tier1)

ELSEIF ( IF(%Tier2 = NULL) THEN (BREAK) ELSE (customer spend > threshold 2 & < threshold 3) THEN (SET var=%Tier2))

ELSEIF ( IF(%Tier3 = NULL) THEN (BREAK) ELSE (customer spend > threshold 3 & < threshold 4) THEN (SET var=%Tier3))

ELSE ( IF(%Tier4 = NULL) THEN (BREAK) ELSE (SET var=%Tier4))

)

<pseudocode>

Thanks

Aron

Message was edited by: Aron Williamson Changed psuedo Script

1 Solution

Accepted Solutions

I haven't test data, I assume this. And please set variables in script or variable overview where you declare the 4 statements and then use this

IF ([customer spend] < [threshold 1],'Break',

IF ([customer spend] > [threshold 1] and [chained spend] < [threshold 2], \$(%Tier1), NULL(),

IF([customer spend] > [threshold 2]  and [customer spend] < [threshold 3]), \$(%Tier2), NULL(),

IF([customer spend] > [threshold 3]  and [customer spend] < [threshold 4], \$(%Tier3), NULL()), \$(%Tier4))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
3 Replies

I haven't test data, I assume this. And please set variables in script or variable overview where you declare the 4 statements and then use this

IF ([customer spend] < [threshold 1],'Break',

IF ([customer spend] > [threshold 1] and [chained spend] < [threshold 2], \$(%Tier1), NULL(),

IF([customer spend] > [threshold 2]  and [customer spend] < [threshold 3]), \$(%Tier2), NULL(),

IF([customer spend] > [threshold 3]  and [customer spend] < [threshold 4], \$(%Tier3), NULL()), \$(%Tier4))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

Thank you Anil, that was the exact answer! I think when I was asking the question I was overthinking it but you hit the nail right on the head. My exact code looked like the below in the end FYI for anyone this may help.

if(isNull(YArrangment.Threshold2) OR \$(CollectiveSPNRev) < YArrangment.Threshold2 , YArrangment.Rebate1 , if(isNull(YArrangment.Threshold3) OR \$(CollectiveSPNRev) < YArrangment.Threshold3 , YArrangment.Rebate2, if(isNull(YArrangment.Threshold4) OR \$(CollectiveSPNRev) < YArrangment.Threshold4 , YArrangment.Rebate3, if(IsNull(YArrangment.Rebate4), YArrangment.Rebate3, YArrangment.Rebate4))))

Don't think much. Anyway, you deserve !!!

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Community Browser