function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
SeaTecSeaTec 

Custom Summary Formulas

I recently inherited the administration of SalesForce so bear wiht me please.

We recently change the standard Fiscal year to go from Feb 1 to Jan 31.  Previoulsy it was from jan to Dec.

We have a Custom Summary Formulas  that calculates YTD % for the sales reps.  This is the formula.

Opportunity.Order_Sale_Price__c:SUM / User.YTD_Sales_Quota__c:SUM   The format is percent and decimals are 2. 

This of course is wrong now since the calculation assumes January being included in the equation. Therefore all quoats are off by 50% as of right now which will increase as the year goes on since its still including January.

 

How could i change this formula to only include  the months that belong in this current Fiscal year?

 

THank you

Best Answer chosen by Admin (Salesforce Developers) 
James (CloudAnswers)James (CloudAnswers)

It is hard to say without seeing how your environment is setup, but you can filter date field based on "this year".  Check out this blog posting about the fields you can use.

 

http://sfdc.arrowpointe.com/2008/03/07/opportunity-roll-up-summary-recommendations/

All Answers

James (CloudAnswers)James (CloudAnswers)

Hi SeaTec,

 

You may want to be a bit more experienced if you are going to be making major changes to your Salesforce.com instance - since there may be other reports, formulas, etc. depending on this.  That being said, what you want to do is update your Organization's Fiscal Year.  Click "Setup" > "Administration Setup" > "Company Profile" > "Fiscal Year".

 

There you can update the fiscal year offset.  You can still do reporting with calendar year (jan-dec) and you should have other fields (ie. fiscal quarter) for feb-jan reporting.

SeaTecSeaTec
The fiscal year has already been changed.  Sorry i didnt make this clear enough.
SeaTecSeaTec

So  basically the problem is that the User.YTD_Sales_Quota__c:SUM  isnt calculated right in reference to the Fiscal year setting.  The user  shows that his quota is Jan + Feb  while it should only be FEB because i changed the standard Fiscal year to start on Feb.

 

How can i have SF start calculating the YTD  quota of each sales rep  starting on FEB?

 

Thank you

Message Edited by SeaTec on 03-03-2009 11:56 AM
James (CloudAnswers)James (CloudAnswers)

If your report shows only this year (I'm guessing since you only have 1 quota field on User), set the Time Frame (top right of report) to:

Interval: "Close Date"

Duration:  "Current Fiscal Year"

 

That should filter out the Opportunities that are outside of this fiscal year.

SeaTecSeaTec

Ill try that

Message Edited by SeaTec on 03-05-2009 05:05 AM
SeaTecSeaTec

that didnt work because the custom summary formula is still baseing its calculation on the User.YTD_Sales_Quota__c:SUM  value which is internally calcluated and still includes  the January quota entry.  The only way this calculation works is if i null the january value in the users quota settings but that will only work till the end of next year january and the final calculation will be off because next years Cal Year month january  (the last month of this FY)  wont be having a value to be included into the quota calculation.

Message Edited by SeaTec on 03-05-2009 05:30 AM
James (CloudAnswers)James (CloudAnswers)
Is this a custom report type that allows reporting from Opportunity > User > YTD_Sales_Quota__c ?
Perhaps you could add a filter for YTD_Sales_Quota to only include this year's values in addition to the time filter on the opportunities returned?
SeaTecSeaTec
What would probbaly work is if i could enhance the formula to deduct the 01 january quota value from the User.YTD_Sales_Quota__c:SUM  if the current year value  = FY  value and not deduct the value if the CY value is not equal to FY  value.  make sense?  i just dont know how to write that.
James (CloudAnswers)James (CloudAnswers)

It is hard to say without seeing how your environment is setup, but you can filter date field based on "this year".  Check out this blog posting about the fields you can use.

 

http://sfdc.arrowpointe.com/2008/03/07/opportunity-roll-up-summary-recommendations/

This was selected as the best answer