- The Quantitative Supply Chain Manifesto
- The Lokad test of supply chain performance
- An overview of quantitative supply chain
- Generalized probabilistic forecasting
- Decision-driven optimization
- Economic drivers
- Data preparation
- The Supply Chain Scientist
- Timeline of a typical project
- Project deliverables
- Assessing success
- Antipatterns in supply chain

- Inventory forecasting
- Prioritized ordering report
- Old forecasting input file format
- Old forecasting output file format
- Choosing the service levels
- Managing your inventory settings
- The old Excel forecast report
- Using tags to improve accuracy
- Oddities in classic forecasts
- Oddities in quantile forecasts
- Stock-out's bias on quantile forecasts
- Daily, weekly and monthly aggregations

Home » Resources » Here

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.

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 `by`

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

. Both `Group`

and `Order`

support R = rank() by [A, B] sort [1]

`abs(number)`

: Similar to the ABS function in Excel.`ceiling(number)`

: Similar to the CEILING function in Excel.`exp(number)`

: Similar to the EXP function in Excel.`floor(number)`

: Similar to the FLOOR function in Excel.`log(number, base)`

: Similar to the LOG function in Excel. The`base`

argument is optional, when it is omitted, it is assumed to be 10.`max(num1, num2, num3, ..., numN)`

: Similar to the MAX function in Excel.`min(num1, num2, num3, ..., numN)`

: Similar to the MIN function in Excel.`percent(N) by [Group]`

: Returns the value`N`

divided by the sum of the all the values within the`Group`

.`norminv(number)`

: Similar to the NORMINV function in Excel with a mean at 0 and a standard deviation at 1.`pow(number, exponent)`

: Similar to the POWER function in Excel. Envision also support the power operator`number ^ exponent`

which performs the same calculation.`round(number, digits)`

: Similar to the ROUND function in Excel. Second argument is optional, and represents the number of digits that intended to be kept.`sqrt(number)`

: Similar to the SQRT function in Excel.

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

: Concatenates text values from`text1`

to`textN`

.`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) == "c"`

.`indexof(text, pattern)`

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

: Returns the lowercase variant of the text.`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 detail for 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.`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.`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.`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 place 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.

`"\{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 arguments. The arguments`y`

,`m`

and`d`

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

: Returns the first Monday that precedes the date (inclusive).`month(date)`

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

: Returns the applicable month (1-12) for the date passed as argument.`today(timezone)`

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

: Returns the applicable year for the date passed as argument. Similar to the YEAR function in Excel.`yearStart(date)`

: Returns the first day of the year for the current year.`yearEnd(date)`

: Returns the last day of the year for the current year.`weeknum(date)`

: Similar to the WEEKNUM (System 2) function in Excel.

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

: Returns`true`

for one 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 returning`true`

for the one last value.`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 backward 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 advance ranking function that is quite different from the other`rank()`

overloads. The purpose of this overload is 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.

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

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

`dirac(n)`

: Returns a function zero valued everywhere except for`n`

where the function is valued at 1.`identity(n)`

: Returns the function $\text{id}: k \to k$ but limited to the segment [0;n] and 0 elsewhere.`uniform(n)`

: Returns the function $\text{unif}: k \to 1$ but limited to the segment [0;n] and 0 elsewhere.`uniform(m, n)`

: Returns the function $\text{unif}: k \to 1$ but limited to the segment [m;n] and 0 elsewhere. If $m - 1 = n$, the`uniform()`

returns a zero distribution.`uniform(D)`

: Returns the function $\text{unif}: k \to 1$ but limited to the support of the distribution`D`

and 0 elsewhere.`poisson(lambda)`

: Returns the Poisson distribution of parameter`lambda`

($\lambda$ in literature).`exponential(lambda)`

: Returns the exponential distribution of parameter`lambda`

($\lambda$ in literature).`negativebinomial(mu, sigma)`

: Returns the negative binomial distribution of mean`mu`

and standard deviation`sigma`

. If the standard deviation is smaller than the mean, then a Poisson distribution of mean`mu`

is returned instead.`ranvar.uniform(n)`

: Returns the distribution represented by the function $k \to \frac{1}{|n| + 1}$ on the segment [0;n] (if $n \ge 0$) or [n;0] (if $n \lt 0$) and 0 elsewhere.`ranvar.uniform(m, n)`

: Returns the distribution represented by the function $k \to \frac{1}{n + 1 - m}$ on the segment [m;n] and 0 elsewhere. We assume that $m \le n$; an error is thrown if $m \gt n$.

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

: Function that returns the distribution 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 distribution resulting from the observations obtained from the vector`T.X`

.`ranvar.segment(...)`

: Advance function that generates empirical distribution resulting from a sliding time-window (more details in the following).

`crps(X, A)`

where`A`

