5 Replies Latest reply on Jan 4, 2019 1:35 PM by Paul Wachtler

    Compare a sum by quarter to a different sum by year

    Ad Bd

      Hello everybody,

       

      I'm having trouble with our bonus scheme, we're calculating our bonus every quarter. Then, at the end of the year, for people who missed a quarter but were very successful on another we're calculating the bonus on a year perspective and we give them the higher bonus.

      As you can see below, Mister Blue & Orange will get more money thanks to the annual bonus despite of Mister Green,Red and White.

       

      I need to create a calculate field which compare these bonus to give me the right bonus amount for each people. I'm not even able to sum the bonus by quarter without displaying quarters... I was able with an LOD calculation to have the sheet below but not to do in the same calculate field what I really want.

       

       

       

      On the workbook attached you'll find only 5 people but in reality I need this calculation for hundreds of people...

      Can you help me please ?

       

      Thanks in advance for your help,

      Adrien

        • 1. Re: Compare a sum by quarter to a different sum by year
          Paul Wachtler

          Hi Ad,

           

          Thanks for including the workbook but I'm a little unclear on what you want the outcome to be.  I'm not sure what you mean by this: "I need to create a calculate field which compare these bonus to give me the right bonus amount for each people."

           

          On your LOD page, if you remove the quarters, that calculation still works.  How are the annual bonuses calculated?  The Red customer has more in Q4 than Orange has for the year so I don't understand why Orange's annual bonus is higher.

           

          Can you create a mockup (screenshot from powerpoint or something) of what you want the outcome of this to look like?  Also please explain how the annual bonuses are calculated.

           

          Best,

          Paul

          • 2. Re: Compare a sum by quarter to a different sum by year
            Ad Bd

            Hello Paul,

             

            Thanks for your answer !

             

            The bonus rules are the same for quarter and annual bonus but on a different time perspective :

             

            Quarter bonus calculation :

            0% of your TO if TO below your target (Real rate field) on a quarter perspective

            50% of your TO if TO between 100% and 130% of your target (Real rate field) on a quarter perspective

            100% of your TO if TO above 130% of your target (Real rate field) on a quarter perspective

             

             

            Annual bonus calculation :

            0% of your TO if TO below your target (Real rate field) on a year perspective

            50% of your TO if TO between 100% and 130% of your target (Real rate field) on a year perspective

            100% of your TO if TO above 130% of your target (Real rate field) on a year perspective

             

            On my workbook "Real rate" is the calculate field which determine the % of achievement of each people (sum of the TO divided by the sum of target).

             

            To answer about the red customer, he has made an exceptional Q4 at 141% (see below) of his quarter target giving him a (super) bonus of 100% of his TO. Mister Orange made a year at 101% but his better quarter was (only) at 119% which is not enough to get a 100% bonus of his quarter TO.

             

             

            At the end I need to have below table :

             

             

             

            Best,

            Adrien

             

             

            • 3. Re: Compare a sum by quarter to a different sum by year
              Paul Wachtler

              Thanks for the detailed description Adrien.  That helped a lot.  I was able to figure it out by creating a few of calculated fields.

               

              The first I called "Total Bonus Quarter" which is calculating the Grand Total of your bonus quarter values, per customer, without needing to have the quarter on the viz.  Make sure to aggregate this as a sum when you put it on your viz.

              {FIXED [Customer], DATETRUNC('quarter',[Mois]):[bonus quarter]}

               

              I then created a field called Final Bonus - which is the first field on your final view:

              if [annual bonus] < sum([Total Bonus Quarter]) then sum([Total Bonus Quarter])

              elseif [annual bonus] = 0 then sum([Total Bonus Quarter])

              else [annual bonus]

              end

               

              Here is a view with all of the calculated numbers so far:

              Screen Shot 2019-01-03 at 2.50.49 PM.png

               

              Once I had all these, I created a separate worksheet for your final view called "End Result".  I created your "Thanks to annual bonus" field like this:

              ([Final Bonus] - sum([Total Bonus Quarter]))

               

              On the final view, I formatted the "Thanks to annual bonus" field so that positive numbers have a plus sign before them.  Here's the outcome:

              Screen Shot 2019-01-03 at 2.54.57 PM.png

               

              All of this is included in the attached workbook.

               

              Best,

              Paul

              • 4. Re: Compare a sum by quarter to a different sum by year
                Ad Bd

                Paul,

                 

                That's just awesome !!! Thanks a lot you've answered perfectly to my problem

                 

                Best,

                Adrien

                • 5. Re: Compare a sum by quarter to a different sum by year
                  Paul Wachtler

                  You're welcome Adrien!  Happy to help