3 Replies Latest reply on Jul 24, 2018 7:22 PM by Jeremy Dempsey

# Calculate Bonus for each Employee by Week

I'm trying to find the bonus potential for each employee's weekly revenue.  I created this calculated field called Bonus Calculation:

IF SUM([ACTUAL REVENUE]) < 6500 THEN 0

ELSE IF SUM([ACTUAL REVENUE])   >= 6500 and SUM([ACTUAL REVENUE])   <= 11499 THEN .005 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 11499 or SUM([ACTUAL REVENUE])   <= 17499 THEN .01 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 17499 or SUM([ACTUAL REVENUE])   <= 19999 THEN .015 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 19999  THEN .02 * SUM([ACTUAL REVENUE])   END END END END END

However, the results are not coming out as expected.  For instance, the employee I highlighted below has \$27,599.48 in revenue for the week of May 6th, which would exceed the last bonus tier,

and should calculate as \$27,599.48 * .02 = \$551.99

What am I doing wrong?  I've tried to do this as a LOD, but I couldn't get it to work.

• ###### 1. Re: Calculate Bonus for each Employee by Week

IF SUM([ACTUAL REVENUE]) < 6500 THEN 0

ELSE IF

SUM([ACTUAL REVENUE])   >= 6500 and SUM([ACTUAL REVENUE])   <= 11499 THEN .005 * SUM([ACTUAL REVENUE])

ELSE IF

SUM([ACTUAL REVENUE])   > 11499 or SUM([ACTUAL REVENUE])   <= 17499 THEN .01 * SUM([ACTUAL REVENUE])

ELSE IF

SUM([ACTUAL REVENUE])   > 17499 or SUM([ACTUAL REVENUE])   <= 19999 THEN .015 * SUM([ACTUAL REVENUE])

ELSE

.02 * SUM([ACTUAL REVENUE])

END

Try

• ###### 2. Re: Calculate Bonus for each Employee by Week

Hey Jeremy,

You should use AND instead of OR in your 2 if conditions

IF SUM([ACTUAL REVENUE]) < 6500 THEN 0

ELSE IF SUM([ACTUAL REVENUE])   >= 6500 and SUM([ACTUAL REVENUE])   <= 11499 THEN .005 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 11499 AND SUM([ACTUAL REVENUE])   <= 17499 THEN .01 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 17499 AND SUM([ACTUAL REVENUE])   <= 19999 THEN .015 * SUM([ACTUAL REVENUE])

ELSE IF SUM([ACTUAL REVENUE])   > 19999  THEN .02 * SUM([ACTUAL REVENUE])   END END END END END

because if you say OR then anything above 11499 will fall into 01* (3rd category )

Thanks,

Ankit Bansal

• ###### 3. Re: Calculate Bonus for each Employee by Week

Yep, that's exactly it.  I made a silly mistake.  Thanks Ankit.