# List of functions

The primary purpose of functions is to extend the capabilities of Envision whenever the basic language syntax would not be sufficient. Below, we list the functions supported by Envision.

- Function syntax
- Mathematical functions
- Text functions
- Calendar functions
- Ranking functions
- Graph functions
- Algebra of ranvars
- Algebra of zedfuncs
- Table creation functions
- Advanced functions
- cumsub(G.Item, G.Stock, G.Quantity, G.Rank)
- cumsub.fallback(G.Item, G.Stock, G.Fallback, G.Quantity, G.Rank)
- extend.distinct(…)
- forex(value, Origin, Destination, date)
- forex.last(Origin, Destination)
- hash(value)
- isCurrency(currencyCode)
- mkuid(X, offset)
- solve.moq(…)
- pricebrk.m(…)
- pricebrk.f(…)
- priopack(V, MaxV, JT, B) by Group sort Order
- ranvar.segment(…)
- ranvar.periodicr(…)
- forest.regress(…)
- smudge(values, present) by Group sort Order
- stockrwd.m(D, AM), stockrwd.s(D), stockrwd.c(D, AC)

## Function syntax

The general syntax for functions is:

```
x = fun(arg1, arg2, arg3, ..., argN) by [Group] sort [Order]
```

`fun`

will be replaced by the name of the function and `arg1, arg2, arg3, ..., argN`

will be replaced by the arguments passed to the function. All functions operate on vectors.

Only certain functions support the extra options `by`

and `sort`

. While the syntax is similar to the syntax of the *aggregators*, those functions are not aggregators. The `by`

option is optional, and when it is omitted, it is equivalent to `by 1`

. Both `Group`

and `Order`

support *tuples* of vectors, that is, multiple vectors delimited by commas. For example:

```
R = rank() by [A, B] sort [Items.1]
```

## Mathematical functions

`abs(T.X: number) -> number`

: absolute value, similar to the ABS function in Excel.`arground(T.X: number) -> number`

: answers the question at what point the trailing 1 becomes too small to be worth printing, or worse, becomes smaller than the precision of floating-point numbers. The rule is as follows: if we find`000`

or`999`

in the decimal expansion of a number, then it should be rounded for display.`arground(5.3001)`

and`arground(5.2998)`

return 4, suggesting that we keep all 4 digits after the decimal point, but`arground(5.30001)`

and`arground(5.29998)`

return 1, suggesting that we present these numbers as 5.3. See also`precision`

. Thus, 5.3001 stays 5.3001; 5.30001 would become 5.3; 5.2998 stays 5.2998; 5.29998 becomes 5.3.`ceiling(T.X: number) -> integer`

: rounds up to the nearest integer, similar to the CEILING.MATH function in Excel.`exp(T.X: number) -> number`

: exponential function, similar to the EXP function in Excel.`floor(T.X: number) -> integer`

: rounds down to the nearest integer, similar to the FLOOR.MATH function in Excel.`log(T.X: number) -> number`

: natural logarithm, similar to the LN function in Excel.`max(a1: number, a2: number, ...) -> number`

: variadic, similar to the MAX function in Excel.`min(a1: number, a2: number, ...) -> number`

: variadic, similar to the MIN function in Excel.`norm(T.X: number, T.G: any) -> number`

: or min-max normalization computes the`min`

and`max`

values of each group implicitely defined by`T.G`

, then rescales the values into a range of $[0,1]$. If`min == max`

, all values become $0$.`norminv(x: number)`

: applied to a probability, returns the inverse of the normal cumulative distribution function, with mean at $0$ and a standard deviation of $1$. Similar to the NORM.S.INV function in Excel.`percent(T.X: number, T.G: any) -> number`

: returns the value`X`

divided by the sum of all the values within the group implicitely defined by`T.G`

.`pow(T.X: number, T.Y: number) -> number`

: similar to the POWER function in Excel. Envision also supports the power operator`X ^ Y`

, which performs the same calculation.`random.normal(T.Seed: integer) sort T.V : any -> number`

: returns pseudo-random values drawn from a normal distribution with $\mu=0$ and $\sigma=1$ over the table`T`

starting from the specified seed.`random.uniform(T.Seed: integer) sort T.V : any -> number`

: returns pseudo-random values drawn uniformly over $[0,1]$ over the table`T`

starting from the specified seed.`ratio(T.X: number, T.Y: number) -> number`

: expresses that the value is the size ratio of a subset of size X in a set of size Y, which means that it will issue a warning if X < 0, Y < 0, or X > Y (all of which are invalid when computing a ratio). The common example would be`ratio(qty,total)`

which would express that the value is the size ratio of a subset of size quantity in a set of size total. The`ratio`

function also has the following behavior:`ratio(0,0) = 0`

, which is the ‘official’ meaning of a ratio.`round(T.X: number, digits: integer) -> integer`

: rounds a number to the nearest even number, so`round(0.5) == 0`

,`round(1.5) == 2`

, and`round(4.5) == 4`

. This is called bankers’ rounding. The second argument is optional and represents the number of digits that are intended to be kept. Thus,`round(X, D)`

rounds to`D`

digits after the decimal point and`round(X) == round(X, 0)`

