Date Formats and Fiscal Dates for Source Data

You set the metadata attributes in the sfdcDigest transformation parameters for Salesforce data or in the metadata file for external CSV data.

Remember these tips when setting the metadata attributes for date columns:

• You can’t configure dates that are obtained from external data via connectors.
• Before loading dates from an external data file, confirm that the date format meets the requirements specified here.
• Confirm that the column names in the external data file don’t conflict with the generated date column names. For example, if you load a CSV with the column Create_Date, CRM Analytics generates the Create_Date_Year column in the dataset. If the CSV also has a column named Create_Date_Year, CRM Analytics throws an error.

Fiscal Periods in CRM Analytics

If the calendar and fiscal year differ, you can enable CRM Analytics to generate the fiscal date columns in the dataset, in addition to calendar date columns. To enable CRM Analytics to generate fiscal date columns, set the fiscalMonthOffset attribute to a value other than 0. Set this attribute for each date column that you want to generate fiscal date columns for. If you set the offset to 0 or you don’t specify a value, CRM Analytics doesn’t generate any fiscal date columns.

Note

• Check out Inherit Custom Fiscal Years from Salesforce to learn how CRM Analytics can apply your Salesforce org’s custom fiscal calendar settings automatically.
• Check out Data Prep Date Configuration to learn how you can define a fiscal year for only your Data Prep data in CRM Analytics.

Additionally, to configure the fiscal periods, set these metadata attributes for each date column.

• fiscalMonthOffset

  In addition to enabling fiscal date columns, this attribute also determines the first month of the fiscal year. Specify the difference between the first month of the fiscal year and the first month of the calendar year (January) in fiscalMonthOffset. For example, if your fiscal year begins in April, set fiscalMonthOffset to 3.

• isYearEndFiscalYear

  Because the fiscal year can start in one calendar year and end in another, you must specify which year to use for the fiscal year. The isYearEndFiscalYear attribute indicates whether the fiscal year is the year in which the fiscal year ends or begins. For example, if isYearEndFiscalYear = true or you don’t specify this attribute, then the fiscal year is the year in which the fiscal year ends. As shown in this diagram, any dates between 4/1/2015 and 3/31/2016 are part of the fiscal year 2016 because the fiscal year ends in 2016.

  

  If isYearEndFiscalYear = false, then the fiscal year is the year in which the fiscal year begins. As shown in this diagram, any dates between 4/1/2015 and 3/31/2016 are part of the fiscal year 2015 because the fiscal year begins in 2015.

  

Week Numbering in CRM Analytics

For each date loaded into a dataset, CRM Analytics generates the corresponding week number for the calendar year and, if applicable, fiscal year. Similar to the SOQL function WEEK_IN_YEAR, week 1 in CRM Analytics is January 1–7. (This is different from the UTC week() calculation.)

If needed, you can configure the week to start on a particular day of the week by setting the firstDayOfWeek attribute. For example, if January 1 is a Saturday and you configure the week to start on a Monday, then week 1 is January 1–2. Week 2 starts on Monday, January 3. Week 3 starts January 10, the following Monday. Notice that week 1 can be a short week to ensure that the subsequent weeks start on the specified day of the week.