Apex Code Development You need to sign in to do that
Don't have an account?
BenjK SOQL: FISCAL_YEAR() function returns wrong fiscal year!
Hi,
I'm seeing a consistent and baffling problem with the calculations of FY (across several different organizations). Has anyone else seen this, can you explain the behavior, or propose a work-around?
The problem is that FISCAL_YEAR() returns a value off by one.
To reproduce the problem:
- Double-check your FY settings. Let's say Sept-Aug for simplicity, but this holds across FY. FY is named by Ending Month.
- With these settings, today must be in FY 2012 - we are between Sept 2011 and August 2012, which is FY 2012.
- Create a new closed opportunity, with a close date of today. Logic compels that the opportunity must be in the current fiscal year, which per (2) is 2012.
- Note the Opportunity ID. Open up the Developer Console, paste the following code, and change the opp ID accordingly:
AggregateResult[] ar = [SELECT FISCAL_YEAR(CloseDate) fy FROM Opportunity
WHERE Id = '006A000000GjODf' GROUP BY FISCAL_YEAR(CloseDate)];
System.debug('FISCAL YEAR IS: ');
System.debug(ar.get(0).get('fy'));
- Check the output - it displays a FY of 2011 instead of 2012.
- I'm seeing this consistent pattern: FISCAL_YEAR() is returning one fiscal year too low for all opportunities.
Has anyone else seen this, can you explain the behavior, or propose a work-around?
Based on my tests, the SOQL FISCAL_YEAR function seems to be ignoring the 'Fiscal Year is Based On The starting/ending month' part of the Org FY settings. i.e. no matter how you setup the FY Name, the FISCAL_YEAR function always returnd based on the calendar year.
So for e.g. if you setup your Fiscal Year to start in April (no matter whether you pick the starting month or ending month as your naming convention), and your Opportunity closes between 4/1/2012 - 12/31/2012, FISCAL_YEAR will always return 2012. If the Opportunity closes between 1/1/2012-3/31/2012, FISCAL_YEAR will always return 2011.
I agree that this sounds like a bug. FISCAL_YEAR should be taking the naming convention (i.e. 'Fiscal Year is Based On The starting/ending month' ) into account. Can you open a case with Salesforce Support and follow-up with them. In the meantime, you might have to manually compute the correct fiscal year using something like this - http://boards.developerforce.com/t5/Apex-Code-Development/How-to-get-current-fiscal-year/m-p/243811/highlight/true#M42680.
Hope this helps.
p.s. Thanks for attending the Apex #codetalklive episode! (which is where I first saw this question)
Thanks for looking into this! I really appreciate it.
I actually tried to file the ticket as a bug w/ salesforce support (even before the codeshare) but they rejected it on the basis that Apex and SOQL-related support are only available to premier customers, which I'm not. Seems like a silly reason to reject a case, but I feel like I have limited options. What do you suggest?
Thanks again
I checked internally, and an internal Case has in fact already been created to track this issue. I'll update this thread when I hear any updates about this Case.
Thanks for keeping an eye on this! I filed a new case last Friday (#07490747); no response yet, but I'll keep this thread updated.
We'll take a look.