. Following this logic,`round(4.33, 0) == 4`

,`round(4.33, 1) == 4.3`

, and`round(4.33, 2) == 4.33`

. If a non-integer or out-of-range value is provided as number of digits, its approximation down to the nearest integer between 0 and 15 is taken instead.`round.next(T.X: number) -> number`

: rounds a number following ‘away from zero’ rounding convention (rounds to the next ‘integer’). Thus,`round.next(0.5) == 1`

and`round.next(1.5) == 2`

. Rounding numbers is a mathematical operation, rather than a presentational setting.`sqrt(T.X: number) -> number`

: similar to the SQRT function in Excel.`tanh(T.X: number) -> number`

: similar to the TANH function in Excel. Returns hyperbolic tangent of`X`

, given by $\tanh(x) = (e^x - e^{-x})/(e^x + e^{-x})$.`zscore(T.X: number, T.G: any) -> number`

: similar to the STANDARDIZE function in Excel. The z-score of a value indicates how many standard deviations from the mean your score is within the group implicitely defined by`T.G`

. If the standard deviation is equal to zero, so is the z-score.

## Text functions

`concat(text1, text2, ...)`

: is a variadic function that concatenates the text values passed as argument, similar to the CONCAT Excel function.`contains(text, pattern)`

: returns`true`

if the text contains an occurrence of the pattern.`endswith(text, pattern)`

: returns`true`

if the text ends with an occurrence of the pattern.`field(text, separator, index)`

: returns the nth field (zero-indexed) in a text value that contains multiple sub-strings separated by a specified separator. Ex:`field("a-b-c-d-", "-", 2) == "c"`

. This function is intended to facilitate parsing values that have been concatenated within a single table column.`field.r(text, separator, index)`

: like the`field()`

function, but counts the separator occurrences from the right. Ex:`field.r("a-b-c-d-", "-", 0) == ""`

.`indexof(text, pattern)`

: returns the index of the first occurrence of the pattern within the text, or $-1$, if no such occurrence is found.`lowercase(text)`

: returns the lowercase variant of the text.`padleft(text, pad, size)`

: prepends`pad`

to`text`

enough times to reach a length of`size`

, e.g.,`padleft("1234","0",7)`

returns`"0001234"`

.`parsedate(text, format)`

: converts the text into a date using the specified format. The format is optional. When the format is omitted, the date is parsed based on the date format auto-detection behavior of Envision. When the format is provided, the date is parsed against the format expectation. See custom date format string for the details of the format syntax. If a date cannot be parsed, the date 2001-01-01 is returned instead.`parsenumber(text)`

: converts the text into a number. The parser leverages the number format auto-detection behavior of Envision. If the number cannot be parsed, zero is returned instead.`parsenumber(text, thousandSeparator, decimalSeparator)`

: converts the text into a number using the specified seperators. Ex:`parsenumber("2525.00",".",",")`

returns a number equal to`252500`

.`parsetime(text, format)`

: converts a time of the day into a fraction between 0 and 1, representing a fractional day. The format is optional. When the format is not specified, the default value`yyyy-MM-dd HH:mm:ss`

is used. Envision is using the .NET Custom Time Format.`printtime(text, format)`

: is a reverse function to`parsetime()`

. It takes a fraction of a day`f : number (0<=f<=1)`

and returns a string formated as specified, e.g.,`mytimestamptoconcat := printtime(0.25,"HH:mm:ss") = "06:00:00"`

. The`parsetime()`

function supports the .NET Custom Time Format specifiers for time.`replace(text, pattern, replacement)`

: replaces in the text all occurrences of the pattern by the replacement. This function is similar to the SUBSTITUTE function of Excel, omitting the`instance_num`

argument.`sliceSearchUrl(number, text)`

: returns the URL, encoded as a text value, that points to a specific slice within a specific dashboard. The dashboard is identified by its project identifier passed as the first argument. The slice is identified by the second argument which acts as a query performed on the dashboard. If there are multiple matching slices, the first slice (name-wise) is selected. The returned text value that contains an URL can be rendered as a link through the StyleCode element {text: “link”}`startswith(text, pattern)`

: returns`true`

if the text starts with an occurrence of the pattern.`strlen(text)`

: returns the length of the text argument.`substr(text, start, count)`

: the start position is defined by`start`

. If negative, it’s an offset from the*end*of the string, otherwise it’s an offset from the*start*of the string. The length of the returned substring is defined by`count`

, treated as 0 if`count < 0`

. If the segment start or length places it partially or completely outside the string, e.g.`substr("A", 2, 1)`

, then the segment is clipped to fit. Function`substr(text, start)`

is defined as`substr(text, start, <infinity>)`

.`uppercase(text)`

: returns the uppercase variant of the text.

It is important to keep in mind that text functions are case-sensitive. The `~`

operator makes all text comparisons case-insenstitive, e.g., `contains(foo, "bar")`

is case-sensitive, whereas `contains(~foo, ~"bar")`

is case-insensitive.

In addition, there exist functions that enable to transform any element into text, e.g.,

`to.text(date or number)`

: is a pure function that transforms an element (date, number) passed as argument into a textual value.

## Calendar functions

