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
Jordan FrankJordan Frank 

Y.T.D. Variance Report - Custom Formula

Hey guys, I am looking to create an executive summary that shows our monthly margin contribution - quota to give me a variance. Currently I have a matrix report, the columns are the months while the row is a group I have to use because of complicated margin capture methods. Essentially the top column will read: January | February | March etc.. Within these columns I want to have Total Amount (For that month), Total Margin Contribution (For that month), Quota (A pre-set amount that doesn't change by month), Y.T.D. Variance. Y.T.D. Variance would equal that months margin - quota. The next month would equal that months margin - quota + PREVIOUS MONTH. This is the hard part. Obviously January is easy to do, but then February is going to simply take Feb. Margin - Quota. I am not sure how to add in the previous month's variance in order to keep a running total. Any ideas? 
Best Answer chosen by Jordan Frank
Parker EdelmannParker Edelmann
Currently, what you're formula is set to do is to take a month's variance, and add it to last month's variance. So when you get to December the result will be December Margin - Quota + November Margin - Quota. One possibility, but a lengthy solution is first create a formula that takes the month number so that January = 1, February =2 and so on. With that, this should do the trick, providing we don't get a compile size error because of all the field references. 
Opportunity.Total_Margin_Contribution__c:SUM - Quota + 
IF(Month_No__c > 1, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,1)- Quota,0)+
IF(Month_No__c > 2, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,2)- Quota,0)+
​IF(Month_No__c > 3, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,3)- Quota,0)+
​IF(Month_No__c > 4, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,4)- Quota,0)+
​IF(Month_No__c > 5, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,5)- Quota,0)+
​IF(Month_No__c > 6, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,6)- Quota,0)+
​IF(Month_No__c > 7, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,7)- Quota,0)+
​IF(Month_No__c > 8, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,8)- Quota,0)+
​IF(Month_No__c > 9, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,9)- Quota,0)+
​IF(Month_No__c > 10, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,10)- Quota,0)+
​IF(Month_No__c > 11, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,11)- Quota,0)

All Answers

Parker EdelmannParker Edelmann
Have you looked at the PREVGROUPVAL function? Help and training says this:

PREVGROUPVAL
Description:This function returns the value of a specified previous grouping. A “previous” grouping is one that comes before the current grouping in the report. Choose the grouping level and increment. The increment is the number of columns or rows before the current summary. The default is 1; the maximum is 12. You can only use this function in custom summary formulas for reports.

Use:PREVGROUPVAL(summary_field, grouping_level [, increment])
Where summary_field is the name of the grouped row or column, grouping_level is the summary level, and increment is the number of rows or columns previous.

Example:AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
This formula calculates, for each month, the difference in amount from the previous month shown in the report. In this example, the report is an opportunity matrix with columns grouped by Close Date and rows by Stage.

Source: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US
Jordan FrankJordan Frank
So this is a great start but I am still struggling. 

I am able to correctly sum the first two months variance but then the 3rd month is calculated incorrectly. Here is my formula. 

Opportunity.Total_Margin_Contribution__c:SUM-500000+(PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c)-500000)

** 500000 is the quota I am using in this example. Here is what I think it is doing. 
 
  1. January Margin - Quota (500000) 
  2. February Margin - Quota + (January Margin - Quota) ** Correct
  3. March Margin - Quota + (January Margin - Quota) ** This is the error. 
In March I need it to take February Y.T.D. Variance + (March Margin - Quota) or 
January Margin - Quota + February Margin - Quota + March Margin - Quota.... 

Any ideas? 
Parker EdelmannParker Edelmann
Currently, what you're formula is set to do is to take a month's variance, and add it to last month's variance. So when you get to December the result will be December Margin - Quota + November Margin - Quota. One possibility, but a lengthy solution is first create a formula that takes the month number so that January = 1, February =2 and so on. With that, this should do the trick, providing we don't get a compile size error because of all the field references. 
Opportunity.Total_Margin_Contribution__c:SUM - Quota + 
IF(Month_No__c > 1, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,1)- Quota,0)+
IF(Month_No__c > 2, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,2)- Quota,0)+
​IF(Month_No__c > 3, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,3)- Quota,0)+
​IF(Month_No__c > 4, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,4)- Quota,0)+
​IF(Month_No__c > 5, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,5)- Quota,0)+
​IF(Month_No__c > 6, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,6)- Quota,0)+
​IF(Month_No__c > 7, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,7)- Quota,0)+
​IF(Month_No__c > 8, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,8)- Quota,0)+
​IF(Month_No__c > 9, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,9)- Quota,0)+
​IF(Month_No__c > 10, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,10)- Quota,0)+
​IF(Month_No__c > 11, PREVGROUPVAL(Opportunity.Total_Margin_Contribution__c:SUM,Opportunity.Month_Book_Date__c,11)- Quota,0)
This was selected as the best answer
Jordan FrankJordan Frank
While this works to calculat the most recent month's variance, I am unable to see the progression. I would like to see something like the following:



Book Date: FY 2016
Jordan FrankJordan Frank
While this works to calculat the most recent month's variance, I am unable to see the progression. I would like to see something like the following:



Book Date: FY 2016 - Total Amount - Total Margin Contribution - Monthly Quota - Y.T.D. Variance

January 2016          - X Amount       -  Y Amount                           - Z Amount         -      Y-Z = J Var.
February 2016        - XF Amount      - YF Amount                         - ZF Amount       -       YF - ZF + J Var.
ETC...


This formula will result in   
Y.T.D. Variance
Jan - Blank
Feb - Blank
Mar - Blank
Apr - Blank
Dec - Total Variance.
                           
Parker EdelmannParker Edelmann
It should give you the progression you asked for. Correct me if I'm wrong, but on Januarys column, it gives February's difference from the quota, plus all months prior, and for April's column, it gives April's difference from the quota, plus the differences from all months prior, with the pattern continuing for all months. If these aren't the fields you wanted to be used you can always add and remove as need be, but the progression from month to month is still there if you look at the particulars of the formula. Can you outline a different procedure for me?
Jordan FrankJordan Frank
It doesn't show the progession but I was able to modify it to show the total year to date and then add a separate column that shows each months deficit/surplus. This works, thanks for the help!