Dates Outside Ranges Defined by Custom Fiscal Year

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.

Consider this example dataset.

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 'Created_Date' as 'Created Date', Created_Date_Year_Fiscal as 'Fiscal Year';
3q = limit q 2000;

SAQL returns these results:

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 'Created_Date_Year_Fiscal';
3q = foreach q generate 'Created Date_Year_Fiscal' as 'Fiscal Year', count() as 'Count';
4q = order q by 'Created_Date_Year_Fiscal';
5q = limit q 2000;

SAQL returns these results:

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