`"\{myDate:yyyy-MM-dd}"`

: custom date formatting through string interpolation. The date format is specified by the token found after the semi-colon. More details about date format strings.`chineseYear(date)`

: returns the current year in the Chinese calendar.`chineseYearEnd(date)`

: returns the last day of the current Chinese Year.`chineseYearStart(date)`

: returns the first day of the current Chinese year.`date(y, m, d)`

: returns a date built from the year, month and day passed as an arguments. The arguments`y`

,`m`

and`d`

are expected to be numbers.`daynum(date)`

: returns the applicable day of the month (1-31) for the date passed as an argument.`isoYear(date)`

: returns the applicable year for the week which the date in the argument belongs to. All days of a given week belong to the same`isoYear`

and weeks begin on Mondays.`monday(date)`

: returns the first Monday that precedes the date (inclusive). You can use`monday(date)`

to obtain the Excel WEEKDAY function:`WEEKDAY(date) = (date - monday(date) + 1) mod 7 + 1`

and`WEEKDAY(date,3) = date - monday(date)`

.`month(date)`

: returns the index of the month associated to the date, counting the number of months since January 1st 2001.`monthend(date)`

: returns the last day of the month that contains the date passed as an argument.`monthnum(date)`

: returns the applicable month (1-12) for the date passed as an argument.`monthstart(date)`

: returns the first day of the month that contains the date passed as an argument.`today(timezone)`

: returns the current wall-time date with the time-zone passed as an argument and expressed as the difference in hours to UTC.`weeknum(date)`

: similar to the ISOWEEKNUM Excel function. Weeks start on Mondays.`year(date)`

: returns the applicable year for the date passed as an argument. Similar to the YEAR function in Excel.`yearEnd(date)`

: returns the last day of the year for the current year.`yearStart(date)`

: returns the first day of the year for the current year.

## Ranking functions

`argfirst() by [Group] sort [Order]`

: returns`true`

for the first value of the group according to the ordered values (the ordering). The group is optional. When the group is provided, the function returns`true`

once per group. An overload`argfirst() by [Group] sort [Order] where condition`

is also provided for convenience. When the`where`

option is used, it may result in groups where no`true`

value is present, because the condition was`false`

for the entire group.`arglast() by [Group] sort [Order]`

: same as`argfirst()`

, but returns`true`

for the last value.`assoc.quantity(l, lq, r, rq) by [] sort []`

: associates quantities from two sets connected by edges. Given two sets, left and right, represented by the`l`

and`r`

groupings, the function takes the list of (left, right) edges connecting the two sets. Each set element has an associated initial quantity`same(lq) by l`

or`same(rq) by r`

. This quantity decreases over the course of the algorithm. Edges are visited in the specified order. The return value for the edge is set to the minimum of the two nodes’ current quantity (the associated quantity), and that value is then subtracted from both quantities. In consequence, once a node’s quantity reaches zero, it no longer contributes to the algorithm. If a ‘by’ grouping is provided, each group is treated independently from all others.`cumsum(N) by [Group] sort [Order]`

: returns the cumulative sum of the numbers`N`

according to the increasing ranks. The group is optional. When the group is specified, it is used to perform a local cumulative sum for each group.`fifo(V, T.D, T.Q)`

: helper for FIFO inventory analysis. Returns the unsold inventory quantity as a vector of`T`

. The vector`V`

contains the total stock. The table`T`

contains the purchase orders.`T.D`

contains the dates, and`T.Q`

contains the purchase quantities. The function computes the unsold quantities by playing the purchase orders backwards in time. See also FIFO inventory method.`rank() by [Group] sort [Order]`

: returns the ranks of the numbers with no tie. Similar to the RANK.EQ function in Excel, except that all numbers get a distinct rank (tie-breaks are arbitrary). The group is optional. When the group is provided, it is used to perform local ranks for each group.`rank(N, Group, S)`

: a more advanced ranking function that is quite different from the other`rank()`

overloads. The purpose of this overload is to support the generation of a prioritized purchase list. In particular,`rank(N, Group, S)`

cannot be re-expressed as a simple expression of sorting and grouping. This is a two-stage imperative algorithm. In the first stage, values are grouped by`Group`

into*stacks*, with each stack ordered by ascending`S`

. In the second stage, the algorithm selects the highest value of`N`

among the top elements of all stacks, pops that element, assigns it a rank (starting at 1), and repeats until all stacks are empty.`rankd(N) by [Group]`

: returns the ranks of the numbers`N`

; identical numbers get identical ranks. Similar to the RANK.EQ function in Excel.

## Graph functions

`canonical(A, B)`

: returns the canonical representative for each`A`

value. From a practical perspective, this function is used to deal with code replacement (ex: SKU code replacement). For example,`canonical(OldSku, NewSku)`

would return the latest SKUs available for each item, recursively performing the replacements. See also`nonCanonical()`

.`connected(A, B) by [Group]`

: considers the undirected graph described by all edges`(A,B)`

, then returns for each node`A`

the name of the smallest node in`A`

's connected component. Here,*smallest*means having the smallest name, in terms of string comparison. The group is optional. When the group is specified, the dataset is first partitioned against the specified groups.`nonCanonical(A, B)`

