function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AsymtekAsymtek 

Calculating a Rolling Average

I need help in creating a formula that maintains a rolling 3 month and 6 month average qty of products sold.  I want to display these two results in a column at the end of a matrix report.

 

 

Columns:       Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 3MonAve 6MonAve

 

Rows:

Product A       

Product B

Product C

 

Has anyone done this?

JKinseyJKinsey

From my experience you can do this with the "PREVGROUPVAL" and "PARENTGROUPVAL" functions with Custom Summary Formulas.  My example code is below were I used it to calculate a 3 month rolling average for Average Booking Lead Time across a bucketed field.  "BucketField_27501594" is my Product in your case.

 

If  (NOT(Isnull(PREVGROUPVAL(Opportunity.Booking_Lead_Time__c:AVG, Opportunity.Date_of_RFS__c,2))) , (PREVGROUPVAL(Opportunity.Booking_Lead_Time__c:AVG, Opportunity.Date_of_RFS__c,2) + PREVGROUPVAL(Opportunity.Booking_Lead_Time__c:AVG, Opportunity.Date_of_RFS__c,1) + PARENTGROUPVAL(Opportunity.Booking_Lead_Time__c:AVG, BucketField_27501594, Opportunity.Date_of_RFS__c)) / 3, PARENTGROUPVAL(Opportunity.Concession_2_Rate__c:AVG, BucketField_95927130, Opportunity.QuotedDate__c)

)

 

Hope this help, it basically adds up the previous 2 months values and current and divides by 3 to display on that current month.