Else If Custom Column issue

[Anonymous]

Hey all,

I am having an issue with my If statement in a custom Column.

The below is suppose to give a percentage based on the total spend, but instead it just gives the first percentage of .16 to everything no matter what their total spend is.

if([RadarService] = 2) then
if[PretaxCharges] < 5000 then .16
else if[PretaxCharges] > 5000 then .15
else if[PretaxCharges] > 10000 then .14
else if[PretaxCharges] > 20000 then .14
else if[PretaxCharges] > 30000 then .13
else if[PretaxCharges] > 40000 then .13
else if[PretaxCharges] > 60000 then .12
else if[PretaxCharges] > 80000 then .12
else if[PretaxCharges] > 100000 then "Quote Needed" else null else null)

Any reason why it's just ignoring the other else if statements?

[Anonymous]

Update: I've managed to solve my issue.

[Zubair_Muhammad · Accepted solution]

@Anonymous

You will need to reverse the order of your else IF Expressions.

Once an if expression condition is met, it stops executing the rest of IF expressions.

So if your PretaxCharges are more than 100,000 they are also more tham 5000 as well

So this expression is executed since it appears first

if[PretaxCharges] > 5000 then .15

[Anonymous]

Hey, thanks for your reply!

The issue is it's not the greater than step that's doing it, it's the very first one which is "If it's less than 5000" at the very top. Now I tried reversing it with no luck, everything is still getting set to .16 

[Zubair_Muhammad]

@Anonymous

When i use some random data  with your formula... I dont get 0.16 every time.

[Anonymous]

Okay so I think I found the problem, I have summed PreTaxCharges because in the table it's 1000s of lines and each line has a different price for each client.

The reason I think I'm getting .16 for everything is that all charges are actually less than 5000 when they are not summed and I think my custom column is looking at the user's individual line item price rather than the sum?

[Anonymous]

Yea I am confused on where the issue is here.

The top one should be .16 as it's below 5k but the others shouldn't be. I don't see what it's looking at, as it should just compare that spend with the if statements