: returns`true`

whenever a canonical representative cannot be computed for the`A`

. This happens when circular paths or branching paths get detected.

## Algebra of ranvars

In the following, given an integer-valued random variable $X: \mathbb{\Omega} \to \mathbb{Z}$, its associated probability distribution $\mathbf{P}[X = k]$ will be referred to as a *ranvar*. Additional information on the algebra of ranvars is provided in the algebra of ranvars section.

### Parametric ranvars

Parametric ranvars can be generated through functions which take a number as an argument - the parameter.

`dirac(n)`

: returns a ranvar zero valued everywhere except for`n`

where the function is valued $1$.`exponential`

$(\lambda)$ : returns the exponential distribution of parameter $(\lambda)$.`negativebinomial`

$(\mu,\sigma)$ : returns the negative binomial distribution of mean $\mu$ and standard deviation $\sigma$. If the standard deviation is smaller than the square root of the mean, then a Poisson distribution with mean $\mu$ is returned instead.`poisson`

$(\lambda)$ : returns the Poisson distribution of parameter $(\lambda)$.`ranvar.uniform(n)`

: returns the ranvar represented by the function $k \mapsto \frac{1}{|n| + 1}$ on the segment [0;n] (if $n \geq 0$) or [n;0] (if $n < 0$) and 0 elsewhere.`ranvar.uniform(m, n)`

: returns the ranvar represented by the function $k \mapsto \frac{1}{n + 1 - m}$ on the segment [m;n] and 0 elsewhere. We assume that $m < n$, an error is thrown if $m > n$.`loglogistic`

$(\alpha, \beta)$ : The log-logistic distribution is a continuous probability distribution for a non-negative random variable. It has two parameters : the scale`alpha`

$\alpha$ and the shape`beta`

$\beta$. The distribution is defined only if $\alpha > 0$ and $\beta > 0$. The returned ranvar reflects the loglogistic distribution by returning probabilities integrated over each integer segment.

### Non-parametric ranvars

`distrib(Id, G.Probability, G.Min, G.Max)`

: function that returns, for each`Id`

, the ranvar defined by a list of buckets, where each bucket has left and right inclusive boundaries and a value for the bucket.`ranvar(T.X)`

: aggregator that returns the empirical ranvar resulting from the observations stored in the vector`T.X`

.`ranvar.segment(...)`

: advanced function that generates an empirical ranvar resulting from a sliding time-window (more details in the following).`ranvar.periodicr(...)`

: more general version of`ranvar.segment(...)`

(more details in the following).

### Indicators on ranvars

Numeric indicators about ranvars can also be obtained in Envision. If `X`

and `Y`

are independent random variables, with ranvars (probability distributions) `R`

and `S`

respectively, and `a`

and `b`

are integers,

`crps(R, a)`

: returns the Continuous Ranked Probability Score (CRPS).`crps(R, S)`

: returns the half of the energy distance, which can be understood as a generalization of the CRPS to a pair of ranvars.`int(R, a, b)`

: returns the integral of`R`

over the inclusive segment [a;b].`mean(R)`

: returns the statistical mean of the random variable`X`

.`quantile(R,`

$\tau$`)`

: returns the quantile of the ranvar; the smallest $k$ such as $\mathbf{P}[X \leq k] \geq \tau$.`spark(R)`

: returns a text value that contains compact ascii-art representation of the ranvar, that can be read by a text editor.`support.max(R)`

: returns the higher bound of the ranvar support.`support.min(R)`

: returns the lower bound of the ranvar support.`variance(R)`

: returns the statistical variance of the random variable`X`

.

### Transformations of ranvars

A ranvar can also be transformed into another ranvar (the total mass of 1 being conserved by the transformation). If `X`

and `Y`

are independent random variables, with ranvars `R`

and `S`

respectively, and `a`

and `b`

are integers,

`fillrate(R)`

: returns the marginal fill rate.`maxr(R, a)`

or`maxr(a, R)`

: returns the ranvar that approximates $k \mapsto R(k) \text{ if } k \in [a + 1; \infty[ \text{ or } \sum_{i=-\infty}^a R(i) \text{ if } k = a \text{ else } 0$.`maxr(R, S)`

: returns the ranvar described by $\mathbf{P}[max(X, Y) \leq k] = \mathbf{P}[X \leq k]\mathbf{P}[Y \leq k]$.`minr(R, a)`

or`minr(a, R)`

: returns the ranvar that approximates $k \mapsto R(k) \text{ if } k \in ]-\infty; a - 1] \text{ or } \sum_{i=a}^\infty R(i) \text{ if } k = a \text{ else } 0$.`minr(R, S)`

: returns the ranvar described by $\mathbf{P}[min(X, Y) \geq k] = \mathbf{P}[X \geq k]\mathbf{P}[Y \geq k]$.`mixture`

(R_{1}, p, R_{2}): returns the mixture of two ranvars and a weight: $k \mapsto p \times R_1(k) + (1 - p) \times R_2(k)$.`mixture`

