Find the difference in forecast and revenue in first quarter and distribute the difference amount to rest of the quarter's forecast
For eg: forecast for Q1,2,3,4 is 1M each and sales bring up revenue of 800k in first quarter then the difference 200k should be distributed to rest of the quarter's for cast so Q2,3,4 forcast will be 1.066 M each
Here is what i have built (logic):
(SUM(CASE when QUARTER(`revenueDateEST`)<=QUARTER(CURDATE()) AND YEAR(`revenueDateEST`)=YEAR(CURDATE()) THEN `DisplayBase Quota` ELSE 0 END)
- SUM(CASE when QUARTER(`revenueDateEST`)<=QUARTER(CURDATE()) AND YEAR(`revenueDateEST`)=YEAR(CURDATE()) THEN (CASE when `Product`='DisplayBase' then `Revenue`else 0 end) ELSE 0 END))
/ (CASE WHEN (4-QUARTER(CURDATE()))>0 THEN (4-QUARTER(CURDATE())) ELSE 1 END)
+sum((CASE when QUARTER(`revenueDateEST`)=QUARTER(CURDATE())and YEAR(`revenueDateEST`)=YEAR(CURDATE()) then `Quota`else 0 end))
But, this is not working as expected, could anyone help with the code for the above, thanks in advance.