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
Michael Kolodner 13Michael Kolodner 13 

SOQL query for "two fiscal years ago"?

I need to write a soql query for  DLRS that will return records with a date in the year prior to "last fiscal year." Is there soql syntax that will return "2 fiscal years ago"? 

My current query is 
Select ID from School_Report_Card__c WHERE School_Year_of_This_Report_Card__c = Last_Fiscal_year

But I need the year before that. Is this possible?
Best Answer chosen by Michael Kolodner 13
Prafull G.Prafull G.
Try using LAST_N_FISCAL_​YEARS:n
Check documenations Date Formats and Date Literals (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm) for details.

All Answers

Prafull G.Prafull G.
Try using LAST_N_FISCAL_​YEARS:n
Check documenations Date Formats and Date Literals (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm) for details.
This was selected as the best answer
Michael Kolodner 13Michael Kolodner 13
But Last_2_Fiscal_Years, for example, would get two years worth of data. It sounds like there is no alternative to having two filters: 
Select ID from School_Report_Card__c WHERE School_Year_of_This_Report_Card__c = Last_2_Fiscal_Years AND School_Year_of_This_Report_Card__c != Last_Fiscal_Year
?
Prafull G.Prafull G.
Hi Michael,

No, the last fiscal year literal works from the starting and ending of given year number. For example -
If your company fiscal years are defined from Jan 1 to Dec 31 then
LAST_N_FISCAL_YEAR:1 will return all records for current year 2019 [from Jan 1, 2019 to Dec 31 2019]
LAST_N_FISCAL_YEAR:2 will return all records for current year 2019 [from Jan 1, 2018 to Dec 31 2018]
  
Select ID from School_Report_Card__c WHERE School_Year_of_This_Report_Card__c = Last_N_Fiscal_Years:2
Above soql should return all Report Card records where report card is 2018 FY. Try running this query and see if you get the right results.

Let me know if this helps.
 
Michael Kolodner 13Michael Kolodner 13
Ah! I didn't quite catch the ":n" part--now I get it. Thank you!!!
Prafull G.Prafull G.
Perfect! I am gald it helped. :)
Michael Kolodner 13Michael Kolodner 13
No, wait--it's adding together a range of FYs, not just pulling N back. 
Here's a report showing them by year:
User-added image

Here's the query result:
User-added image
Prafull G.Prafull G.
Hi Michael,

Can you check Fiscal year settings in your org? Here is the description from SF documentation-

LAST_N_FISCAL_​YEARS:n
Starts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
Michael Kolodner 13Michael Kolodner 13
You're right--it was set wrong. Fixed now and all's working. Also. I learned that N_Fiscal_Years_Ago can also take the colon and a number.