
1. Re: Compare a sum by quarter to a different sum by year
Paul Wachtler Jan 3, 2019 8:02 AM (in response to Ad Bd)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 Jan 3, 2019 8:32 AM (in response to Paul Wachtler)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 Jan 3, 2019 11:57 AM (in response to Ad Bd)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:
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:
All of this is included in the attached workbook.
Best,
Paul

forum_v10.4.twbx 370.1 KB


4. Re: Compare a sum by quarter to a different sum by year
Ad Bd Jan 4, 2019 6:26 AM (in response to Paul Wachtler)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 Jan 4, 2019 1:35 PM (in response to Ad Bd)You're welcome Adrien! Happy to help