How do SOQL date literals behave with fiscal years that differ from one year to the next

Question

Let's say that in 2013 and 2014 a business used trimesters, and then in 2015 it switched to quarters. How will queries that use the LAST N FISCAL QUARTERS literal work?

SELECT Id FROM Account WHERE CreatedDate = LAST_N_FISCAL_QUARTERS:8

Can I assume that this query, running during the first fiscal quarter of 2016, will return Accounts created during 2015 (4 fiscal quarters), 2014 (3 fiscal "quarters") and the last trimester of of 2013 (1 fiscal "quarter")?

Or will SOQL operate based on my "current" fiscal quarter settings? The documentation makes it sound like it'll be smart and return the results described above, but has anyone tried this?

Answer 1

Behavior

Here is my interpretation based on the evidence I have gathered (detailed below):

The LAST_N_FISCAL_QUARTERS Date 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:1 will 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?