Client Expression Functions and Operators

This appendix provides reference information for the operators and functions that are allowed in client expressions.

This chapter covers the following topics:

• About This Reference
• Operators in Client Expressions
• Abs Function
• Case
• CurrentRow
• Date
• Day
• Exp
• ForecastFirstRow
• Fpos
• Fsum
• GetRow
• GetWorksheetLevelNumber
• If
• Is_Modified
• IsNull
• Mod
• Month
• Pi
• Rand
• Round
• RowCount
• Sqrt
• SummaryAVG
• SummaryCount
• SummaryMax
• SummaryMin
• SummarySum
• SummaryWAVG
• Today
• Truncate
• Year
• Z_Val

About This Reference

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.

Operators in Client Expressions

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.

Abs Function

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.

Case

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”

CurrentRow

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”

Date

Given a string argument, returns a date. This function works only in the desktop.

Syntax

• Date (string)

See also

• “Day” “Month” “Today” “Year”

Day

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.

Exp

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)

ForecastFirstRow

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”

Fpos

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.

Fsum

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

GetRow

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”

GetWorksheetLevelNumber

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.

If

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”

Is_Modified

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 )

IsNull

Returns true or false depending on whether a given series equals null.

Syntax

• IsNull(series)

Mod

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

Month

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.

Pi

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)

Rand

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)

Round

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”

RowCount

Returns the numbers of rows in the worksheet where this function is used.

Syntax

• RowCount ( )

See also

• “ForecastFirstRow” “GetRow”

Sqrt

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)

SummaryAVG

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.

SummaryCount

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.

SummaryMax

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.

SummaryMin

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.

SummarySum

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.

SummaryWAVG

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.

Today

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 ()

Truncate

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”

Year

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

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