Using External Source Model Functions

External Source Models support the use of functions within formulas for ease of calculations.

External Source Model Supported Functions

You can transform or modify data coming from the external data source using formulas. In all formulas, fields used as the arguments in the syntax must be enclosed with square brackets [ ].

The following function types are similar to those supported in Excel.

• Multiple Data Types
• Text
• Numeric
• Math
• Logical
• Date

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.
• See Example: Using EOMonth, EDate, Min, Max, and Text Functions
• 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.
• See Example: Using EOMonth, EDate, Min, Max, and Text Functions 
• DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,...... ) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.
  see Example: Using the Dropdown Function.
• DROPDOWN("Model::[ESM Model Name]","[Key Field Name]" ) returns the dropdown value with the Key Field Name from an existing External Source Model name. See Example: Dropdown function with External Source Model Field referenced values.

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

The following External Source Model uses six input fields and one functions.

Three fields are inputted:

• Account 
• Department 
• ID
• Joining Date
• Salary 
• Bonus

The other one field has function:

• Total uses the SUM function to specify salary and bonus calculation

The external data source contains the following fields.

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

Example: Using the Dropdown Function

The DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,.....) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.

• When you use the dropdown on the text field, the parameter is defined as text in double-quotes separated by a comma.
• When you use the dropdown on the numeric field, the parameters are defined as normal numbers, decimal numbers without double quotes and separated by a comma.
• When you use the dropdown on the date field, the parameters are defined in date format in double-quotes separated by a comma. Date field type supports six types of formats (MMM-YYYY, MMM-YY, DD/MM/YYYY, MM/DD/YYYY, DD-MM-YYYY, and MM-DD-YYYY).

Following are the limitations for using the dropdown function:

• You cannot use duplicate values for the parameters
• You cannot use more than 100 parameters
• Parameter values must be case insensitive
• You can only enter those values manually which are already present in the dropdown.

For example, let’s say you want to list the numbers of units sold per region in a month as shown below.

You can select the region from the dropdown that is defined in the source model.

You can select the units sold from the dropdown that is defined in the source model.

Example: Dropdown function with External Source Model Field referenced values

This function returns the dropdown value with the Key Field Name from an existing External Source Model name.

For example, let’s say you have one existing External Source Model with Dropdown function, and you want to create a new External Source Model model with Dropdown function by reusing the existing External Source Model fields.

The existing External Source Model is named as DropdownSource. The following images gives you a view of DropdownSource model and the fields.

The following image display the source data of DropdownSource model.

If you want to use the DropdownSource fields in the DropdownTarget model, create the DropdownTarget model using the dropdown function with External Source Models field referenced values as shown in the below figure:

The DropdownTarget model can now use the existing DropdownSource fields and you can select the Units and Month field valued from the dropdown list as shown in the below figure:

Text Functions

• LEFT([text field ], n ), returns n characters from the left side of a text field . If n is omitted, one character is returned.
• RIGHT([text field ], n ), returns n characters from the right side of a text field . If n is omitted, one character is returned.
• CONCATENATE([text field1 ], [text field2 ], [text field3 ], ...), returns the combination of text field1 and text field2 (and optionally additional text fields) as one longer text string.
• MID([text field ], start_num, num_chars), returns the characters from the middle of a text string, given a starting position and length.
• PROPER([text field ]), converts and returns a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase.
• LEN([text field ]), returns the number of characters in the specific text field.
• TRIM([text field ]), returns the text field with any trailing white space, such as blank spaces, removed.
• UPPER([text field ]), returns text as an uppercase. This function is applicable only for Text type and ConstantText type only.
• VALUE([text field ]), converts a text string representing a number into the number (double) format. It supports percentages, currencies, scientific notations, and negative numbers.
• LOWER([text field ]), returns text as a lowercase. This function is applicable only for Text type and ConstantText type only.
• FIND([text field1 ], [text field2 ]), returns the starting position of text field1 within text field2. You can also use quotes to specify the text to find in text field2. If the text appears more than once within text field2 , this function returns the starting position of the first occurrence, from left to right.
• LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name }), returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where Key Field Name was found. Key Field Name must exist in ESM ModelName and in the model that contains the LOOKUP function. The Key Field Name that exists in both models must be enclosed with curly braces { }. See Example: Using the LOOKUP Function. To look up a number instead of text, see RANGELOOKUP under Math and Numeric 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".

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

