Business Intelligence and Salesforce Report Formulas: Quarter-to-Date Weekly Average

Do you ever get frustrated trying to coax a calculation out of Salesforce that you know should be possible, but feel like you can't quite get to without special business intelligence app add-ons, or worse, manually calculating in Excel?  Ever felt your hackles rise when someone describes Salesforce Reporting as more of a 'Dark Art' than what should be fairly straight forward math?

I would agree that Salesforce metrics and KPIs can feel more like Art than Science, but I will say that over the past few years, Salesforce has provided more and more tools to parse data and get to the metrics that are important to your business, if you know where to look, and what to use.  Such tools include:

•  Date Literals - Date expressions available in filters to call a select range of records (e.g., YESTERDAY, LAST 12 WEEKS, etc.)
• Report Bucketing - Categorize report records without creating a formula or a custom field, or modifying a picklist
• Summary Report Functions - PARENTGROUPVAL and PREVGROUPVAL to return the value of a report summary grouping one level higher, or one grouping previous for comparison calculation purposes
• Using Report Formulas to do additional calculation work when your field formula hits the dreaded "Compiled formula is too big to execute (5,001 characters).  Maximum size is 5,000 characters", and you need to push Salesforce harder (also see this Salesforce help doc: Tips for Reducing Formula Size)
• Embedded Analytics (New! in Winter'14 - Release Notes p.167) - embed report charts in record detail pages for any standard or custom object!!!  (Did I mention how excited I am about this functionality?)

Anyway, I was recently asked to calculate a Closed Won Opps Quarter-to-Date Weekly Average and Closed Won Total by Sales Rep for a client.  How are we going to do that?  Essentially we need the sum of amount plus the correct week of the quarter (we'll solve the problem of first six days in the quarter in a second).

Being the not-reinvent-the-wheel kind of guy I am, I quickly googled 'salesforce calculate week of the quarter formula.'  After digging for a few minutes, I found this discussion thread on developerforce.com asking for "help help writing formula to display Week number."  Bingo!  Not exactly what I wanted, but very close.  I read through the thread and found a bit of formula written by Super Contributor, Steve Mo (over 26,000 Answers and 1700 Best Answers!  Go Steve!), which calculates Week Number in the year based upon a date field (for my formula, I used TODAY() in place of Steve Mo's Testing_Date__c):

CEILING(((Testing_Date__c - DATE(YEAR(Testing_Date__c), 1, 1) + 1) +

MOD(DATE(YEAR(Testing_Date__c), 1, 1) - DATE(1900, 1, 7), 7)) / 7)

Okay, now that  we have the week of the year, we can use a Case formula to determine the week in the quarter:

1,AMOUNT:SUM/1,
2,AMOUNT:SUM/2,
3,AMOUNT:SUM/3,
4,AMOUNT:SUM/4,
5,AMOUNT:SUM/5,
6,AMOUNT:SUM/6,
7,AMOUNT:SUM/7,
8,AMOUNT:SUM/8,
9,AMOUNT:SUM/9,
10,AMOUNT:SUM/10,
11,AMOUNT:SUM/11,
12,AMOUNT:SUM/12,
13,AMOUNT:SUM/13,
14,AMOUNT:SUM/1,
15,AMOUNT:SUM/2,
16,AMOUNT:SUM/3,
17,AMOUNT:SUM/4,
18,AMOUNT:SUM/5,
19,AMOUNT:SUM/6,
20,AMOUNT:SUM/7,
21,AMOUNT:SUM/8,
22,AMOUNT:SUM/9,
23,AMOUNT:SUM/10,
24,AMOUNT:SUM/11,
25,AMOUNT:SUM/12,
26,AMOUNT:SUM/13,

and so on...

Finally, in order to account for the first six days of the quarter, IF MONTH(TODAY()) = 1,4,7,10 AND DAY(TODAY()) = 1,2,3,4,5,6, THEN AMOUNT:SUM, ELSE combine Steve Mo's week of the quarter plus my CASE addition equals Closed Won Average per week Quarter-to-Date.

Image: John Baumgart [Public domain, GFDL (http://www.gnu.org/copyleft/fdl.html) or CC-BY-SA-3.0 (http://creativecommons.org/licenses/by-sa/3.0)], via Wikimedia Commons