Apex Code Development You need to sign in to do that
Don't have an account?
Funny Fiscal Formula
I could use the help of a report guru here. In our Force.com package we have a simple field that allows us to compare sales data from prior years with a "this year to date vs last year to date". Essentially we don't count any volume data for a month prior to this month. Seems easy enough.
IF(MONTH( DATEVALUE(gvn__Order_Date__c) ) <= (MONTH(TODAY()) + 12), gvn__Extended_Price__c , 0)
Now we have a customer whose Fiscal year starts on July 1st. This means that data for December should count in April but data in April shouldn't count in December. Does that make sense? I thought it would be as easy as adding 12 to the month or something but that fails pretty quickly.
How is your fiscal year set up in Salesforce? I am not certain, but I believe it makes a difference. Our company is a July 1- June 30 FY and I will have to double check but I believe these types of formulas work for us.
We have it starting July 1 but this formula does not account for the company fiscal year at all, it is simply comparing 4 vs 10 (month of the year) and if one is larger than the other or equal, it counts the sales.
Ah I was under the impression that if your FY is July-June, then July = 1 and June = 12, therefore December is 6 and April is 10.
I know that this is supported by SOQL. http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_date_functions.htm
EDIT: Note that if your company is set to Custom Fiscal Years this doesnt work. If its set to Standard Fiscal Year but starts in July, it should work.
How, in a formula, can I get a July date to show as Month 1? That would definitely solve my problem! You need a function like FISCALMONTH(Date__c)
Sorry Jim... I guess what I said only works with SOQL and not with formulas... I will be interested to see if anyone can figure this out.
It would be a pain but could you use a CASE function to establish July = 1, ... June = 12?