This appendix provides reference information for the operators and functions that are allowed in client expressions.
This chapter covers the following topics:
This appendix provides reference information for the operators and functions that are allowed in client expressions.
Edit-lock expressions must evaluate to true or false values.
In these reference sections, square brackets indicate optional parts of the syntax.
You can use the following operators in a client expression:
+
-
*
/
()
[]
<
<=
<>
=
>
>=
And
Else
In
Not
Or
Then
When
Precedence of calculations follows standard algebraic rules.
Finally, to specify a series, use either of the following syntaxes:
series_name
series_name[relative-time-bucket]
For example: Sales [ -1] refers to the previous period(column). Sales [ 1] refers to the next period (column). [0] is not allowed.
Returns the absolute value of a number.
Syntax
Abs (argument)
The value of argument must be either numeric or null.
If argument is numeric, the function returns the absolute value of argument.
If argument is null, the function returns null.
Tests the values of a series or expression and returns values based on the results of the test. If more than one WHEN clause matches the given input, the function returns the result corresponding to the first matching one.
Syntax
Case ( test WHEN value1 THEN result1 [ WHEN value2 THEN result2] [ additional WHEN-THEN clauses] [ ELSE else ] )
The square brackets indicate optional parts of the syntax. The arguments are as follows:
test is the series or expression whose values you want to test. You can use a column name or a column number preceded by a pound sign (#).
value1, value2, and so on are possible values that test can have. Each value can be any of the following:
A single value
A list of values separated by commas (for example, 2, 4, 6, 8)
IS followed by a relational operator and comparison value (for example, IS>5)
Any combination of the preceding expressions, separated by commas (for example, 1,3,5,7,9, IS>42)
In this case, the function implicitly behaves as if the expressions were combined by a logical OR.
result1, result2, and so on are the results to return for the possible values. For example, if test evaluates to value1, then the function returns result1. All returned values must have the same data type.
else specifies the value to return if test does not equal any of the given cases (value1, value2, and so on). The default for else is null.
Examples
The following expressions are valid uses of Case:
Case ( Input1 When is > 1 Then 10 When 2 Then 20 Else 30 ) Case ( Input1 When is < 10 Then 5 When is < 20 Then 50 ) Case ( Input1 When is > 100, is < 0 Then 5 Else 30 ) Case ( Input1 When is > 1 Then 10 )
See also
“If”
Returns the number of the worksheet row that currently has the focus, that is, the worksheet row that the user has selected.
This function is easiest to use in color expressions.
Within the main client expression for a series, you can use this function to find the relative position of a row, in relation to another row. For example, you can use it indirectly to find the last row number.
Note: Remember that apart from the color expression and the edit-lock expression, the worksheet does not reevaluate client expressions until a value changes in the worksheet. That is, the action of moving the cursor does not force the main client expressions to be reevaluated.
Note: This means that if you use this function within the main client expression for a series, the expression should also refer to a series that the user will change.
If the function fails, it returns 0.
Syntax
CurrentRow ()
Notes
When used in a general client expression (that is, neither a color expression nor an edit-lock expression), this function always returns 1.
When used in a color or edit-lock expression, this function returns the number that corresponds to the row that currently has the focus, that is, the row that the user has currently highlighted.
You can use this function in an edit-lock expression, but the GetRow function is a better choice. For example, consider the following possible edit-lock expressions:
CurrentRow() < ForecastFirstRow() GetRow() < ForecastFirstRow()
Both expressions make it impossible to edit the series for the rows before the start of the forecast. However, if you use the former expression, all the cells will appear editable if the user selects a row before the start of the forecast, which would be very confusing.
See also
“ForecastFirstRow” “GetRow”
Given a string argument, returns a date. This function works only in the desktop.
Syntax
Date (string)
See also
“Day” “Month” “Today” “Year”
Returns an integer indicating the day of the month of the given date value.
Syntax
Day (argument)
The value of argument must be either a date or null.
If argument is a date, the function returns an integer representing the day (1–31) of the month in that date.
If argument is null, the function returns null.
Returns the number e raised to the specified power.
Syntax
Exp (argument)
The value of argument must be either numeric or null.
If argument is numeric, the function returns the number e raised to the power of argument.
If argument is null, the function returns null.
Example
This expression returns 7.38905609893065:
Exp(2)
These statements convert a natural logarithm (base e) back to a regular number. When executed, Exp sets value to 200:
double value, x = log(200)
value = Exp(x)
Returns the number of the row in the current worksheet where the forecast begins. It refers to the batch forecast.
Syntax
ForecastFirstRow()
See also
“CurrentRow” “GetRow”
Periods of supply. Checks the period of the first arguments against the period (from the next period) of the second argument. This function works only on the forecast data.
Note: You cannot use this function in color expressions.
Syntax
Fpos (series1,series2)
Each argument should be a series.
Examples
If (GetRow() >= ForecastFirstRow() Fpos(SupplyTinv, SupplyTinvFinalFcst) Null)
The section that uses “GetRow() >= ForecastFirstRow()” is essential because the expression works only in forecast.
Date | Inventory (SupplyTinv) | Forecast (SupplyTinvFinalFcst) | Third Series with FPOS |
---|---|---|---|
January | 12 | 5 | 2.5 |
February | 5 | ||
March | 4 | ||
April | 6 |
Fpos = 5 + 4 + 6*0.5 = 2.5. The inventory (12) will cover the forecast for 2.5 periods.
Returns a series that adds multiple future consecutive items from a given series. The second argument, either a number or another series, specifies the number of time periods to use for each sum.
Note: You cannot use this function in color expressions.
Syntax
Fsum (series , count)
The arguments are as follows:
Series should be an actual series name.
Count should be either a numeric series or an actual integer between 1 and 15, inclusively. If count is more than 15, the function returns null.
The parameters can be only the names of data series or actual values. It is not permitted to enter other functions or [ ] brackets inside the FSUM function.
Examples
Fsum (Series1, Series2)
Date | Series1 | Series2 | =Fsum | Note about this entry |
---|---|---|---|---|
Jan | 100 | 3 | 360 | Series2 for Jan is 3, so the function finds the next 3 time periods within Series 1; that is, 110, 130, and 120. The sum of those numbers is 360. |
Feb | 110 | 2 | 250 | Series2 for Feb is 2, so the function finds the next 2 time periods within Series 1; that is, 130 and 120. The sum of those numbers is 250. |
March | 130 | 2 | 290 | Series2 for March is 2, so the function finds the next 2 time periods within Series 1; that is, 120 and 170. The sum of those numbers is 290. |
April | 120 | 2 | ... | |
May | 170 | 3 | ... | |
April | ... |
Returns the number of the worksheet row. You generally use this function in edit-lock expressions that lock worksheet rows depending on position.
If the function fails, it returns 0.
Syntax
GetRow ( )
See also
“CurrentRow” “ForecastFirstRow”
Returns an integer that indicates the relative level of the summary row where this function is used.
Note: This function is available only for use within the summary row of a series.
Syntax
GetWorksheetLevelNumber ( )
You use this function to achieve different kinds of summaries for a series in different contexts in a given worksheet. Specifically, when a worksheet uses one or more levels on the x-axis, the worksheet table includes intermediate summary rows, for example:
By default, the final summary row and all the intermediate summary rows are calculated in exactly the same way. The GetWorksheetLevelNumber function provides a way to distinguish each summary row, so that you can create a different summary functions as needed, at each of those levels.
To determine the relative aggregation level of the summary rows, the function considers the layout of the worksheet. The following figure relates the summary levels to the x-axis layout:
For level 1 in the worksheet, GetWorksheetLevelNumber returns 1, and so on. If there are n levels on the x-axis of the worksheet, the function returns n+1 for the final summary.
Example
Suppose that you want to display only the intermediate summary but not the final summary, as follows:
To achieve this, you could use a summary row expression of the following form:
If ( GetWorksheetLevelNumber() =1, SummaryMax ( Sample Max ) , Null Value )
Note: If you configure a series with a context-sensitive summary row like this, be careful to use that series only within worksheets that have the appropriate layout. You may want to add a usage note to the series hint message to guide users.
Tests a true/false expression and returns one of two possible values based on the results of the test.
Syntax
IF (test , trueresult) IF (test , trueresult, falseresult)
The arguments are as follows:
test is the series or expression that has true or false values.
trueresult is the result to return if test equals true.
falseresult is the result to return if test equals false. The default for falseresult is null. That is, if you do not specify this argument, the function returns null.
Note: Within a color expression, only the first syntax variant is allowed. That is, a color expression cannot include falseresult.
Examples
This expression returns 7 if Retail_history is greater than Retail_model; otherwise it returns Demand:
If (Retail_History > Retail_Model, 7, Demand)
See also
“Case”
Returns true or false depending on whether a given series has been edited since the last time data was saved.
Syntax
Is_Modified (series)
Here series should be an editable series. The function does not detect whether a calculated series has been changed by having its inputs edited.
Examples
The following expression returns the Inventory series if Pseudo has been modified and returns the Safety series if Pseudo has not been modified:
If (Is_Modified(Pseudo), Inventory, Safety )
Returns true or false depending on whether a given series equals null.
Syntax
IsNull(series)
Returns the remainder (modulus) of a division operation.
Note: The results of this function take slightly longer to display than for other functions.
Syntax
Mod (argument1, argument2 )
The value of argument1 and argument2 must be either numeric or null.
If both argument1 and argument2 are numeric, the function returns the remainder (modulus) of a division operation. Specifically, it returns the following result:
argument2 - round(argument2/argument1) where round(argument2/argument1) equals argument2/argument1 rounded to the nearest integer.
The returned value is the data type of whichever argument has the more precise data type.
If argument2 or argument2 is null, the function returns null.
If argument2 is 0, the function returns null.
Examples
Mod(20, 6) returns 2
Mod(25.5, 4) returns 1.5
Mod(25, 4.5) returns 2.5
Returns an integer indicating the month of the given date value.
This can be used to show or use dates in calculations. Also, a planning process can be maintained, while the locking expression is based dynamically on dates. For example, override is allowed only in the first two weeks of the month.
Syntax
Month (argument)
The value of argument must be either a date or null.
If argument is a date, the function returns an integer (1 to 12) representing the month in that date.
If argument is null, the function returns null.
Returns the number pi multiplied by a specified number.
Syntax
Pi (argument)
The value of argument must be either numeric or null.
If argument is numeric, the function returns the number pi multiplied by argument.
If argument is null, the function returns null.
The function returns -1 if an error occurs.
Examples
You can use this function to convert angles to and from radians. For example, because pi equals 180 degrees, you can convert 60 degrees to radians as follows:
60 * 180 / pi(1)
Returns a random integer between 1 and a specified upper limit.
Note: This function does not generate true random numbers. If you repeatedly call this function, you will receive a pseudo random sequence.
Syntax
Rand ( argument )
The value of argument must be either numeric or null. If numeric, the argument must have a value between 1 and 32767, inclusive.
If argument is a number greater than or equal to 1, the function returns a random integer between 1 and argument, inclusive.
If argument is null, the function returns null.
Examples
The following expression returns a random whole number between 1 and 10:
Rand(10)
Returns a number rounded to a specified number of decimal places.
Syntax
Round (argument1, argument2)
The value of argument1 and argument2 must be either numeric or null. If numeric, argument2 should be a non negative integer between 0 to 18, inclusive.
If argument1 is numeric and argument2 is a non negative integer, the function returns the value of argument1, rounded to the number of decimal places specified by argument2.
If argument1 or argument2 is null, the function returns null.
If the function fails, it returns null.
Examples
The following expression returns 9.62:
Round(9.624, 2)
The following expression returns 9.63:
Round(9.625, 2)
The following expression returns 9.600:
Round(9.6, 3)
The following expression returns -9.63:
Round(-9.625, 2)
see also
“Truncate”
Returns the numbers of rows in the worksheet where this function is used.
Syntax
RowCount ( )
See also
“ForecastFirstRow” “GetRow”
Returns the square root of a non negative number.
Syntax
Sqrt (argument)
The value of argument must be either a non negative number or null.
If argument is a non negative number, the function returns the square root of argument.
If argument is null, the function returns null.
Examples
This expression returns 1.414213562373095.
Sqrt(2)
This expression results in an error at execution time.
Sqrt(-2)
Returns the average value of the displayed rows of the specified series.
Note: This function is available only for the summary row of a series.
Syntax
SummaryAvg( argument )
Here argument is the name of a series that has a numeric value.
Any null value is treated as zero.
Returns the total count of the displayed rows of the specified series.
Note: This function is available only for the summary row of a series.
Syntax
SummaryCount( argument)
Here argument is the name of a series that has a numeric value.
Returns the maximum value of the displayed rows of the specified series.
Note: This function is available only for the summary row of a series.
Syntax
SummaryMax( argument )
Here argument is the name of a series that has a numeric value.
Any null value is treated as zero.
Returns the minimum value of the displayed rows of the specified series.
Note: This function is available only for the summary row of a series.
Syntax
SummaryMin( argument )
Here argument is the name of a series that has a numeric value.
Any null value is treated as zero.
Returns the sum of the displayed rows of the specified series.
Note: This function is available only for the summary row of a series.
Syntax
SummarySum( argument )
Here argument is the name of a series that has a numeric value.
Any null value is treated as zero.
Returns the weighted average of the displayed rows.
Note: This function is available only for the summary row of a series.
Syntax
SummaryWavg( argument1 , argument2 )
Here argument1 and argument2 are the name of series that have a numeric value. Tis function performs a weighted average of the values in argument1, using the values in argument2 as the weights.
Any null value is treated as zero.
Returns the current system date.
This can be used to show or use dates in calculations. Also a planning process can be maintained, while the locking expression is based dynamically on dates. For example, override is allowed only in the first two weeks of the month, and so on.
Syntax
Today ()
Returns a number truncated to a specified number of decimal places.
Syntax
Truncate ( argument1, argument2)
The value of argument1 and argument2 must be either numeric or null. If numeric, argument2 should be a non negative integer between 0 to 18, inclusive.
If argument1 is numeric and argument2 is a non negative integer, the function returns the value of argument1, truncated to the number of decimal places specified by argument2.
If argument2 or argument2 is null, the function returns null.
If the function fails, it returns null.
Examples
The following expression returns 9.2:
Truncate(9.22, 1)
The following expression returns -9.2:
Truncate(-9.29, 1)
See also
“Round”
Returns a four-digit integer indicating the year of the given date value.
This can be used to show or use dates in calculations. Also a planning process can be maintained, while the locking expression is based dynamically on dates. For example, override is allowed only in the first two weeks of the month, and so on.
Syntax
Year ( argument )
The value of argument must be either a date or null.
If argument is a date that includes a four-digit year, the function returns a four-digit integer representing the year.
If argument is a date that includes a two-digit year, the function returns a four-digit integer representing the year, as follows:
If the two-digit year is between 00 to 49, Demantra assumes 20 as the first two digits.
If the two-digit year is between 50 and 99, Demantra assumes 19.
If argument is null, the function returns null.
If an error occurs, the function returns 1900.
Demantra handles years from 1000 to 3000 inclusive. If your data includes date before 1950, such as birth dates, always specify a four-digit year so that Year and other functions, such as Sort, interpret the date as intended.
Z_val is used in safety stock calculation. Given a specified service level, this function returns a value to use in the safety stock calculation, by looking up values in a table.
Syntax
Z_Val (argument)
The value of argument must be either numeric or null.
If argument is less than or equal to 0, the function returns null.
If argument is greater than 0 but less than the largest max_value, the function returns the z_val from the following table.
If argument is greater than the largest max_value, the function returns null.
If argument is null, the function returns null.
Z_Val Table
If the argument is... | The function returns this z_val... | |
---|---|---|
greater than the min_level | and less than or equal to the max_level | |
0 | 0.85 | 1.04 |
0.85 | 0.86 | 1.09 |
0.86 | 0.87 | 1.13 |
0.87 | 0.88 | 1.18 |
0.88 | 0.89 | 1.23 |
0.89 | 0.9 | 1.282 |
0.9 | 0.91 | 1.34 |
0.91 | 0.92 | 1.41 |
0.92 | 0.93 | 1.48 |
0.93 | 0.94 | 1.56 |
0.94 | 0.95 | 1.645 |
0.95 | 0.96 | 1.75 |
0.96 | 0.97 | 1.88 |
0.97 | 0.98 | 2.06 |
0.98 | 0.99 | 2.33 |
0.99 | 0.995 | 2.576 |
0.995 | 0.999 | 3.09 |
0.999 | 0.9995 | 3.291 |
0.9995 | 0.99995 | 3.891 |
0.99995 | 0.999995 | 4.417 |
Example
If we select a service level of 95.5% then the expression will look at the table at the following line, because this lies between 0.95 and 0.96 (the minimum and maximum values in the range). A z_val value of 1.75 is returned.
min_level | max_level | z_val |
---|---|---|
0.95 | 0.96 | 1.75 |