(R_{1}, p_{1}, R_{2}, p_{2}, R_{3}): returns the mixture of three ranvars and two weights: $k \mapsto p_1 \times R_1(k) + p_2 \times R_2(k) + (1 - p_1 - p_2) \times R_3(k)$.`mixture`

(R_{1}, p_{1}, R_{2}, p_{2}, R_{3}, p_{3}, R_{4}): returns the mixture of four ranvars and three weights: $k \mapsto p_1 \times R_1(k) + p_2 \times R_2(k) + p_3 \times R_3(k) + (1 - p_1 - p_2 - p_3) \times R_4(k)$.`reflect(R)`

: returns the reflected ranvar $k \mapsto R(-k)$.`smooth(R)`

: returns a smooth variant of the original ranvar to mitigate the overfitting issue. See the smoothing ranvars sub-section for details.`transform(R, a)`

: returns the ranvar that approximates through interpolation $k \mapsto R(k / a)$.`truncate(R, a, b)`

: returns the truncated ranvar that approximates $k \mapsto R(k) \text{ if } k \in [a; b] \text{ else } 0$. The boundaries`A`

and`B`

are inclusive.

## Algebra of zedfuncs

In Envision, a *zedfunc* is defined as a function $f: \mathbb{Z} \mapsto \mathbb{R}$. As an example, `stockrwd.m()`

and `pricebrk.m()`

return a zedfunc.

### Parametric zedfuncs

`linear(a: number) -> zedfunc`

: returns the linear function $f: k \mapsto ak$.`uniform(a: integer) -> zedfunc`

: returns the constant function $k \mapsto 1$ over the segment $[0,a]$ and zero elsewhere; $a$ is expected to be a positive integer.`uniform(a: integer, b: integer) -> zedfunc`

: returns the constant function $k \mapsto 1$ over the segment $[a,b]$ and zero elsewhere. Both $a$ and $b$ are expected to be integers, with $a \leq b$.`uniform.left(a : integer) -> zedfunc`

: returns the constant function $k \mapsto 1$ over the segment $]-\infty,a]$ and zero elsewhere.`uniform.right(a : integer) -> zedfunc`