Example: Using the LOOKUP Function

The LOOKUP function allows two ESM models to communicate with each other. There must be one matching field in both models, called the Key Field.

In the following example, Account is the Key Field. Models AA1 and BB1 will interact. Model AA1 contains the information that model BB1 will look up.

Here is the list of fields in model AA1.

Here is model AA1's data.

Model BB1 will look up data in model AA1 based on the common key field, Account.

Here is the list of fields in model BB1. Three fields are formulas with LOOKUP functions.

To calculate the LOOKUP functions, you need to load data only into the first field, Account. All other fields are derived.

Copy and paste the accounts into column A, then click Load Data.

Click Refresh and then you will see the remaining columns calculated and filled in.

Explanation

• Reminder: here is the syntax for the LOOKUP function:

LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name })

• Model BB1 has 4 fields.
  • The first field in BB1 is a text field that serves as the Key Field: Account.
  • The second field in BB1 is Offset Account and contains the following formula:
    LOOKUP("AA1","AAOffset Account", {Account})
    Offset Account is derived by matching the Key Field {Account} (from row 1 in BB1, "PM Fee Revenue"), to a field of the same name in model AA1. Once the matching row is found, the value of the field called AAOffset Account from the row where "PM Fee Revenue" was found in AA1 is returned and placed into Field 2 in BB1: Offset Account.
  • The third field in BB1 is Activity and contains the following formula:
    LOOKUP("AA1","AAActivity", {Account})
    Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAActivity from AA1 into Field 3 in BB1: Activity.
  • The fourth field in BB1 is Offset Activity and contains the following formula:
    LOOKUP("AA1","AAOffset Activity", {Account})
    Offset Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAOffset Activity from AA1 into Field 4 in BB1: Offset Activity.
• This completes the first row in BB1.
• In the second and following rows of BB1, the same method is used to look up the Key Field and derive the values to place into Fields 2-4.
• When a row is encountered in BB1 where the Key Field does not exist in model AA1 ("DEFABC"), no values for Fields 2-4 are created.

Numeric Functions

• Basic arithmetic operators: + - * /
  Example: [Unit Price]*[Avg Daily Units]
• SUM([numeric field1 ], [numeric field2 ], ... ), returns the sum of a comma-separated list of values.
• AVG([numeric field1 ], [numeric field2 ], ... ), returns the average value of a comma-separated list of values.
• ROUND([numeric field ], n ), returns the value of the numeric field rounded to the n th decimal place.
  Example: Round([Total Amount]/12,2)
• FLOOR([numeric field ], n ), returns the value of the numeric field rounded down to the nearest integer multiple of n.
  Example: FLOOR([MyPrice],0.25) rounds down to the nearest quarter.
• CEILING([numeric field ], n ), returns the value of the numeric field rounded up to the nearest integer multiple of n.
  Example: CEILING([MyPrice],0.25) rounds up to the nearest quarter.
• RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })

or

RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField }, Decimal_Precision )

This function returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where the Key Fields match and where the value of KeyLookupField fits into the range of values between LowRangeFieldName and HighRangeFieldName.

