The following tables lists the arithmetic functions and indicates whether you can use them in properties window fields (QW), in the Data Editor (DE), or in both:
| Function | Description | QW | DE |
|---|---|---|---|
| ABS(x) | Returns the absolute value of an expression. | Y | Y |
| AVEPERIODS(# field) | Returns the average (over all periods) of a numeric field. | Y | |
| AVG(a,b,...) | Returns the average of a list of values. | Y | Y |
| DegToRad (x) | Converts degrees to radians. | Y | Y |
| EXP(x) | Returns e to the power of x. | Y | Y |
| INT(x) | Returns the integer value of a value; truncates decimal point values. | Y | Y |
| LN(x) | Returns the natural logarithm (basee) of value x. | Y | ? |
| LOG(x) | Returns the common logarithm (base 10) of value x. | Y | Y |
| MAX(a,b,...) | Returns the maximum value in a list. | Y | Y |
| MIN(a,b,...) | Returns the minimum value in a list. | Y | Y |
| PERCENT(x,y) | Removed text - Art | Y | Y |
| PI() | Returns the value 3.1415926. | Y | Y |
| POWER(x,y) | Returns x to the power of y. | Y | Y |
| PRODUCT(a,b,...) | Multiplies the list of numbers in parentheses. | Y | Y |
| RadToDeg(x) | Converts radians to degrees. | Y | Y |
| RAND(x,y) | Returns a random number between x and y. | Y | Y |
| ROUND(x,number) | Rounds the expression x to the indicated number of decimal places. | Y | Y |
| SQRT(x) | Returns the positive square root of x. | Y | Y |
| SUM(a,b,..) | Returns the sum of a list of values or expressions. | Y | Y |
| SUMPERIODS(# field) | Returns the sum (over all periods) of a numeric field. | Y |
The following tables lists the Boolean or conditional functions and indicates whether you can use them in properties window fields (QW), in the Data Editor (DE), or in both:
| Function | Description | QW | DE |
|---|---|---|---|
| BETWEEN(lb,y,ub) | Returns 1 if y is equal to or greater than the lower boundary (lb) and y is equal to or less than the upper boundary (ub); otherwise returns 0. | Y | Y |
| EQTOL(x,y,tol) | Returns 1 if the difference between x and y is equal to the tolerance value (tol). | Y | Y |
| FALSE() | Returns 0. This function does not take an argument. | Y | Y |
| GETOL(x,y,tol) | Returns 1 if the difference between x and y is greater than or equal to the tolerance (tol). | Y | Y |
| GTTOL(x,y,tol) | Returns 1 if the difference between x and y is greater than the tolerance (tol). | Y | Y |
| IF(cond,x,y) | Returns x if the condition is true, y if the condition is false. | Y | Y |
| ISMULTIPLE(x,y) | Returns 1 if x=ny, where n is an integer, otherwise, it returns 0. | Y | Y |
| LETOL(x,y,tol) | Returns 1 if the difference between x and y is less than or equal to the tolerance (tol). | Y | Y |
| LTTOL(x,y,tol) | Returns TRUE() if the difference between x and y is <= tolerance (tol). | Y | Y |
| NEG(x) | Returns 1 (true) if x is negative. | Y | Y |
| NETOL(x,y,tol) | Returns TRUE() if the difference between x and y is <> tolerance (tol). Data Editor only. | Y | Y |
| NOT(x) | Returns 1 (true) if x is 0. | Y | Y |
| OVERUNDER(lb,x,ub) | Returns 0 if x is between the lower boundary (lb) and the upper boundary (ub); otherwise returns 1. | Y | Y |
| POS(x) | Returns 1 if x is greater than 0. | Y | Y |
| SEQUENCE(a,b,...) | Ensures that the values or expressions are performed in order and returns TRUE(). | Y | Y |
| SIGN(x) | Returns -1 if x is less than 0; returns 0 if x equals 0; returns 1 if x is greater than 0. | Y | Y |
| TRUE() | Returns 1. This function does not take an argument. | Y | Y |
| ZERO(x) | Returns 1 if x equals 0. | Y | Y |
The following tables lists available color functions that are used only in the Data Editor:
| Function | Description |
|---|---|
| BACKGROUND() | Changes the background and foreground to the same color. |
| BLACK() | Changes color to black. |
| BLUE() | Changes color to blue |
| GREEN() | Changes color to green. |
| ORANGE() | Changes color to orange. |
| PINK() | Changes color to pink. |
| RED() | Changes color to red. |
| VIOLET() | Changes color to violet. |
| YELLOW() | Changes color to yellow. |
The following tables lists the trigonometric functions and indicates whether you can use them in properties window fields (QW), in the Data Editor (DE), or in both:
| Function | Description | QW | DE |
|---|---|---|---|
| ATAN2(x,y) | Returns the arc tangent of x y. | Y | Y |
| COS(x) | Returns the cosine of x. | Y | Y |
| SIN(x) | Returns the sine of x. | Y | Y |
| TAN(x) | Returns the tangent of x. | Y | Y |
The following tables lists other functions available in Strategic Network Optimization. These functions are only used in the Data Editor:
| Function | Description |
|---|---|
| Assign(#tofield,expr) | Assigns the result of the expression to a specific field. |
| Cover(#timeUnit,#demand,#cover, [#last/avg]) | Returns the amount of time that your inventory lasts. |
| Depletion(#depletor,depletorOffset, #depletee, #timeUnit,#f/b,#last/avg) | Determines the ability to meet future demand based on inventory levels. |
| ERROR(#errorMessage) | Displays an error message. |
| InvCover(#timeUnit,#demand,#cover, [#last/avg]) | Returns the amount of inventory required to meet demand in a certain time period. |
| InverseDepletion(#depletor,depletorOffset, #depletee,#timeUnit,#f/b,#last/avg) | Edits the value calculated by the depletion function and recalculates. |
| Next(#field,outOfRangeValue) | Returns the value of the next field or the out OfRangeValue. |
| Prev(#field,outOfRangeValue) | Returns the value of the previous field or the outOfRangeValue. |
| RelOffset(#field,offset,outOfRangeValue) | Returns the value from a relative position or the outOfRangeValue. |
The Next, Previous, and RelOffset functions give you values from relative periods for every column in the Data Editor. However, you might need to use the value from a single relative period and you do not need all of the information that the Next, Previous, and RelOffset functions give you.
In this case, you can use the relative period parameters. Relative period parameters locate one period and use its value in your expression. For example, you might want to use the value in the Min field that is two periods ahead of the current period.
If you specify a period that is after the last period, the data in the last period is repeated. If you specify a period that is before the first period, the data in the first period is repeated.
The following tables describes how to use relative periods:
| Period | Syntax | Explanation |
|---|---|---|
| Current | fieldname | This is the usual method; this expression works with the data in the current period. |
| Previous | fieldname:-n | The number of periods previous, relative to the current period. For example, Min:-3 means to use the value from the Min field that is three periods before the current period. |
| Future | fieldname:n | The number of periods in the future, relative to the current period. For example, Min:3 means to use the value from the Min field that is three periods after the current period. |
| Specific | fieldname:#n | The specific periods to use, regardless of the current period. For example, Min:#3 means to use the value from the Min field in the third period (the first period in your model is numbered 1). |
The ABS function returns the absolute (non-negative) value of an expression. You can use this function if you want to calculate the difference between a forecast value and an actual value, but do not want the calculation to result in a negative number.
The function syntax is:
ABS(x)
For example, if you want to ensure that the result of the sum of the two field hours available and hours used is a positive number, enter:
ABS(SUM('hours available','hours used'))
The Assign function, available only in the Data Editor, takes the result of the expression (expr) and places it in the specific field (#tofield). This function is used only with inverse formulas.
The function syntax is:
Assign(#tofield,expr)
Enter the number sign (#) before the name of the field to which you are assigning the data. For example, if you want to calculate the sum of the fields YellowBig and RedBig and apply it to the field BigTotal, enter:
Assign(#BigTotal,SUM(YellowBig,RedBig))
If you want to change the value of several fields when you edit a single expression, you multiply several assign statements in the inverse formula. To perform three Assign functions when you edit the field called InputField, enter:
Assign(#FieldA,InputField/1000) * Assign(#FieldB,InputField*2) * Assign(#FieldC,InputField)
The ATAN2 function returns tan-1(x/y), the arc tangent of x divided by y, where x and y are measured in radians.
The function syntax is:
ATAN2(x,y)
The AVEPERIODS function is available in Strategic Network Optimization but not the Data Editor. The function finds the average (over all of the periods) of a numeric field. You must precede the field name with a # character.
The function syntax is:
AVEPERIODS(#field)
For example, suppose you want to find demand data points that lie well outside the average demand values. You could use the following expression in the Demand field of a StorageDemand Match window:
x <= AVEPERIODS(#Demand)*.8 OR x >= AVEPERIODS(#Demand)*1.2
The expression matches only when the value in the Demand field is 20 percent above or 20 percent below the average value for demand across all periods.
The AVG function finds the average of the listed values.
The function syntax is:
AVG(a,b,...)
For example, if you want to find the average of the PctUsed field for BigRed, BigBlue, and BigYellow, you could enter:
AVG('PctUsed_BigRed','PctUsed_BigBlue','PctUsed_BigYellow')
The BACKGROUND function is available in the Data Editor only. This function changes the background and foreground to the same color so you cannot see your data. Use this function if you want to hide certain values. For example, you might want to look at a Data Editor view that displays only the values that violate your model's constraints.
The function syntax is:
Background()
For example, suppose you want to display only the values in the PctUsed field that are between 1 and 9. To do this, you can use the IF, BETWEEN, and BACKGROUND functions. You could enter the following expression in the HighlightExpression Editor:
IF(BETWEEN(1,PctUsed,9),BLACK(),BACKGROUND())
The BETWEEN function returns TRUE() if the value of the number is equal to or greater than the lower boundary (lb) and equal to or less than the upper boundary (ub). If the number is not between the upper and lower boundary, the result is FALSE().
The function syntax is:
BETWEEN(lb,y,ub)
For example, if you want to ensure that your value for a field called Production is between your minimum of 10 and maximum of 25, you could enter:
BETWEEN(10,Production,25)
BLACK()
Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
The BLUE function is available in the Data Editor only. This function changes the color of a value to blue.
The function syntax is:
BLUE()
For example, if you want the value of a field to be displayed in blue if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',BLUE(),BLACK())
Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
The COS function returns the cosine of x.
The function syntax is:
COS(x)
The Cover function is available in the Data Editor only. The Cover function determines the amount of time in which your inventory is depleted by using:
The amount of time, in days, in each period
Demand data
Inventory data
Whether the coverage should look forward or backward
The function syntax is:
Cover(#timeUnit ,#demand ,#storage ,[#last/avg])
| Parameter | Meaning |
|---|---|
| #timeUnit | Enter the time unit. The default is days. |
| #demand | Enter the name of the field that contains the demand information. |
| #storage | Enter the name of the field that contains the storage information. |
| #last/avg | Determines how the Data Editor handles the last period. Forward coverage depends on knowing future demand, but in the last period, no demand for subsequent periods exists. The #last option tells the Data Editor to assume that the demand in the periods after the last period is identical to the demand in the last period. The #avg option tells the Data Editor to assume that the demand for periods after the last period is an average of all period demand. This parameter is optional. The default is #last. |
For example, if you want to calculate the number of days in which your inventory will be depleted, and you want to display the result in a new Data Editor field called CoverData, you would enter the following in the Formula field of the Expression Editor for the CoverData field:
Cover(#days,#Demand,#Storage,#last)
The parameters of this example have the following meaning:
#days is the number of days in each period.
#Demand is the field that contains the demand data.
#Storage is the field that contains the inventory data.
#last indicates that the demand after the last period is identical to the demand in the last period.
The Cover function is available in the Data Editor only.
The #timeUnit Parameter
The #timeUnit parameter has different meanings in different situations. The way that the Data Editor interprets the #timeUnit value depends on the situation. The three possible options are described below.
Option 1
Use this option if you do not need a period durations file because your period boundaries are calendar dates, such as February 1, 2005. This option subtracts the date of the previous period from the date of the current period to determine the length of the period.
For example, if the current period is February 1, 2005, and the previous period is January 1, 2005, 31 days are in the period. The result of this calculation is dependent on the time unit that you specify in the #timeUnit parameter.
| Time Unit | Meaning | Result in This Example |
|---|---|---|
| Days | Number of days between the two dates. | 31 days |
| Weeks | umber of weeks between the two dates. The number of weeks is calculated by dividing the number of days by 7. | 4.4 weeks |
| Months | Number of months between the dates; this is the exact number of months if the periods begin on the same day of the month. If the periods do not begin on the same day of the month, the Data Editor assumes that there are 30 days in a month. | 1 |
Option 2
Use this option if you need to have a period durations file because your periods are named by strings, not by real calendar dates, such as Period Two instead of February 1, 2005.
This option looks at the period durations file for your definition of the string that makes up the period name. For example, if the period is called Period Two, the Data Editor looks for your definition of Period Two in the period durations file. If the string is not defined in the period durations file, the Data Editor uses the following defaults:
| Time Unit | Meaning |
|---|---|
| days | 20 |
| weeks | 4 |
| month | 1 |
Option 3
Use this option if you need to have a period durations file because your period boundaries are real calendar dates, such as February 1, 2005, but you do not want to use them. Instead, you want to define your own time unit. The time unit defined in the period durations file is used instead of the realtime unit.
This option is useful when you want to define the number of workdays in a month instead of the number of days. For example, January 2005 has 31 days but there are 21 workdays. If you want the Data Editor to use the number of workdays instead of the number of days (as in Option 1), you need to use the period durations file. In this file, you define the number of days in January 2005 as 21. If the Data Editor cannot find the period durations file, it uses the calculation defined in Option 2.
Creating a Period Durations File
If you want to use a period durations file, you first need to create it. To create a period durations file:
From the Configure menu, select Fields.
Change the period field type to string.
Using a text editor, create and save the period durations file.
Enter the period information for the next few years or decades so that you do not need to update the file each time when the period changes.
From the Configure menu, select Display.
In the Period Durations File field, enter the name of the period durations file that you created in step 3.
Format
The format of the period durations file is a three-column table. The first column contains the name of the period, the second column contains the name of the time unit, and the third contains the number of units in the period.
For example, if you have five periods, your file could look like this:
January days 31 February days 28 March days 31 April weeks 4 May weeks 4
The Depletion function is available in the Data Editor only. This function plans coverage, which compares inventory levels to projected demand and determines your ability to meet future demand based on the inventory levels. The function determines your coverage requirements using the demand that you have in each period, and also determines how long the inventory will meet the demand. The function uses the following information:
Current and projected inventory levels
Projected demand
The unit of time in which the coverage is reported
Whether the coverage should look forward or backward
The function syntax is:
Depletion(#depletor,depletorOffset,#depletee,#timeUnit,#[f/b],#[last/avg])
The parameters have the following meanings:
| Parameter | Meaning |
|---|---|
| #depletor | Contains the product demand on which the coverage is based. |
| depletorOffset | The starting period offset that determines the period, relative to the current period, from which the demand should be calculated. This value must be an integer. For example, if you enter 0, the calculation begins in the current period. If you enter 1 the calculation begins in the next period; whether it begins one period forward or backward depends on the #f/b value. |
| #depletee | Contains the amount of planned inventory or safety stock. Data Editor considers this field when calculating the amount of available inventory that can cover the demand. If you want the Data Editor to consider how your safety stock value covers demand, you must enter the safety stock field name as the depletee. If you want the Data Editor to consider how your inventory levels compare to your demand, you must enter the storage field name as the depletee. |
| #timeUnit | Specifies the time units in which the result of the calculation is measured. You can use days, weeks, or months. |
| #f/b | This specifies whether the calculation should run forward in time (f) or backward in time (b). This parameter is optional; the default is #f. |
| #last/avg | Determines how the Data Editor handles the last period. Forward coverage depends on knowing future demand, but in the last period, no demand exists for subsequent periods. The last option tells the Data Editor to assume that the demand in the periods after the last period is identical to the demand in the last period. The avg option tells the Data Editor to assume that the demand for periods after the last period is an average of all period demand. This parameter is optional. The default is #last. |
For example, if you want to calculate the weeks of supply that are available to meet your demand and display the result in a new Data Editor field called WeeksofSupply, enter the following in the Formula field of the Expression Editor for the WeeksofSupply field:
Depletion(#Demand,1,#EndingInv,#week,#f,#last)
The parameters in this example have the following meanings:
| Parameter | Meaning |
|---|---|
| #Demand | Cover is based on the demand in this field. |
| 1 | The calculation begins in the next time period. |
| #EndingInv | Compared to the #Demand field to determine the amount of inventory available to cover demand. |
| #f | The depletorOffset begins calculations to project demand forward. |
| #last | The demand after the last period is identical to the demand in the last period. |
The DegToRad function converts degrees to radians.
The function syntax is:
DegToRad(x)
For example, DegToRad(180) returns the value3.14
The EQTOL function returns TRUE() if the magnitude of the difference between x and y is less than or equal to the tolerance value (tol). The tolerance level is an arbitrary value that you determine based on your operations. If a value is null, the function returns null.
The function syntax is:
EQTOL(x,y,tol)
The EQTOL function returns true if (x-y)=tol).
The ERROR function is available in the Data Editor only. This function displays the message that you specify.
The function syntax is:
ERROR(#errorMessage)
For example, if you want to display 'Exceeds boundaries' if the result of the sum of the two fields hours available and hours used is not between the minimum and maximum, you can enter:
IF(OVERUNDER(minimum,SUM('hours available','hours used'),maximum),ERROR ('#Exceeds boundaries'),TRUE ())
The EXP function returns e to the power of x.
The function syntax is:
EXP(x)
The FALSE function returns the value 0.
The function syntax is:
FALSE()
The GETOL function (within tolerance x is greater than or equal to y) returns TRUE() if the difference between x and y is equal to or greater than the negative of the tolerance value (tol). The tolerance level is an arbitrary value that you determine, based on your operations. In IDF, if a value is null, the function returns null.
The function syntax is:
GETOL(x,y,tol)
The GETOL function returns true if: x=(y-tol).
The GREEN function is available in the Data Editor only. This function changes the color of a value to green. Color functions are part of the highlighting option. To use them, enter your expression in the Highlight Expression Editor.
The function syntax is:
GREEN()
For example, if you want the value of a field to display in green if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',GREEN(),BLACK())
The GTTOL function (within tolerance x is greater than y) returns TRUE() if the difference between x and y is greater than the negative of the tolerance value (tol). The tolerance level is an arbitrary value that you determine, based on your operations. In IDF, if a value is null, the function returns null.
The syntax is:
GTTOL(x,y,tol)
The GTTOL returns TRUE if: x>(y-tol).
The IF function takes one action when a condition is true and takes another action when the condition is false. If cond is true, do x ; otherwise, do y. You will probably use the IF function with several other expressions.
The function syntax is:
IF(cond,x,y)
For example, if you want to display a value in green if the difference between the planned and actual values is equal to the tolerance level of 5, or else in red, you could enter:
IF(EQTOL(Planned,Actual,5),GREEN(),RED())
You use these comparisons in IF functions as you would other comparisons, such as =, <=, and >=. In Boolean comparisons, true is equivalent to 1, and false is equivalent to 0. The following tables show the results when you compare two expressions:
AND
| Expression 1 | Expression 2 | Result |
|---|---|---|
| 0 | 0 | 0 |
| 0 | 1 | 0 |
| 1 | 0 | 0 |
| 1 | 1 | 1 |
| If both expressions are true, the result is true. |
OR
| Expression 1 | Expression 2 | Result |
|---|---|---|
| 0 | 0 | 0 |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 1 | 1 | 1 |
| If either expression is true or if both expressions are true, the result is true. |
XOR
| Expression 1 | Expression 2 | Result |
|---|---|---|
| 0 | 0 | 0 |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 1 | 1 | 0 |
| If only one expression is true, the result is true. |
The INT function returns the integer value of x only; it does not return any decimal point values. Use this function if you are not concerned with the decimal point values of a number.
The function syntax is:
INT(x)
For example, the expression INT(9.999) returns the integer 9.
The InvCover function is available in the Data Editor only. It calculates the amount of inventory that you need to meet your demand over a certain length of time. This function helps you to calculate your safety stock levels.
The syntax of the InvCover function is similar to the syntax for the Cover function, and each of the parameters has the same meaning as the Cover function parameters.
The function syntax is:
InvCover(#timeUnit,#demand,#cover,[#last/avg])
The parameters have the following meanings:
| Parameter | Meaning |
|---|---|
| #timeUnit | In this parameter, enter the time unit. |
| #demand | In this parameter, the syntax of the InvCover function is similar to the syntax for the Cover function, and each of the parameters has the same meaning as the Cover function parameters. Enter the name of the field that contains the demand information. |
| #over | In this parameter, enter the name of the field that contains the data for the time when the inventory will be depleted. |
| #last/avg | Determines how the Data Editor handles the last period. Forward coverage depends on knowing future demand, but in the last period, demand for subsequent periods does not exist. The #last option tells the Data Editor to assume that the demand in the periods after the last period is identical to the demand in the last period. The #avg option tells the Data Editor to assume that the demand for periods after the last period is an average of all period demand. This parameter is optional. The default is #last. |
For example, suppose you want to calculate the safety stock for the inventory Red, and you want to display the result in a new Data Editor field called RedSafety. You would enter the following formula in the Formula field of the Expression Editor for the RedSafety field:
InvCover(#days,#RedDemand,#RedCover,#last)
The parameters of this example have the following meaning:
| Parameter | Meaning |
|---|---|
| #days | The number of days in each period. |
| #RedDemand | Contains the demand data. |
| #RedCover | Contains the information about how many days it will take to deplete the inventory. |
| #last | The demand after the last period is identical to the demand in the last period. |
The InverseDepletion function is only available in the Data Editor. This function allows you to edit the value calculated by the depletion function, and then recalculate the safety stock or storage that you must have on hand to meet your demand.
When you use the inverse depletion function, you must assign the result of the calculation to a specific field. The inverse depletion function is useful if you want to conduct "what-if" scenarios. The syntax of this function is similar to the depletion function, and each of the parameters has the same meaning as the depletion function parameters. The function syntax is:
InverseDepletion(#depletor, depletorOffset,#unitofCover, #timeUnit,[#f/b],[#last/avg])
The parameters have the following meanings:
| Parameter | Meaning |
|---|---|
| #depletor | Contains the product demand on which the coverage is based. |
| depletorOffset | The starting period offset that determines the period, relative to the current period, from which the demand should be calculated. This value must be an integer. For example, if you enter 0, the calculation begins in the current period. If you enter 1 the calculation begins in the next period; whether it begins one period forward or backward depends on the #f/b value. |
| #unitofCover | Specifies the depletion field name. |
| #timeUnit | Specifies the time units in which the result of the calculation is measured. |
| #f/b | Specifies whether the calculation should run forwards in time (f) or backwards in time (b). This parameter is optional. The default is f . |
| #last/avg | Determines how the Data Editor handles the last period. Forward coverage depends on you knowing future demand, but in the last period, no demand for subsequent periods exists. The #last option tells the Data Editor to assume that the demand in the periods after the last period is identical to the demand in the last period. The #avg option tells the Data Editor to assume that the demand for periods after the last period is an average of all period demand. This parameter is optional. The default is #last. |
For example, if you have four weeks of inventory, and you want to determine how much safety stock that you need if you change to two weeks of supply, you would enter the following depletion and inverse depletion function expressions in the Expression Editor:
| Field Name | Weeks of Supply |
|---|---|
| Formula | Depletion(#Demand,1,Safety,#days,#f, #last |
| Inverse Formula | Assign(#Safety,InverseDepletion (#Demand,1,#'Weeks of Supply',#weeks,#f, #last) |
The ISMULTIPLE function returns 1 if x=ny, where n is an integer. Otherwise, it returns 0.
The function syntax is:
ISMULTIPLE(x,y)
The LETOL (within tolerance x is less than or equal toy) function returns TRUE() if the difference between x and y is less than or equal to the tolerance value (tol). The tolerance level is an arbitrary value that you determine based on your operations. In IDF, if a value is null, the function returns null.
The function syntax is:
LETOL(x,y,tol)
The LETOL returns TRUE if: x=(y +tol).
The LN function returns the natural logarithm (base) of an expression (x). Use this function if you want to perform a statistical analysis that requires logarithmic functions.
The function syntax is:
LN(x)
The LOG function returns the common logarithm (base 10) of an expression (x). Use this function if you want to perform a statistical analysis that requires logarithmic functions.
The function syntax is:
LOG(x)
The LTTOL function (within tolerance x is less than y) returns TRUE() if the difference between x and y is less than the tolerance value (tol). The tolerance level is an arbitrary value that you determine based on your operations. In IDF, if a value is null, the function returns null.
The function syntax is:
LTTOL(x,y,tol)
The LTTOL function returns TRUE if: x <(y +tol).
The MAX function returns the maximum value in a list of values. This function is useful if you have a series of calculations and want to know which expression returns the highest number. If a value is null, it is ignored.
The function syntax is:
MAX(a,b,...)
For example: MAX(1,2,3,4,5) The result of this expression is 5, the maximum number.
The MIN function returns the minimum value in a list of values. This function is useful if you have a series of calculations and want to know which expression returns the lowest number. If a value is null, it is ignored.
The function syntax is:
MIN(a,b,...)
For example: MIN(1,2,3,4,5) The result of this expression is 1, the minimum number.
The NEG function returns 1 (true) if x is negative. Otherwise, it returns 0 (false).
The function syntax is:
NEG(x)
The NETOL is the reverse of the EQTOL function and is only available in the Data Editor. The tolerance level is the value that you determine, based on your business needs.
The function syntax is:
NETOL(x,y,tol)
The function returns TRUE if: (x-y)>tol.
NETOL(11,10,2) is FALSE because the difference between 11 and 10 is less than or equal to the tolerance value of 2.
NETOL(12,10,1) is TRUE because the difference between 12 and 10 is greater than the tolerance value of
The Next function is available in the Data Editor only. It returns the value of the field after the selected field. If no value is in this field, it returns the outOfRangeValue value.
The function syntax is:
Next(#field,outOfRangeValue)
The #field is the name of the field to which you want to apply the expression.
The outOfRangeValue is the number that is used if no number is in the next field. This number is optional. The default is 0. This function affects every column in the Data Editor for the specified field. If you want to use the value from only one of these fields, use the relative period function.
For example, suppose you have a Data Editor view with three columns: January, February, and March. The values for each column in the Blue row are:
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
If you create a field called NextColor that displays the results of the Next function for Blue and has an outOfRangeValueof 6, the expression for NextColor is:
Next(#Blue,6)
The Data Editor looks to the next column and returns its value. No columns are after March, so the Data Editor returns theoutOfRangeValue of 6.
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
| NextColor | 2 | 4 | 6 |
The NOT function returns FALSE() if x is not 0.
The function syntax is:
NOT(x)
The ORANGE function is available in the Data Editor only. It changes the color of a value to orange. Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
The function syntax is:
ORANGE()
For example, if you want the value of a field to be displayed in orange if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',ORANGE(),BLACK())
The OVERUNDER function returns FALSE() if x is between the lower boundary (lb) and the upper boundary (ub). If x is less than the lower boundary, the Data Editor calculates lb-x; if x is greater than the upper boundary, the Data Editor calculates ub-x.
This function is like the reverse of the BETWEEN function. You can use it to ensure that a value is not between two values that you specify.
The function syntax is:
OVERUNDER(lb,x,ub)
The PERCENT function returns the value of x divided by y and multiplied by 100. Ifx=0 ory=0, the function returns 0. The function syntax is:
PERCENT(x,y)
For example, if the value of Orders_filled is 10 and the value of Number_orders is 50, the following expression returns 20, indicating that 20 percent of the orders have been filled:
PERCENT(Orders_filled,Number_orders)
The PI function returns the value 3.141592. Use this function if you are performing a calculation that requires p.
The function syntax is:
PI()
The PINK function is available in the Data Editor only. It changes the color of a value to pink. Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
The function syntax is:
PINK()
For example, if you want the value of a field to be displayed in pink if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',PINK(),BLACK())
The POS function returns TRUE() if x is greater than 0.
The function syntax is:
POS(x)
The POWER function returns x to the power of y. This function is similar to the EXP(x) function, but it allows you to select the base and the exponent.
The function syntax is:
POWER(x,y)
For example, to find the volume of a cube, you must multiply the length by the width by the height. If the length, width, and height are 5, you could calculate the volume by entering:
POWER(5,3)
This result is 125.
The Prev function is available in the Data Editor only. It returns the value of the previous field, the one before the selected field.
The function syntax is:
Prev(#field,outOfRangeValue)
If no value in this field, it returns the value that you specify in the outOfRangeValue. The #field is the name of the field to which you want to apply the expression.
The outOfRangeValue is a number that is used if no number is in the previous field. This number is optional. The default is 0.
This function affects every column in the Data Editor for the specified field. If you want to use the value from only one of these fields, use the relative period function. For example, suppose you have a Data Editor view with three columns: January, February, and March. You have values for each column in the Blue row:
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
If you create a new field called PrevColor that displays the results of the Previous function for Blue and has an outOfRangeValue of 5, the expression for PrevColor is:
Previous(#Blue, 5)
The Data Editor looks to the previous column and returns its value. No columns are before January, so the Data Editor returns the outofRangeValue of 5.
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
| PrevColor | 5 | 3 | 2 |
The PRODUCT function multiplies the list of numbers in parentheses.
The function syntax is:
PRODUCT(a,b,...)
For example:
PRODUCT(1,2,3,4,5)
This expression results in 120.
For another example, suppose you have the production cost per unit of Yellow listed in the Yellow Cost field, and you have the number of units of Yellow that you produced listed in the Yellow Produced field. You can multiply the values of the two fields to arrive at the cost of producing Yellow with the following expression:
PRODUCT('Yellow Cost','Yellow Produced')
The RadToDeg function converts radians to degrees. The function syntax is:
RadToDeg(x)
For example, RadToDeg(PI()) returns the value 180.
The RAND function returns a random number between x and y. You could use this function if you want to model your capacity based on your demand, but you do not know what your demand is. The random numbers could be used to project demand.
The function syntax is:
RAND(x,y)
The RED function is available in the Data Editor only. It changes the color of a value to red. Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
The function syntax is:
RED()
For example, if you want the value of a field to be displayed in red if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',RED(),BLACK())
The RelOffset function is available in the Data Editor only. It returns a value from a relative position. The function syntax is:
RelOffset(#field,offset,outOfRangeValue)
The #field is the field name of the field that you select.
The offset is the number of fields relative to the field that you specify in #field. A positive number looks forward from this field. A negative number looks backward from this field.
The outOfRangeValue is the number that is used if no number is in the period found by the offsetvalue. This number is optional. The default is 0.
This function affects every column in the Data Editor for the specified field. If you want to use the value from only one of these fields, use the relative period function. For example, suppose you have a Data Editor view with three columns: January, February, and March, with these values for the Blue row:
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
Create a field called ColorOffset that displays the results of the RelOffset function for Blue that is offset by 2 and has an outofRangeValueof 0. The expression for ColorOffset is:
RelOffset(#Blue,2,0)
In the Blue row, the Data Editor looks two columns beyond January and finds the value of 4 in March so it returns the value of 4. It looks two columns ahead of February and March and finds that no available values are found because these columns do not exist. Therefore, it uses the outofRangeValueof 0. The Data Editor now looks like this:
| January | February | March | |
|---|---|---|---|
| Blue | 3 | 2 | 4 |
| ColorOffset | 4 | 0 | 0 |
The ROUND function rounds a number to the specified number of decimal places. The default is 0.
The function syntax is:
ROUND(x,num)
For example:
ROUND(123.7684,3)
This expression results in 123.768.5 If you want to round the value of your total Cost field to two decimal places, enter:
ROUND(Total_Cost,2)
The SEQUENCE function ensures that the values or expressions are performed in the order in which they appear and returns TRUE(). This function is useful if you have multiple entries of the Assign function in an inverse expression.
The function syntax is:
SEQUENCE(a,b,...)
The SIGN function returns -1 if x is less than 0, returns 0 if x is equal to 0, or returns 1 if x is greater than 0.
The function syntax is:
SIGN(x)
The SIN function returns the sine of x, where x is measured in radians.
The function syntax is:
SIN(x)
The SQRT function returns the positive square root of a number.
The function syntax is:
SQRT(x)
For example, the expression SQRT(15) results in 3.87.
The SUM function returns the sum of a list of values or expressions.
The function syntax is:
SUM(a,b,...)
For example, SUM(1,2,3,4,5) returns 15.
The SUMPERIODS function, available in Strategic Network Optimization but not the Data Editor, finds the sum (over all periods) of a numeric field. You must precede the field name with a # character.
The function syntax is:
SUMPERIODS(#field)
For example, suppose you want to quickly calculate the total demand for all periods in a model in a particular StorageDemand node. In any field other than the Demand field of the StorageDemand properties window, you can enter the following:
SUMPERIODS(#Demand)
The TAN function returns the tangent of x, where x is measured in radians.
The function syntax is:
TAN(x)
The TRUE function returns the value 1.
The function syntax is:
TRUE()
The VIOLET function is available in the Data Editor only. It changes the color of a value to violet.
The function syntax is:
VIOLET()
Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
For example, if you want the value of a field to be displayed in violet if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',VIOLET(),BLACK())
The YELLOW function is available in the Data Editor only. It changes the color of a value to yellow. The function syntax is:
YELLOW()
Color functions are part of the highlighting option. When you use them, you must enter your expression in the Highlight Expression Editor.
For example, if you want the value of a field to be displayed in yellow if the inventory level goes below the safety level, enter:
IF('Inventory Level'<'Safety Level',YELLOW(),BLACK())
The ZERO function returns 1 if x is equal to 0. Otherwise, it returns 0.
The function syntax is:
ZERO(x)