Note: This release is in preview. Features described here don’t become generally available until the latest general availability date that Salesforce announces for this release. Before then, and where features are noted as beta, pilot, or developer preview, we can’t guarantee general availability within any particular time frame or at all. Make your purchase decisions only on the basis of generally available products and features.

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.

Analytics SAQL Developer Guide

Dates Outside Ranges Defined by Custom Fiscal Year

Example