• The Key Field Names are KeyFieldName1, KeyFieldName2, ..., KeyFieldNameN. You must have at least one Key Field Name. The Key Field Name(s) must exist in ESM ModelName and in the model that contains the RANGELOOKUP function. These key field names must be enclosed with curly braces { }. You can use an unlimited number of Key Fields, but a best practice is to use fewer than 10.
• KeyLookupField is a field of type Numeric or Formula (that resolves to a number). It contains the value that you want to look up in the range of values between LowRangeFieldName and HighRangeFieldName.
• LowRangeFieldName and HighRangeFieldName must be of type Numeric or Formula (that resolves to a number) in ESM ModelName.
  • LowRangeFieldName uses >= (greater than or equal to) when comparing to KeyLookupField.
  • HighRangeFieldName uses < (less than) when comparing to KeyLookupField.
  • Ranges should be continuous so they do not leave gaps: 0-5, 5-10, 10-15, and so on.
  • If the value of KeyLookupField is below the value of LowRangeFieldName , 0 is returned.
  • If the value of KeyLookupField is equal to or above the value of HighRangeFieldName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
  • If the value of KeyLookupField does not fit in any range, 0 is returned. This implies that the ranges are not continuous, and the KeyLookupField value falls in a gap between ranges.
  • If the value of KeyLookupField is valid in more than one set of ranges (rows) in ESM ModelName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
  • Decimal_Precision is optional. It indicates to what decimal place numbers should be compared. In other words, how far out to the right should the KeyLookupField be compared to the low range and high range. For example, if Decimal_Precision is 6, KeyLookupField is 0.12345 and the low range is 0.12 and the high range is 0.123456, then KeyLookupField is found in the range. If not provided, the default is 4. Valid values for Decimal_Precision are integers 0-9.
• HighRangeFieldName has a maximum value of 922 trillion, if using 4 decimal places.
• Lowering the number of decimal places from the default of 4 increases the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
• Increasing the number of decimal places from the default of 4 reduces the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
• As a best practice, using decimal precision greater than 4 is recommended only when the range values are small numbers.
• See Example: Using the RANGELOOKUP Function. To look up text instead of a number, see LOOKUP under Text Functions.

Example: Using the RANGELOOKUP Function

The RANGELOOKUP function allows two ESM models to communicate with each other. There must be one or more matching fields in both models, called the Key Fields.

In the following example, Company and Region are the Key Fields. Models Commission Ranges and Commissions Looked Up will interact. Model Commission Ranges contains the information that model Commissions Looked Up will look up.

Here is the list of fields in model Commission Ranges.

Here is the data for model Commission Ranges.

Model Commissions Looked Up will look up data in model Commission Ranges based on the common key fields, Company and Region.

Here is the list of fields in model Commissions Looked Up.

To calculate the RANGELOOKUP function, you need to load data into the first four fields. The formula fields are derived. Copy and paste the raw data into columns A-D, then click Load Data.

Click Refresh and then you will see the remaining columns calculated and filled in.

Explanation

• Reminder: here is the syntax for the RANGELOOKUP function:

  RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })

• Model Commissions Looked Up has six fields:
• The first two fields are text fields that serve as the Key Fields: Company and Region.
• The third field is an informational text field: Sales Rep.
• The fourth field is a numeric field that serves as the Key Lookup Field: Sale Amount. This is the number that is used to look in the ranges of numbers in the model Commission Ranges.
• The fifth field is Commission % and contains the following formula:
  RANGELOOKUP("Commission Ranges","Comm%", "Sales Amt Low", "Sales Amt High", {Company}, {Region}, {Sale Amt})
  • Commission % is derived by matching the Key Fields Company and Region (from row 1 in Commissions Looked Up, "Company A" and "West"), to the fields of the same name in model Commission Ranges. In Commission Ranges, "Company A" and "West" are found in row 1.
  • Next, the value of Sale Amt is matched to the range of values between Sales Amt Low and Sales Amt High in row 1 of Commission Ranges. If Sale Amt >= Sales Amt Low, and if Sale Amt < Sales Amt High, the range is a match. Since the Sale Amt was 5000 and the Low and High Ranges were 0 to 10,000, then the range is row 1 is a match.
  • Next, the value in the field Comm% from the row where the range matches in Commission Ranges is returned and placed into the Commission % field in Commissions Looked Up. The value of Comm % is 0.1 and this is returned to Commissions Looked Up and placed into the field Commission %.