are integral numbers, returns the Continuous Ranked Probability Score (CRPS).`crps(X, Y)`

where both`X`

and`Y`

are distributions, returns the half of the energy distance which can be understood as a generalization of the CRPS to a pair of distributions.`mean(X)`

: Returns the statistical mean.`variance(X)`

: Returns the statistical variance.`mass(X)`

: Returns to mass of distribution, that is, $\sum_{k=-\infty}^{\infty}f(k)$`isranvar(X)`

: Returns a Boolean that is`true`

if the distribution is a random variable.`int(X, A, B)`

where`A`

and`B`

are integral numbers returns the integral of`X`

over the inclusive segment [A;B].`quantile(X, tau)`

: Returns the quantile of the distribution; the smallest $x$ such as $\mathbf{P}[X \leq x] \geq \tau$.`spark(X)`

: Returns a text value that contains compact ascii-art representation of the distribution.`support.max(X)`

: Returns the higher bound of the distribution support.`support.min(X)`

: Returns the lower bound of the distribution support.

`reflect(X)`

: Returns the reflected distribution $k \to f(-k)$.`transform(X, a)`

: Returns a distribution that approximates through interpolation $k \to f(k / a)$.`fillrate(X)`

: Returns the marginal fill rate. Expects a random variable as input and returns a random variable.`truncate(X, a, b)`

: Returns the truncated distribution $k \to f(k) \text{ if } k \in [a; b] \text{ else } 0$. The boundaries`A`

and`B`

are inclusive.`maxr(X, a)`

: Returns the distribution $k \to f(k) \text{ if } k \in [a + 1; \infty[ \text{ or } \sum_{i=-\infty}^a f(i) \text{ if } k = a \text{ else } 0$.`minr(X, a)`

: Returns the distribution $k \to f(k) \text{ if } k \in ]-\infty; a - 1] \text{ or } \sum_{i=a}^\infty f(i) \text{ if } k = a \text{ else } 0$.`mixture(X1, p, X2)`

: Returns the mixture of two distributions and a weight: $k \to p \times f_1(k) + (1 - p) \times f_2(k)$.`mixture(X1, p1, X2, p2, X3)`

: Returns the mixture of three distributions and two weights: $k \to p_1 \times f_1(k) + p_2 \times f_2(k) + (1 - p_1 - p_2) \times f_3(k)$.`mixture(X1, p1, X2, p2, X3, p3, X4)`

: Returns the mixture of four distributions and three weights: $k \to p_1 \times f_1(k) + p_2 \times f_2(k) + p_3 \times f_3(k) + (1 - p_1 - p_2 - p_3) \times f_4(k)$.`zoz(X)`

(*zero on zero*): Returns the distribution where $k \to f(k) \text{ if } k \neq 0 \text{ else } 0$.

`extend.range(T.N)`

: Creates`N`

lines for each line from the table`T`

where`N`

is expected to be an integer. For example:

table T = extend.range(Orders.42)

T.Quantity = Orders.Quantity // implicit extension

show table "T" with T.N, T.Quantity`extend.distrib(distribution, gap, multiplier, reach)`

: Extends a distribution into a table. See also extend.distrib().`extend.billOfMaterials(...)`

: Translates a demand history for items into the demand history for the parts. See also extend.billOfMaterials().

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

`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, and all bundles are ordered by increasing rank.

The function

`cumsub()`

explores all bundles by increasing rank, keeping track of the remaining stock for each item. Initially, this stock is defined by the `G.Stock`

vector. For each bundle, the function determines whether there is enough remaining stock to purchase `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 item has run out - then the function does not update the remain 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`

).`forex(value, Origin, Destination, date)`

`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 updated on a daily basis. See also `isCurrency()`

to test the validity of your currency code.`hash(value)`

`isCurrency(currencyCode)`

`true`

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

function.`mkuid(X, offset)`

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 for the UID. The generated strings are numbers in format `PPPPPPPAAA`

, with `P`

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

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.
- An UID generated at time T is strictly inferior (in alphabetical order) to an 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(...)`

`pricebrk(D, P, Prices.MinQ, Prices.P, Stock, StockP)`

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

`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 values is homogeneous to`V`

, it is typically expected to 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 below 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(...)`

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 quantity: Orders.Quantity) // quantity for each eventThis function computes, for each item, the distribution of the sum of event quantities for periods of horizon length 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 = 5
- Jan 4th - Jan 6th: Q = 0
- Jan 5th - Jan 7th: Q = 0

And so the resulting distribution is 40% Q = 0, 60% Q = 5.

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

`values`

and a Boolean vector that determines where the valid values are present. It returns a full vector of valid values, that 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 been seen yet in the group.`stockrwd.m(D, AM), stockrwd.s(D), stockrwd.c(D, AC)`