Dates Outside Ranges Defined by Custom Fiscal Year

If your query includes a date that falls outside of a range defined by an inherited fiscal year, SAQL does not return data for that date.

If a date falls outside of a range defined by an inherited custom fiscal year from Salesforce, then SAQL returns null for that date. When grouping by a date field that includes dates outside a range defined by an inherited custom fiscal year, no group is returned for undefined dates. If you group data based on non-fiscal periods, dates that aren’t included in a custom fiscal year return data as expected.

Example

If your fiscal year ends in March 2021, and a date field, CreatedDate, is in April 2021, grouping by CreatedDate_Month_Fiscal returns null or no group for April 2021. Grouping by CreatedDate_Month returns data as expected.

Consider this example dataset.

Opportunity Name	Created Date	Amount
Widgets	2/1/2017	100
Widgets	2/1/2018	100
Widgets	2/1/2019	100
Widgets	2/1/2020	100
Widgets	2/1/2021	100
Widgets	2/1/2022	100
Widgets	2/1/2023	100

In Salesforce, you have custom fiscal years defined as January 1 to December 31 for each year from 2018 through 2022. Inherit them in Analytics by using the Start Date setting.

When running a query like this:

1q = load "opportunities";
2q = foreach q generate 'CreatedDate' as 'Created Date', CreatedDate_Year_Fiscal as 'Fiscal Year';
3q = limit q 2000;

SAQL returns these results:

Created Date	Fiscal Year
2/1/2017	-
2/1/2018	2018
2/1/2019	2019
2/1/2020	2020
2/1/2021	2021
2/1/2022	2022
2/1/2023	-

Because a custom fiscal year definition doesn’t include 2/1/2017 or 2/1/2023, SAQL returns null.

Now, let’s group the dataset.

1q = load "opportunities";
2q = group q by 'CreatedDate_Year_Fiscal';
3q = foreach q generate 'CreatedDate_Year_Fiscal' as 'Fiscal Year', count() as 'Count';
4q = order q by 'CreatedDate_Year_Fiscal';
5q = limit q 2000;

SAQL returns these results:

Fiscal Year	Count
2018	1
2019	1
2020	1
2021	1
2022	1

Since the custom fiscal year definition doesn’t include 2/1/2017 or 2/1/2023, the query excludes these dates from the results.