• The sixth field is Commission Amt and it is a formula of Commission % * Sale Amt. This formula takes place within the Commissions Looked Up model and results in 500. To further refine this formula, a rounding function could be added to it: ROUND([Sale Amt]*[Commission %], 2)

The rows of data in Commissions Looked Up demonstrate the rules for the ranges:

Example: Using RANGELOOKUP to Compare Dates

If you want to compare Ranges and get a value as the output based on the details configured in the model, you can use the RANGELOOKUP function.

The following use case provides details on how you can use the RANGELOOKUP function to compare Dates and other attributes in the model and get a Value as the output.

For example, let's consider a source model that contains data for a company and its products in a specific region. Suppose you have defined a date range with their respective Start Date and End Date and defined a Value for each date range. The following image shows the model with all data.

When you enter any date for comparing data (as the last lookup field argument), the RANGELOOKUP function uses the attributes defined in the syntax and searches for the date within the date range. If the date falls within any of the Date Range defined and all other attributes match, the RANGELOOKUP function returns the value specified for that Date Range. The low range is inclusive and the high range is exclusive, so 04/01/2021 would return the value

The RANGELOOKUP function validates the date and other model parameters such as region, product, etc., and returns the configured value as the output.

In the above example, when you enter the comparison date as 09/01/2021, the RANGELOOKUP function returns the Value as 3. The function validates all parameters defined as keys in the syntax and returns the Value only if all keys match, that is, Region, Product, Company, and Comparison Date. So, when the comparison date was 09/01/2021, the RANGELOOKUP function validates if the date “09/01/2021” falls in any of the specified Date Range (Start Date and End Date) and also validates if the keys Region - “West”, Product - “Laptop”, Company - “Company A” is available in the Model, and then returns the value “3” configured for the combination.

The following image shows the defined syntax for the function.

If any of the parameters are not available in the Model, the function returns 0 as the value. The last parameter must be the field used for comparison. For example, when the Comparison Date was 11/28/2021 and Region was “East” and Company was “Company B”, the function returned the value 0 as the Model did not contain the Region and Company details.You have the flexibility to define the keys in the function's syntax based on your requirements and model. If the Comparison Date falls below the ranges (12/01/2020), the value returned will be 0. If the Comparison Date falls beyond the ranges (01/01/2022), the value returned will be 4.

Logical Functions

• Basic comparison operators: < > <= >= <>
• IF([expression ], TrueResult , FalseResult ), evaluates the logic of the expressions and returns TrueResult if the expression is true and FalseResult if the expression is false.
• AND([expression1 ], [expression2 ]), returns TRUE if expression1 and expression2 are both true, otherwise returns FALSE.
• OR([expression1 ], [expressions2 ]), returns TRUE if either expression1 or expression2 is true; returns FALSE if both expressions are false.
• ISBLANK([field ]), returns TRUE if the field is blank, otherwise returns FALSE.
• Nested IF, you may nest IF functions within other IF functions. You can nest up to 7 levels.

Date Functions

• Basic arithmetic operators: + -
• YEAR([date ]), returns the year in the date , 0000-9999.
  Example: YEAR([datefield]) where datefield points to 11/08/18 returns 2018.
• MONTH([date ]), returns the number of the month in the date , 1-12.
  Example: MONTH([datefield]) where datefield points to 9/8/18 returns 9.
• DAY([date ]), returns the number of the day of the month in the date , 1-31.
  Example: DAY([datefield]) where datefield points to 11/08/18 returns 8.
