The current documentation for SOQL Date Literals does not have N_X_AGO on its list. In fact I had never seen the AGO date literals on any version of the documentation. So I am wondering if there are more "hidden" date literals and which rock they are hiding under.
[SalesForce] Documentation for hidden SOQL date literals (e.g. N_DAYS_AGO)
Related Solutions
Wow, that's an interesting one! I doubt it's allowed though (at least without having to use Dynamic SOQL).
You can always do this instead:
Date d = System.today() - 7;
List<Opportunity> opportunities = [SELECT OwnerId, Amount, Probability FROM OPPORTUNITY
where Amount > 0 and LastModifiedDate < :d];
Behavior
Here is my interpretation based on the evidence I have gathered (detailed below):
The
LAST_N_FISCAL_QUARTERSDate Literal does respect Custom Fiscal Quarter definitions. Quarters that come before your first defined Custom Fiscal Year are each treated as a Standard Fiscal Quarter. Quarters that come after your last defined Custom Fiscal Year, however, are ignored entirely. So if your last defined Fiscal Year is in the past,LAST_N_FISCAL_QUARTERS:1will encompass the last defined Quarter.
In light of this finding, it would be prudent to make sure your Custom Fiscal Years are always defined into the future.
Data
I created MyObject__c records with the following Date__c values (expected Quarter in parentheses):
2012 (4)
2012-02-29, 2012-06-30, 2012-09-16, 2012-12-04
(2012-Q1) (2012-Q2) (2012-Q3) (2012-Q4)
2013 (6)
2013-01-03, 2013-05-23, 2013-08-06, 2013-10-01, 2013-12-30, 2013-12-30
(2013-T1) (2013-T2) (2013-T2) (2013-T3) (2013-T3) (2013-T3)
2014 (6)
2014-03-20, 2014-04-29, 2014-06-01, 2014-08-06, 2014-10-31, 2014-12-11
(2014-T1) (2014-T1) (2013-T2) (2013-T2) (2013-T3) (2013-T3)
2015 (3)
2015-02-26, 2015-05-05, 2015-08-02
(2015-Q1) (2015-Q2) (2015-Q3)
I would then query for the data as follows:
SELECT count() FROM MyObject__c WHERE Date__c = LAST_N_FISCAL_QUARTERS:12
Preliminary Investigation
First, I set up trimesters just for 2013 and 2014.*
Trimesters setup:
2013-T1 (2013-01-01 through 2013-04-30)
2013-T2 (2013-05-01 through 2013-08-31)
2013-T3 (2013-09-01 through 2013-12-31)
2014-T1 (2014-01-01 through 2014-04-30)
2014-T2 (2014-05-01 through 2014-08-31)
2014-T3 (2014-09-01 through 2014-12-31)
Records for each value of LAST_N_FISCAL_QUARTERS:
12 - 16 11 - 16 10 - 16
09 - 15 08 - 14 07 - 13
06 - 12 05 - 11 04 - 9
03 - 6 02 - 4 01 - 2
Which looks like it matches up with:
2011-Q3 - 0 2011-Q4 - 0
2012-Q1 - 1 2012-Q2 - 1 2012-Q3 - 1 2012-Q4 - 1
2013-T1 - 1 2013-T2 - 2 2013-T3 - 3
2014-T1 - 2 2014-T2 - 2 2014-T3 - 2
Sure seems like LAST_N_FISCAL_QUARTERS:1 matches up with 2014-T3.
Follow Up
After that I added 2015.
Quarters setup:
2015-Q1 (2015-01-01 through 2015-03-31)
2015-Q2 (2015-04-01 through 2015-06-30)
2015-Q3 (2015-07-01 through 2015-09-30)
2015-Q4 (2015-10-01 through 2015-12-31)
Records for each value of LAST_N_FISCAL_QUARTERS:
15 - 19 14 - 19 13 - 18
12 - 17 11 - 16 10 - 15
09 - 14 08 - 12 07 - 9
06 - 7 05 - 5 04 - 3
03 - 2 02 - 1 01 - 0
Which looks like it matches up with:
2011-Q4 - 0
2012-Q1 - 1 2012-Q2 - 1 2012-Q3 - 1 2012-Q4 - 1
2013-T1 - 1 2013-T2 - 2 2013-T3 - 3
2014-T1 - 2 2014-T2 - 2 2014-T3 - 2
2015-Q1 - 1 2015-Q2 - 1 2015-Q3 - 1 2015-Q4 - 0
And finally 2016.
Quarters setup:
2016-Q1 (2016-01-01 through 2016-03-31)
2016-Q2 (2016-04-01 through 2016-06-30)
2016-Q3 (2016-07-01 through 2016-09-30)
2016-Q4 (2016-10-01 through 2016-12-31)
Records for each value of LAST_N_FISCAL_QUARTERS:
18 - 19 17 - 19 16 - 19
15 - 18 14 - 17 13 - 16
12 - 15 11 - 14 10 - 12
09 - 9 08 - 7 07 - 5
06 - 3 05 - 2 04 - 1
03 - 0 02 - 0 01 - 0
Which looks like it matches up with:
2011-Q3 - 0 2011-Q4 - 0
2012-Q1 - 1 2012-Q2 - 1 2012-Q3 - 1 2012-Q4 - 1
2013-T1 - 1 2013-T2 - 2 2013-T3 - 3
2014-T1 - 2 2014-T2 - 2 2014-T3 - 2
2015-Q1 - 1 2015-Q2 - 1 2015-Q3 - 1 2015-Q4 - 0
2015-Q1 - 0 2015-Q2 - 0
Looks like THIS_FISCAL_QUARTER is one quarter after LAST_N_FISCAL_MONTHS:1, in case that wasn't obvious.
* This post prompted me to lay out in detail how to set up trimesters in the first place. How Do I Set Up Fiscal Trimesters, Anyway?
Best Answer
These are not the date literals you're looking for....... :-)
The trick is that the filters that are available for filter criteria in list views and reports are secretly available in SOQL as well. You'll find them by checking out this link below. Most of these are already on your list, but the missing ones are here as well, just replace spaces with underscores!
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm