April 2019 Release Notes

Modeling 2.8.2: No New Add-In

The SpotlightXL and Spotlight Office Add-Ins were not changed in this release. This release does not require existing users to upgrade their client software. See Where to Download SpotlightXL and Version Information for information.

Modeling 2.8.2: Web Reporting Enabled for All Customers

An edition of Reports in Spotlight was introduced in November 2018, called Web Reporting. Until now, customers needed to call Support to have this feature enabled in their applications. With the April release 2.8.2, Web Reporting will be automatically enabled for all customers. If you were already using Web Reporting, there is no change and you can continue to use it.

For information on this feature, see Spotlight Web Reporting.

Modeling 2.8.2: External Source Model 2.0 Enabled By Default for New Tenants and Existing Tenants with No ESM Implementations

An edition of the External Source Model was introduced in November 2018, called ESM 2.0. This edition supports data transformation, date logic, dynamic dimension additions, operational planning, new data types, and formulas and expressions.

• All new customers and new tenants established after the April 2019 release will have ESM 2.0 enabled by default.

• Existing customers and tenants who have not used the ESM functionality will have ESM 2.0 enabled by default.

• Existing customers and tenants who have used the older ESM functionality must call Host Analytics Support to use ESM 2.0 because the existing ESMs must be migrated to the ESM 2.0 functionality.

• If you were already using ESM 2.0, there is no change and you can continue to use it.

For information on this feature, see Using External Source Models.

Modeling 2.8.2: Five Formulas Added to External Source Models

In this release, you have the option to use five additional formulas when defining External Source Models:

• EOMonth

• EDate

• Min

• Max

• Text

Using formulas, you can transform or modify data coming from the external data source.

For basic information on External Source Model data types, formulas, and fields, see External Source Model Field Types, Expressions, and Formulas.

Additional Date Functions

• EOMONTH([start_date], number of months), returns the date for the last day of the month that is indicated by the number of months before (negative value) or after (positive value) the start_date. The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EOMONTH calculates project end dates that fall on the last day of the month. Both arguments are required.

   Example: EOMONTH([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 28, 2019.

• EDATE([start_date], number of months), returns the date that is indicated by the number of months before (negative value) or after (positive value) the start_date. The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EDATE calculates project end dates that fall on the same day of the month as the project start date. Both arguments are required.

   Example: EDATE([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 23, 2019.

Additional Functions for Multiple Data Types: Numbers, Dates, or Text Strings

• MIN(a1, a2, a3, ..., a255), returns the smallest or oldest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.

• MAX(a1, a2, a3, ..., a255), returns the largest or newest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.

Additional Text Functions

• TEXT([date], format), returns the date as a text string in the specified format. The argument date must be specified by using a date field, the DATE function, or the result of other formulas. The argument format is the same as the Format column for Date fields and must use uppercase: "MMM-YYYY", "MMM-YY", "DD/MM/YYYY", "MM/DD/YYYY", "DD-MM-YYYY", or "MM-DD-YYYY". Both arguments are required.
   Example: TEXT(DATE(1,1,2019), "MMM-YYYY") returns the text string "Jan-2019".

Example: Using EOMonth, EDate, Min, Max, and Text Functions

The following External Source Model uses all five of the new functions. Three fields are inputted:

• Project name

• Project Start date

• Term in Months

The other five fields are calculated:

• Project End date uses the EDATE function to calculate the end date by adding the specified number of months to the project start date (assuming the Term is a positive number). It returns a date that is on the same day of the month as the project start date.

• Project End Month date uses the EOMONTH function to calculate the end month by adding the specified number of months to the project start date (assuming the Term is a positive number). It returns a date that is on the last day of the month as the project start date.

• MIN of Dates uses the MIN function to specify which date is earlier: Project Start or Project End.

• MAX of Dates uses the MAX function to specify which date is later: Project Start or Project End.

• Month uses the TEXT function to indicate what month the project ends, and returns the information in the format MMM-YY.

The external data source contains the following fields.

After loading this data into the External Source Model, the five formula fields are calculated.

For basic information on creating External Source Models, see Creating an External Source Model using Model Manager.

For basic information on loading data into External Source Models, see Loading Data into the External Source Model.

Consolidation Functionality Changes Coming Spring19

There are two changes to the Consolidation functionality coming with the Spring19 release. Read all about how it will impact you by clicking here.