• DAYSCOUNT([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), returns the count of days from a Sub-Start Date to the specified "MMM-YYYY".
  Example: DAYSCOUNT([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018")
• See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle
• DAYSFACTOR([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), factor based on the count of days from a Sub-Start Date to the specified "MMM-YYYY" divided by the number of days in that month.

Example: DAYSFACTOR([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018")

See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle

• DAYS([datefield2 ], [datefield1 ]), returns the number of days between datefield1 and datefield2 . For a positive value, datefield2 should be after datefield1.
• WEEKNUM([date ]), returns the count of weeks from the beginning of the year to the date specified.

Example: WeekNum("2/1/18") returns 5 because Feb 1 falls in the middle of the 5th week of 2018.

• DATE(mm, dd, yyyy ), returns the date in the format MM/DD/YYYY.
• 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.

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

• 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.

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

• SUM([datefield ], n), returns the date that is indicated by the number of days, n , added to the datefield . To add to the datefield , n should be a positive number. To subtract from the datefield , n should be a negative number.

See Example: Using Date Arithmetic Functions

Example: Calculating DAYSCOUNT and DAYSFACTOR for a Contract within a Planning Cycle

DAYSCOUNT calculates the number of days that a contract is in progress in a particular month. DAYSFACTOR calculates the percentage of time that a contract is in progress in a particular month. In the following example, assume the following values:

• Contract Start is 03/15/2023 and Contract End is 05/31/2023.
• Planning Start is 01/01/2023 and Planning End is 12/31/2023.
• DAYSCOUNT for Feb-2023 is 0. DAYSCOUNT for Mar-2023 is 17. DAYSCOUNT for Apr-2023 is 30. DAYSCOUNT for May-2023 is 31.
• DAYSFACTOR for Feb-2023 is 0.0. DAYSFACTOR for Mar-2023 is 0.548. DAYSFACTOR for Apr-2023 is 1.0. DAYSFACTOR for May-2023 is 1.0.

Example: Using Date Arithmetic Functions

The following External Source Model uses five input fields and three formulas.

Five fields are inputted:

• Name
• Work Start Date
• Duration in Days
• Bid Amount
• Status

The other three fields are calculated:

• RFP Start Date uses date arithmetic to subtract 30 days from the Work Start Date. This date provides a timeframe for the Request for Proposal to be written.
• Midpoint Date uses date arithmetic to calculate the midpoint date of the project. It uses the input field Duration in Days, divides it in half, and adds that number to the Work Start Date.
• Projected End Date uses the SUM function to demonstrate how to add the input field Duration in Days to the input field Work Start Date.

The external data source contains the following fields.

After loading this data into the External Source Model, the three formula fields are calculated. You can see that the Projected End Date is the Work Start Date plus the Duration. The Midpoint Date is halfway between the Work Start Date and the Projected End Date. The RFP Start Date is 30 days before the Work Start Date.

Example: Converting Transaction Date MM/DD/YY to Separate Fields: Month MM and Year YYYY

In the example below, the source data contains 6 columns (rows 5-10). The Master Model requires that the transaction date (Time) be broken out into separate Month and Year dimensions, so expressions are added that render these fields. Since the expression MONTH[Time] renders as a month number without a leading zero (such as month 1, 2, 3, 10, 11, 12), a constant "0" is concatenated ahead of the month number (such as 01, 02, 03, 010, 011, 012). Then final Month ID is calculated as the right two characters of that string.

Transferring Data from Analytical Model to ESM Model

You can now transfer data from Analytical Model to ESM. Previously, we could transfer Analytical Model data to Analytical Model, and ESM data to ESM or Analytical Model. In the Source Model drop-down list, you can now see Analytical and ESM options which you can map to the Target ESM.

In Practice: To Transfer Data

1. In SpotlightXL, select the Model task> External Source Model> Source Map.
2. Enter the name of the Source Map and add the description.
3. Select the type as Data for loading external source data.
4. Select the Analytical model you want to map the target ESM.
   
5. Select any of the following in the Maps To column:
   1. DimensionFilter: This filter indicates that there is no field present in the Source ESM to map to the Target ESM. You will have to specify a value in the Default Value Column.
   2. Value: indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.
6. Run the Source Map to check the data in the ESM.