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

    Calculate Bonus for each Employee by Week

    Jeremy Dempsey

      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.

       

      Bonus Potential screenshot 2.png

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

          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
            Ankit Bansal

            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
              Jeremy Dempsey

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