I've built a report showing "Rolling 3 Monthly Average Sales" by account by month. The formula i'm using to calculate the Rolling 3 month is the following:
(
Opportunity.Amount:SUM
+
PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate)
+
PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate,2)
)
/3
The problem i'm facing is that the first month when the closed opportunity occurs takes into account the preceding 2 months at $0.00, meaning the Rolling 3 Month Average is incorrect essentially for the first 2 months for each Account. See screenshot here
(https://www.dropbox.com/s/4bcmj18a8jc4cus/Screenshot%202019-02-22%2010.40.39.png?dl=0)
So... I thought why not replace the $0 by NULL which should fix the problem, right? Wrong. It fixes the problem above, but if there are $0 months after the account goes live then the Rolling 3 Month Average formula returns NULL values instead of using the $0 as part of the average.
(
IF(Opportunity.Amount:SUM=0,NULL,Opportunity.Amount:SUM)
+
IF(PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate)=0,null,PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate))
+
IF(PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate,2)=0,null,PREVGROUPVAL(Opportunity.Amount:SUM,Opportunity.CloseDate,2))
)
/3
See screenshot here:
https://www.dropbox.com/s/5ab2hu0ueiguvsr/Screenshot%202019-02-22%2010.50.43.png?dl=0
How do I fix this formula so that I get the best of both worlds, i.e. it ignores all the $0 up to the first opportunity and then keeps them in thereafter?
Any help is greatly appreciated! Thank you!
Shaun
So something kinda like this?
(Opportunity.Amount:SUM +
BLANKVALUE(PREVGROUPVAL(Opportunity.Amount:SUM, CLOSE_MONTH,1),0) +
BLANKVALUE(PREVGROUPVAL(Opportunity.Amount:SUM, CLOSE_MONTH,2),0))
/ 3