: returns the constant function $k \mapsto 1$ over the segment $[a, +\infty[$ and zero elsewhere.

### Transformations of zedfuncs

`diff(z: zedfunc) -> zedfunc`

: returns the derivative $f'$ of the original zedfunc $f$.`int(z: zedfunc) -> zedfunc`

: returns the integral $F$ of the original zedfunc $f$ with $F(0)=0$.`maxz(z1 : zedfunc, z2 : zedfunc, ...) -> zedfunc`

: variadic, returns the max value (point-wise) over the zedfuncs passed as argument.`minz(z1 : zedfunc, z2 : zedfunc, ...) -> zedfunc`

: variadic, returns the min value (point-wise) over the zedfuncs passed as argument.`zoz(z: zedfunc) -> zedfunc`

(*zero on zero*): returns the same zedfunc, except on zero where the value is now zero.

### Evaluations of zedfuncs

`valueAt(z: zedfunc, x: number) -> number`

: returns the value of the zedfunc at $x$. If $x$ is fractional, then the result is obtained from a linear interpolation of the zedfunc.`sum(z: zedfunc, a: integer, b: integer) -> number`

: returns the sum of the zedfunc ($z(k)$) evaluated for $k$ between $a$ and $b$ inclusive. Beware, it’s a not the integral of the interpolated zedfunc over ${x \in \mathbb{R} : a\leq x \geq b}$.

## Table creation functions

New tables can be created from existing tables or ranvars:

`extend.billOfMaterials(...) -> table { DemandId: text, Quantity: number }`

: translates a demand history for items into the demand history for the parts. See also extend.billOfMaterials().`extend.range(k: integer) -> table { N: integer }`

: creates`k`

lines for each line of the argument table.`extend.distinct(...) -> table { Value: text }`

: gathers and deduplicates all the input text values into a single table. See also extend.distinct(…).`extend.distrib(r: ranvar, gap: integer, multiplier: integer, reach: integer) -> table { Id: text, Min: integer, Max: integer}`

: extends a ranvar into a table. See also extend.distrib(…).

## Advanced functions

### cumsub(G.Item, G.Stock, G.Quantity, G.Rank)

The `cumsub()`

function explores all bundles by increasing rank, keeping track of the remaining stock for each item. It takes 4 vectors belonging to a grid table with:

`G.Item`

: the item identifier, all lines that share the same value belong to the same item;`G.Stock`

: the initial stock for the item, all lines that belong to the same item must have the same`G.Stock`

value;`G.Quantity`

: the quantity of the item required for the purchase of the grid line;`G.Rank`

: a*bundle*identifier, all lines that share the same bundle identifier belong to the same bundle. It is forbidden to have two lines with the same`(G.Item, G.Rank)`

pair. All bundles are ordered by increasing rank.

Initially, this stock is defined by the `G.Stock`

vector. For each bundle, the function determines whether there is enough remaining stock to purchase *all* grid lines in that bundle, based on whether the stock exceeds `G.Quantity`

. If that is the case, then the function decrements the stock for each item, and writes to each grid line the remaining stock for that item. If there is not enough stock to serve the entire bundle - usually because one of the items has run out - then the function does not update the remaining stocks and stores for each grid line the value `-(S+1)`

(where `S`

is the remaining stock for that item at that point), to indicate both that the grid line is not purchased (test if `G.S < 0`

) and whether it was that specific line that caused the bundle not to be purchased (test if `G.Quantity + G.S + 1 > 0`

) and by how much (`G.Missing = G.Quantity + G.S + 1`

).

### cumsub.fallback(G.Item, G.Stock, G.Fallback, G.Quantity, G.Rank)

The `cumsub.fallback()`

function explores all bundles by increasing rank, keeping track of the stock consumed for each item. Every bundle attempts to subtract the corresponding quantity from the remaining stock of the corresponding item. If insufficient, additional stock is taken from the fallback item.

The `cumsub.fallback()`

function takes 5 vectors belonging to a grid table with:

`G.Item`

: the item identifier, all lines that share the same value belong to the same item;`G.Stock`

: the initial stock for the item, all lines that belong to the same item must have the same Grid.Stock value;`G.Fallback`

: the fallback item with an associated fallback stock in case we run out of the initial stock. This parameter should be equal to Grid.Item in case there is no fallback item;`G.Quantity`

: the quantity of the item required for the purchase of the grid line;`G.Rank`

: a*bundle*identifier, all lines that share the same bundle identifier belong to the same bundle. Again, it is forbidden to have two lines with the same`(Grid.Item, Grid.Rank)`

pair and all bundles are ordered by increasing rank.

If the bundle succeeds, the function returns for each bundle line the quantity of stock taken from the item’s stock. The quantity taken from the fallback item can be deduced by calculating `Grid.Stock - Grid.Quantity`

. In the specific example where there is no remaining stock on the item to serve the bundle but enough on the fallback item, the function returns `0`

. Then, the quantity taken from the fallback item is exactly `Grid.Quantity`

. If the bundle fails, the function returns `-1`

for each bundle line. Note that each fallback item must have an associated line in the grid, otherwise, its fallback stock will not be considered. If none, fake grid lines must be created with missing fallback items.

There are various possible applications of the `cumsub.fallback()`

function, e.g.:

- Bundle management in purchase and dispatch lists;
- Fallback stock management in dispatch lists;
- Identification of open orders covered by current stock on hand.

The last use case, namely, Identification of open orders covered by current stock on hand, is being picked up and presented below. It displays a specific example of a customer willing to identify which open orders are currently entirely covered by the stock of his main warehouse (all references inside the order must be covered by the main warehouse stock). Otherwise, those orders would be served by other locations. There is an additional constraint which allows every reference to have a fallback stock in case their own stock would not be sufficient to cover the demand. In this example, two items are considered, A and B. A is the fallback of B. There are five orders ranked by order date that are explored by increasing rank (one by one). If there is enough stock to cover all references of the first order, the whole order is considered covered: the stock of all the associated references is being reduced. If there is still a need, the stock of their fallback references is being decreased as well. The next order is being explored. The same logic applies.

In the table entitled: “Results: cumsub.fallback”:

two fields that represent the stock levels of a reference A and B require particular attention. The stock of A needs to be reduced even when the stock of B is required. As a rule of thumb, the stock of a reference with a fallback will be displayed as “Own Stock”, while the stock of a reference without any fallback will be displayed as “Fallback Stock”.

In the selected example, the first order requires `8`

units of A, whereas the initial stock contains 10 units. Thus, this order can be easily served. The stock of A will be reduced to `2`

units (see “Fallback Stock”). The second order requires `3`

units of A, however, only `2`

units are left. This order cannot be served. The “Own Consumption” is assigned a `-1`

consumption, and the stock of A remains the same. The third order requires `5`

units of B, and B has `4`

units of initial stock, and A has `2`

remaining units: this order can be served. We reduce the stock of B which drops to `0`

(see “Own Stock”), while the stock of A falls down to `1`

unit (see “Fallback Stock”). At last, entirely covered orders are identified as those with either an own stock consumption or a fallback stock consumption strictly positive for every reference of those orders.

The following Envision code covers the selected use case:

```
read "/sample/Lokad_Items.tsv" as Items with
Id : text
Fallback : text
Stock : number
read "/sample/Lokad_Orders.tsv" as Orders with
Id : text
OrderId : text
Date : date
OrderedQty : number
Orders.OrderIdRank = -1
where argfirst() by Orders.OrderId sort Orders.1
Orders.OrderIdRank = rank() sort parsenumber(Orders.OrderId)
Orders.OrderIdRank = same(Orders.OrderIdRank) if(Orders.OrderIdRank != -1) by Orders.OrderId
//Cumsub fallback
//Variables initialization
Orders.Fallback = same(Fallback) by Id at Orders.Id
HasFallback = Id != Fallback
///Orders coverage calculation
Orders.StockConsumed = cumsub.fallback(Id, Stock, Fallback, Orders.OrderedQty, Orders.OrderIdRank)
Orders.FallbackStockConsumed = HasFallback & Orders.StockConsumed >= 0 ? Orders.OrderedQty - Orders.StockConsumed : 0
Orders.IsCovered = all(Orders.StockConsumed > 0 | Orders.FallbackStockConsumed > 0) by Orders.OrderId
//Reporting
//In Orders.Stock, we keep track of the stock of all references with a fallback item
Orders.CumStockConsumed = cumsum(HasFallback ? max(Orders.StockConsumed, 0) : 0) by Id sort Orders.OrderIdRank
Orders.Stock = (HasFallback ? Stock : 0) - Orders.CumStockConsumed
//In Orders.FallbackStock, we keep track of the stock of all references without a fallback item
Orders.CumFallbackStockConsumed = cumsum(HasFallback ? Orders.FallbackStockConsumed : max(Orders.StockConsumed, 0)) by Fallback sort Orders.OrderIdRank
Orders.FallbackStock = same(Stock) by Id at Orders.Fallback
Orders.FallbackStock = Orders.FallbackStock - Orders.CumFallbackStockConsumed
show table "Cumsub fallback" e3h3 with
Orders.OrderIdRank as "OrderId Rank"
Orders.OrderId as "OrderId"
Id as "Ref"
Fallback as "Fallback"
HasFallback ? "Yes" : "No" as "Fallback Item?"
Stock as "Own Initial Stock"
Orders.OrderedQty as "Ordered Qty"
Orders.StockConsumed as "Own Consumption"
Orders.FallbackStockConsumed as "Fallback Consumption"
Orders.Stock as "Own Ctock"
Orders.FallbackStock as "Fallback Ctock"
Orders.IsCovered ? "Yes" : "No" as "Covered?"
order by Orders.OrderIdRank
```

### extend.distinct(…)

The function `extend.distinct`

takes up to 8 tables as arguments - to be passed to `T1`

, `T2`

, etc. with each table associated to a varying number of text vectors. All those text values are gathered and deduplicated in order to return a single table that consolidates all those values.

```
table T = extend.distinct(
T1: TA.VA, TA.VB, ...
T2: TB.VA, TB.VB, ...
T3: ...)
show table "Merged" with T.Value
```

### forex(value, Origin, Destination, date)

Returns the amount expressed in the currency `Origin`

into the equivalent amount in the currency `Destination`

according to the historical rates at the specified date. The currencies should be encoded with their canonical three-letter codes. Lokad supports about 30 currencies leveraging the data provided by the European Central Bank. Rates are mostly updated on a daily basis. See also `isCurrency()`

to test the validity of your currency code.

### forex.last(Origin, Destination)

Returns the date of the latest exchange rate found for a pair of currencies. The currencies should be encoded with their canonical three-letter codes. Not all exchange rates are uploaded daily to the European Central Bank.

### hash(value)

Returns a pseudo-injective hash value between 0 and 2^24-1. This function is typically used to randomly shuffle a dataset by hashing the content of a column, and then sorting against the hashed values.

### isCurrency(currencyCode)

Returns `true`

if the text entry passed as an argument is a currency code recognized by the `forex()`

function.

### mkuid(X, offset)

Returns a unique number, with unicity maintained across Envision runs. This function is intended to uniquely identify results calculated by Lokad. For example, it can be used to generate a unique *purchase order number* to be incremented whenever the Envision script is re-executed.

The vector `X`

is ignored, but the UID (unique identifier) is generated as a scalar in the table associated to `X`

. The `offset`

is an optional scalar that represents the starting suffix for the UID. The generated strings are numbers in the format `PPPPPPPAAA`

, with `P`

as a page number (does not start with 0) that is always strictly increasing, and `A`

as an incremented counter that starts at offset (or 0 if no offset parameter is provided). `P`

has at least 7 digits, `A`

has at least 3.

The UIDs offer three properties.

- All UIDs can be parsed as numbers, and those numbers will be different. Keep in mind, however, that UIDs have at least 10 digits, and likely more if each call needs to generate more than 1000.
- A UID generated at time T is strictly inferior (in alphabetical order) to a UID generated at time T’ > T.
- If all calls generate similar numbers of UIDs (less than 999, or between 1000 and 9999, etc.) then the previous property is also true for the numeric order between UIDs.

### solve.moq(…)

An advanced numeric solver for the general MOQ problem (minimal order quantities).

### pricebrk.m(…)

Merchant variant of the price break function. Returns the zedfunc representing the marginal purchase unit price from a table listing the price breaks. See Supplier Price Breaks.

### pricebrk.f(…)

Fiscal variant of the price break function. Returns the zedfunc representing vector of the marginal purchase unit price from a table listing the price breaks. See Supplier Price Breaks.

### priopack(V, MaxV, JT, B) by [Group] sort [Order]

A simple variant of the bin packing algorithm intended to be used with a purchase prioritization list. Unlike the classic bin packing algorithm, not only do we seek to optimize the bin capacities, but the ordering of the units will also be preserved as much as possible.

`V`

is the volume of each line.`MaxV`

is the max volume capacity, its value is homogeneous to`V`

, and it is assumed to be a constant value across the equivalent class`Group`

.`JT`

is the jumping threshold, its value is homogeneous to`V`

, it is typically expected to be a small multiple of the`Group`

value.`B`

is an optional argument interpreted as the*barrier*, when this value is provided, the bin-packing process is not allowed to reorder lines that belong to the same equivalence class as defined by`B`

.`Group`

is the equivalence class of the suppliers, with bin packing computed*per supplier*.`Order`

contains the ranks of the lines to be packed.

### ranvar.segment(…)

This call-function is intended to convert time-series into probability distributions (*ranvar*) by collecting observations over moving windows.

```
D = ranvar.segment(
start: Items.Start // first date (inclusive) for each item
end: Items.End // end date (inclusive) for each item
step: Items.Step // number, increments in day in-between observation
horizon: Items.Horizon // number, the length in day of period for each item
date: Orders.Date // date for each event
censoredDemandDate: Censored.Date // days that are skipped when generating the ranvar
quantity: Orders.Quantity) // quantity for each event
```

This function computes, for each item, the ranvar of the sum of event quantities over periods of horizon length, that are entirely between the first and last date for that item. For example, for a start date on Jan 1st, end date on Jan 7th, a horizon of 3 days, and a single event of quantity 5 on Jan 2nd, the observed periods are:

- Jan 1st - Jan 3rd: Q = 5
- Jan 2nd - Jan 4th: Q = 5
- Jan 3rd - Jan 5th: Q = 0
- Jan 4th - Jan 6th: Q = 0
- Jan 5th - Jan 7th: Q = 0

Thus, the resulting ranvar is 60% Q = 0, 40% Q = 5.

When using the censoredDemandDate: argument, the censored days are skipped, i.e., the ranvar is generated as if these days never existed. Using the previous example, if the 3rd of January is censored, the ranvar segment is applied as if the 4th of January was the day following the 2nd of January.

- Jan 1st - Jan 4rd: Q = 5
- Jan 2nd - Jan 5th: Q = 5
- Jan 4th - Jan 6th: Q = 0
- Jan 5th - Jan 7th: Q = 0

Thus, the resulting ranvar is 50% Q = 0, 50% Q = 5.

### ranvar.periodicr(…)

This is a more general version of `ranvar.segment`

, where the horizon is a ranvar of lengths (e.g. the probability distribution of lead times) and events at the extrema or at the middle of the chosen time interval are equally taken into account:

```
D = ranvar.periodicr(
start: Items.Start // first date (inclusive) for each item
end: Items.End // end date (inclusive) for each item
horizon: Items.LeadTime // ranvar of lengths in day
date: Orders.Date // date for each event
quantity: Orders.Quantity) // quantity for each event
```

Indeed, `ranvar.periodicr`

considers an infinite repetition of the input data and sums the event quantities over periods of all possible lengths contained in the horizon ranvar. As a consequence, if we consider the example above and we imagine an additional event of quantity 2 on Jan 7th, `ranvar.periodicr`

with `dirac(3)`

as the horizon would observe the following:

- Jan 1st - Jan 3rd: Q = 5
- Jan 2nd - Jan 4th: Q = 5
- Jan 3rd - Jan 5th: Q = 0
- Jan 4th - Jan 6th: Q = 0
- Jan 5th - Jan 7th: Q = 2
- Jan 6th - Jan 1st: Q = 2
- Jan 7th - Jan 2nd: Q = 7

and returns the ranvar ~28% Q = 0, ~28% Q = 2, ~28% Q = 5, ~14% Q = 7. For comparison, `ranvar.segment`

with horizon 3 and step 1 would ignore the last two lines of the above list and return 40% Q = 0, 20% Q = 2, 40% Q = 5.

### forest.regress(…)

Gives access to a random forest regression algorithm. A training dataset (`Example`

) and an evaluation dataset (`Sample`

) must be provided. These two tables contain the same series of column attributes, for a different set of Id. In addition, the `Example`

table contains a further column called `Label`

, which represents the desired output of the regression function. The `Example`

table shall thus be conceived as a `Sample`

with a `Label`

.

The function syntax is the following:

```
Sample.Label = forest.regress(
training: Example.A, Example.B, Example.C // number/boolean/text
trainingBow : Example.D // plain text, optional
label: Example.Label // number
evaluation: Sample.A, Sample.B, Sample.C // number/boolean/text
evaluationBow: Sample.D) // plain text, optional
```

This call-function returns for each Id a ranvar (probability distribution) representing the quantity `Label`

.
Up to 16 different attributes are supported as `training`

and `evaluation`

entries. Of these, 8 at most can be number vectors and 8 at most can be Boolean or text vectors representing categories. Optionally, a text vector of words can be provided as `trainingBow`

and `evaluationBow`

: here the text string is treated as a bag-of-words, and analysed in terms of words occurrences.

### smudge(values, present) by [Group] sort [Order]

Takes an incomplete vector of `values`

and a Boolean vector that determines where the valid values are present. It returns a full vector of valid values, which has been completed by spreading valid values into the non-valid ones. More precisely, the output vector is built by looking at every line, group by group (if there is a `Group`

argument) and following the ascending `Order`

, and replacing any non-valid value by the last value that has been seen, or by a default value if no valid value has yet been seen in the group.

### stockrwd.m(D, AM), stockrwd.s(D), stockrwd.c(D, AC)

The stock reward functions are used to build prioritized ordering policy out of the probabilistic forecasts produced by Lokad.