Chapter 16. Inventory Functions

LogiQL provides a special form of rule to calculate the cover and uncover time series functions that are specific to retail applications.

A time series is a sequence of data points indexed in order. The time series index can be any primitive or entity type; it does not necessarily have to be a datetime. The choice of data type used for the time series index is a modeling decision left to the user. For example, integers could be used to represent weeks.

An example of a time series is the sequence of sales data for each week of the second fiscal quarter, where week n is indexed by the integer n and the sales value is a decimal.

A time series function performs a calculation on a time series and produces another time series. We describe the calculation performed by both cover and uncover in the sections that follow.

16.1. General form of inventory rules

All inventory rules have the following general form:

InventoryRule = Atom "<-" "inventory" "<<" OutputVar "=" FunctionExpression [ "from" FromVar ] [ "to" ToVar ] [ "extend" "with" ExtensionAtom ] ">>" BodyConjunction "." .

FunctionExpression = FunctionName "<" FirstInOrder "," OthersInOrder ">" "[" IndexVar "]" "(" InputVar1 "," InputVar2 ")" .
FunctionName       = ("cover" | "uncover") .

FirstInOrder  = AtomName .
OthersInOrder = AtomName .

OutputVar = BasicIdentifier .
IndexVar  = BasicIdentifier .
InputVar1 = BasicIdentifier .
InputVar2 = BasicIdentifier .

FromVar  = BasicIdentifier .
ToVar    = BasicIdentifier .

ExtensionAtom = AtomName "[" KeyArguments "]" "=" ValueArgument .
KeyArguments  = BasicIdentifier { "," BasicIdentifier } .
ValueArgument = BasicIdentifier .

BodyConjunction = BodyUnit { "," BodyUnit } .
BodyUnit = Atom | ArithmeticFormula .

The body of an inventory rule must be a conjunction of atoms or arithmetic formulas; in particular, disjunction is not allowed. Furthermore, the head must only contain one atom. Both of the input variables (i.e., InputVar1 and InputVar2) must be bound in the body. The variable IndexVar is used to index the elements of the time series (for example, the weeks of the first quarter); it must be bound in the body, it must be the rightmost key in the head atom, and it must either be of an entity type or of a primitive type. We refer to all keys in the head other than the rightmost key as the group-by variables.

The two supported inventory functions are cover and uncover. Both of these functions take two arguments, and the order of arguments matters. If cover is used, then InputVar1 is the number of units of inventory and InputVar2 is the number of unit sales (see Section 16.3, “Cover” for further details). If uncover is used, then InputVar1 is the number of time units of supply, and InputVar2 is the number of unit sales (see Section 16.4, “Uncover” for further details). We will describe the meaning of these functions in the usage sections which follow.

The predicate names that are used for FirstInOrder and OthersInOrder specify an ordering on the index of the time series. The FirstInOrder predicate is a scalar predicate that specifies the first index of the time series (for example, the week 0 is the first week of the quarter), and OthersInOrder specifies the successor function for the series (for example, that week 0 is succeeded by week 1, which is succeeded by week 2, and so on, up to some final week). For more details, see Section 16.2, “Time series order”.

The from clause is optional and, if specified, the value of variable FromVar is used as the first index of the time series. Similarly, the to clause is optional and, if specified, the value of variable ToVar is used as the last index of the time series. The variables FromVar and ToVar are instantiated in the body, and their instantiations may depend on the instantiations of the group-by variables.

The extend with clause is optional and, if specified, ExtensionAtom is the atom used to extend the time series with additional data (for further explanation, see Section 16.3, “Cover” and Example 16.9, “Weeks of supply with sales time series extension”). Each key argument of this atom must occur in the head; the rightmost key of the atom must be the same as the chosen IndexVar variable; and, the value argument must be the same as the chosen InputVar2 variable.

The extend with clause cannot be used together with either a from clause or a to clause.

Restrictions on the form of rules

We say that a predicate is materialized if it is either an EDB predicate or an IDB predicate with the derivation type is "DerivedAndStored" (see Section 8.8, “Derivation Types”).

Here is a complete list of conditions that must be satisfied by an inventory rule:

  1. The head must only contain one atom.
  2. Each key argument of the head atom must be a variable that occurs in the body.
  3. The head atom must have exactly one value argument, and it must be the output variable (i.e., the variable used for OutputVar).
  4. The output variable cannot occur in the body of the rule.
  5. Each formula in the body conjunction must be an atom or an arithmetic formula.
  6. In each body atom that refers to a materialized predicate, each key argument must be a variable that occurs as a key in the head.
  7. Each input variable (i.e., the variables used for InputVar1 and InputVar2) must be bound in some body atom.
  8. The time index variable (i.e., the variable used for IndexVar) must be the rightmost key in the head atom, and it must be bound as a key argument in some body atom that refers to a materialized predicate.
  9. If the "from" variable (i.e., the variable used for FromVar) is specified, then it must be bound in some body atom, and it must be of the same type as the time index variable.
  10. If the "to" variable (i.e., the variable used for ToVar) is specified, then it must be bound in some body atom, and it must be of the same type as the time index variable.
  11. Both of the input variables and the output variable must all be of the same type. The type must be summable, i.e., it must be one of the following: int, int128, float, or decimal.

If the time series extension predicate is specified, then the following conditions must also be satisfied:

  1. Neither a from nor a to clause is used.
  2. The time series extension atom must refer to a materialized predicate.
  3. Each key argument of the extension atom must be a variable (i.e., not a constant) that occurs as a key in the head.
  4. The rightmost key of the extension atom must be the time index variable.
  5. The value argument the extension atom must be the second input variable (i.e., the one used for InputVar2).

16.2. Time series order

The time series index can be any primitive or entity type. For example, if a time series is indexed by week, then we could choose to represent each week by a unique int value, or we could choose to represent each week using a value of an entity type.

The time series is ordered according to the first and rest predicates supplied by the user (cf. Section 8.9, “Ordered Predicates”). The following examples show how an order can be defined for a primitive time series type and an entity time series type.

Example 16.1. Ordering of weeks represented as a primitive

Suppose that a retailer defines their fiscal quarters as 13-week periods. We can represent each week as an integer in the range 1 through 13. A total order on the weeks of a quarter could be defined as follows:

week_first_int[] = w -> int(w).
week_first_int[] = 1.

week_next_int[w] = wn -> int(w), int(wn).
week_next_int[1] = week_first_int[] + 1.
week_next_int[i] = week_next_int[i - 1] + 1 <- 1 < i < 14.

The defined order does not have to be the same as the natural order, as the following example shows.

Example 16.2. Ordering of weeks with non-natural order

Each week is represented as an integer in the range 1 through 12. The order on weeks that is defined here is different from the natural order.

week_first_alt[] = w -> int(w).
week_first_alt[] = 1.

week_next_alt[w]  = wn -> int(w), int(wn).
week_next_alt[1]  = week_first_alt[] + 2.
week_next_alt[13] = week_first_alt[] + 1.
week_next_alt[i]  = week_next_alt[i - 1] + 1 <- 1 < i < 13.

The predicates week_first_alt and week_next_alt together define the order 1 < 3 < 5 < 7 < 9 < 11 < 13 < 2 < 4 < 6 < 8 < 10 < 12.

Such an order may not make much sense for a real-world application, but this example nonetheless shows that alternative week order definitions are possible.

In a similar way, an order can be constructed for entity types, as is shown in the next example.

Example 16.3. Ordering of weeks represented as an entity

In this example, we choose to represent each week as an entity of type Week. The entity type Week captures a set of weeks, and the week_first and week_next together define the total order on the weeks.

Week(week) -> .
Week_id[id] = week -> int(id), Week(week).
lang:constructor(`Week_id).

week_first[] = w -> Week(w).
week_first[] = Week_id[0].

week_next[w] = wn -> Week(w), Week(wn).
week_next[Week_id[id]] = Week_id[id + 1].

If the type of indexes in the time series is either int or int128 then we an specify the order by giving the first and last index of the time series.

Example 16.4. Simplified natural ordering definition for int and int128

In Example 16.1, “Ordering of weeks represented as a primitive”, each week in a quarter was represented an integer in the range 1 through 13 inclusive.

An alternative definition of the same order is the following:

week_first_simple[] = w -> int(w).
week_first_simple[] = 1.

week_last_simple[] = w -> int(w).
week_last_simple[] = 13.

This definition uses the natural order on int from 1 to 13 inclusive.

Time series orders on int or int128 indexes defined in this way are slightly more efficient, and so should be used when applicable.

We define the cardinality of an order to be the count of the number of indexes in the order from the first index through all successors in the order.

For example, given the order defined in Example 16.1, “Ordering of weeks represented as a primitive”, the cardinality is 13 because the first week is 1, and weeks 2 through 13 are successors of 1. Alternatively, if we instead defined week_first_int to be week_first_int[] = 5, then the cardinality would be 9 because the first week is now 5, and 6 through 13 are successors of 5.

We will refer to the cardinality of the chosen order when describing the behavior of the cover and uncover functions in the sections that follow.

16.3. Cover

Introduction

In this section, we describe the calculation performed by the cover time series function.

Given a number of units of inventory at a given point in time, and a time series of unit sales, the inventory cover is defined to be the (possibly fractional) number of time units for which the cumulative total of unit sales is equal to the inventory. Hence cover tells us how long a store is able to continue selling an item, given the stock level and the expected sales per unit of time.

For example, if the time series is indexed by week, then inventory cover for a given week is the number of weeks for which the cumulative total of unit sales over successive weeks is equal to the inventory.

From one time unit to the next, the stock level of an item may increase due to replenishment, or may decrease due to stock reallocation to a different store or warehouse. Hence, we do not assume that the stock level of an item can only decrease by the sales of the item as given for the previous time unit.

We will now take a look at two examples. The first example is without the optional extend with clause, and the second example shows how the extend with can be used. The following table gives the data for the sales and stock predicates over the time series of weeks:

week Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
sales (input) 200 25 10 75 50 25
stock (input) 100 110 25 100 150 255
weeksOfSupply (output) 0.5 3 1.2 1.5 2 1
weeksOfSupply (output when extended) 0.5 3 1.2 1.5 2.375 3.5

The cover function takes as input the unit sales time series and the inventory time series and outputs a time series with weeks of supply values. For a given week, the number of weeks of supply is calculated as the number of weeks of running total unit sales (beginning at the given week) that would exhaust the inventory of the given week. For example, in week 1 we have 0.5 weeks of supply because 0.5 * sales[week 1] = stock[week 1]. For week 2, we have 3 weeks of supply because sales[week 2] + sales[week 3] + sales[week 4] = stock[week 2]. For week 3, we have 1.2 weeks of supply because 1 * sales[week 3] + 0.2 * sales[week 4] = stock[week 3], and the value is the sum of multipliers. Similarly for week 4.

But what about week 5 and week 6 where the running total of sales to the end of the time series is less than the inventory? In this case, the weeks of supply is defined to be the number of remaining weeks in the time series. Hence, we have 2 weeks of supply in week 5, and 1 week of supply in week 6.

It is also possible to extend the time series of unit sales data that is used by the cover function. An additional predicate provides the extended time series of unit sales data, which is appended to the end of the specified unit sales data.

For example, if we reuse the sales predicate to extend the time series, then the sequence of sales values is 200, 25, 10, 75, 50, 25 followed by 200, 25, 10, 75, 50, 25 a second time. Now, for week 5 and week 6, the running total of sales of the end of the time series is greater than the inventory for the respective weeks. Hence, for week 5, we have 2.375 weeks of supply because 1 * sales[week 5] + 1 * sales[week 6] + 0.375 * sales[week 7] = stock[week 5]. Similarly, for week 6, we have 3.5 weeks of supply because 1 * sales[week 6] + 1 * sales[week 7] + 1 * sales[week 8] + 0.5 * sales[week 9] = stock[week 6].

Usage Examples

In LogiQL, the weeks of supply can be calculated using the cover time series function as shown in the following example.

Example 16.5. Weeks of supply

This example uses the predicates week_first_int and week_next_int that were defined in Example 16.1, “Ordering of weeks represented as a primitive”.

sales[week] = s -> int(week), decimal(s).
stock[week] = k -> int(week), decimal(k).

weeksOfSupply[week] = wos -> int(week), decimal(wos).
weeksOfSupply[week] = wos <-
   inventory<< wos = cover<`week_first_int, `week_next_int>[week](k, s) >>
      k = stock[week],
      s = sales[week].

Note

In the above example, the type of the time series index is int and so the order could have been defined by specifying the first and last week (see Example 16.4, “Simplified natural ordering definition for int and int128 for an example).

It is common in real-world applications that an entity type is used for the time series index, and this is shown in the following example.

Example 16.6. Weeks of supply with entity type time index

This example uses the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

sales[week] = s -> Week(week), decimal(s).
stock[week] = k -> Week(week), decimal(k).

weeksOfSupply[week] = wos -> Week(week), decimal(wos).
weeksOfSupply[week] = wos <-
   inventory<< wos = cover<`week_first, `week_next>[week](k, s) >>
      k = stock[week],
      s = sales[week].

There is often a need to compute multiple time series of weeks of supply, for example separately for each location or product. This is known as a group-by (cf. a similar mechanism in sorting, as illustrated in Example 13.6, “Sorting with group-by).

If the materialized predicates in the body have key arguments other than the time index argument, then the other key arguments function as a group-by.

Example 16.7. Weeks of supply with group-by

This example shows how group-by variables can be used; the rules are the same as in the previous example except that we now use store and sku as group-by variables. We again use the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

The following LogiQL rule shows how a time series of weeks of supply can be computed separately for each store location and stock keeping unit:

sales[store, sku, week] = s -> Store(store), Sku(sku), Week(week), decimal(s).
stock[store, sku, week] = k -> Store(store), Sku(sku), Week(week), decimal(k).

weeksOfSupply[store, sku, week] = wos -> Store(store), Sku(sku), Week(week), decimal(wos).
weeksOfSupply[store, sku, week] = wos <-
   inventory<< wos = cover<`week_first, `week_next>[week](k, s) >>
      k = stock[store, sku, week],
      s = sales[store, sku, week].

The group-by variables are store and sku.

We now describe how the from and to clauses can be used to specify the first and last weeks for each binding of group-by variables.

Example 16.8. Weeks of supply with different time series per group-by

In this example, we demonstrate how the begin and end weeks of the time series can be specified for each group. The Inventory P2P rule is the same as in Example 16.7, “Weeks of supply with group-by” but with the addition of from and to clauses.

We again use the predicates week_first and week_next to define the order on the time series as given in Example 16.3, “Ordering of weeks represented as an entity”.

sales[store, sku, week] = s -> Store(store), Sku(sku), Week(week), decimal(s).
stock[store, sku, week] = k -> Store(store), Sku(sku), Week(week), decimal(k).

week_begin[store, sku] = week -> Store(store), Sku(sku), Week(week).
week_end[stone, sku]   = week -> Store(store), Sku(sku), Week(week).

weeksOfSupply[store, sku, week] = wos -> Store(store), Sku(sku), Week(week), decimal(wos).
weeksOfSupply[store, sku, week] = wos <-
   inventory<< wos = cover<`week_first, `week_next>[week](k, s) from wb to we >>
      k  = stock[store, sku, week],
      s  = sales[store, sku, week],
      wb = week_begin[store, sku],
      we = week_end[store, sku].

In this LogiQL rule, the predicates week_first and week_next define the time series order as before, but in addition wb is the beginning week (inclusive) and we is the end week (inclusive) for the store and sku group-by.

For example, week_first and week_next could together define the weeks time series 1 through 53 according to the retail calendar, and for a given store and sku pair, wb could be 4 and we could be 10.

In real-world applications, using a beginning week and an end week for a store and sku pair (instead of defaulting to the first and last week of the time series) might be needed if, for instance, some products in some store locations have a shorter life-cycle than others.

The cover time series function also provides a mechanism for extending the sales time series, as described above in the section called “Introduction”. This is demonstrated by the following example.

Example 16.9. Weeks of supply with sales time series extension

The time series of sales values can be extended using an additional predicate that is specified with the extend with clause.

As described in Section 16.1, “General form of inventory rules”, the rightmost key of this additional time series extension predicate must be the variable that indexes time.

Here we again use the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

sales[week]          = s -> Week(week), decimal(s).
salesExtension[week] = s -> Week(week), decimal(s).
stock[week]          = k -> Week(week), decimal(k).

weeksOfSupply[week] = wos -> Week(week), decimal(wos).
weeksOfSupply[week] = wos <-
   inventory<< wos = cover<`week_first, `week_next>[week](k, s) extend with salesExtension[week] = s >>
     k = stock[week],
     s = sales[week].

Predicate salesExtension could, of course, have been any other predicate (provided that the time series index occurred as the rightmost key). For example, the extension predicate could be a predicate from the body of the rule (see Example 16.10, “Weeks of supply with repeated sales time series” below).

Note

The time series order is also used to order the data of the sales time series extension predicate.

If a unit sales time series extension predicate is specified with an extend with clause, then neither a from clause nor a to clause can be specified (as stated in the section called “Restrictions on the form of rules”).

It is also possible to use a unit sales extension predicate with group-by keys.

Example 16.10. Weeks of supply with repeated sales time series

In this example, we extend the time series of unit sales data by using the sales predicate (which occurs in the body of the rule) as the predicate in the extend with clause. This has the effect of repeating the sales time series for a second time when computing inventory weeks of supply values.

This example uses the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

sales[store, sku, week]   = s   -> Store(store), Sku(sku), Week(week), decimal(s).
returns[store, sku, week] = r   -> Store(store), Sku(sku), Week(week), decimal(r).
stock[store, sku, week]   = k   -> Store(store), Sku(sku), Week(week), decimal(k).

weeksOfSupply[store, sku, week] = wos -> Store(store), Sku(sku), Week(week), decimal(wos).
weeksOfSupply[store, sku, week] = wos <-
   inventory<< wos = cover<`week_first, `week_next>[week](k, sls) extend with sales[store, sku, week] = sls >>
      sls = sales[store, sku, week] - returns[store, sku, week],
      k = stock[store, sku, week].

16.4. Uncover

Introduction

In this section, we describe the calculation performed by the uncover time series function.

Given a number of time units, and a time series of predicted future unit sales, the inventory uncover is defined to be the cumulative total of future unit sales over the specified number of time units. Hence the inventory uncover is the stock level that a store would require to be able to sell for the specified number of weeks of supply and the given time series of unit sales.

For example, if the time series is indexed by week, then the inventory uncover is the sum of future unit sales over the specified number of weeks.

We now consider two examples. The first example is without the optional extend with clause, and the second example shows how the extend with can be used. The following table gives the data for the sales and weeksOfSupply predicates over the time series of weeks:

week Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
sales (input) 200 25 10 75 50 25
weeksOfSupply (input) 0.5 3 1.2 1.5 2.375 3.5
stock (output) 100 110 25 100 75 25
stock (output when extended) 100 110 25 100 150 255

The uncover function takes as input the unit sales time series and the weeks of supply time series and outputs a time series with inventory values. For a given week, the inventory is calculated as the running total of unit sales (beginning at the given week) for the number of weeks of supply in the current week. For example, in week 1 we have inventory of 100 because 0.5 * sales[week 1] = 100. For week 2, we have inventory of 110 because 1 * sales[week 2] + 1 * sales[week 3] + 1 * sales[week 4] = 110. For week 3, we have inventory of 25 because 1 * sales[week 3] + 0.2 * sales[week 4] = 25. Similarly for week 4.

If the number of weeks to the end of the time series is less than the number of weeks of supply, then the inventory is calculated as the running total of unit sales for the remaining weeks. Hence, in week 5 we have have inventory of 75 because sales[week 5] + sales[week 6] = 75. Similarly for week 6.

It is also possible to extend the time series of unit sales data (in the same way as is done for the cover function). For example, using the sales predicate to extend the unit sales time series, we obtain the inventory values shown in the last row of the table.

Comparing these inventory values as computed by uncover to those in the table in the section called “Introduction”, we can see that cover and uncover are inverses of each other provided that the sales time series is suitably extended.

Note

The cover and uncover time series functions are inverses of each other provided that both of the following conditions hold:

  • when computing the cover time series, in each time unit, the running total of unit sales to the end of the time series is less than or equal to the inventory; and
  • when computing the uncover time series, in each time unit, the number of time units of supply is less than or equal to the number of time units to the end of the time series.

Usage Example

The usage of uncover is similar to that of cover, as shown in the following example.

Example 16.11. Uncover with different time series per group-by

This example uses the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

sales[store, sku, week]         = s   -> Store(store), Sku(sku), Week(week), decimal(s).
weeksOfSupply[store, sku, week] = wos -> Store(store), Sku(sku), Week(week), decimal(wos).

week_begin[store, sku] = week -> Store(store), Sku(sku), Week(week).
week_end[stone, sku]   = week -> Store(store), Sku(sku), Week(week).

stock[store, sku, week] = k -> Store(store), Sku(sku), Week(week), decimal(k).
stock[store, sku, week] = k <-
   inventory<< wos = uncover<`week_first, `week_next>[week](wos, sls) from wb to we >>
      sls = sales[store, sku, week],
      wos = weeksOfSupply[store, sku, week],
      wb  = week_begin[store, sku],
      we  = week_end[store, sku].

In this LogiQL rule, the predicates week_first and week_next define the time series order, but in addition wb specifies the beginning week (inclusive) and we specifies the end week (inclusive) for each store and sku.

Compare this to a similar example for the cover function in Example 16.8, “Weeks of supply with different time series per group-by”.

As with the cover function, when we use the uncover function, we can extend the time series of unit sales data with an additional predicate that is specified with the extend with clause.

Example 16.12. Uncover with sales time series extension

This example uses the predicates week_first and week_next that were defined in Example 16.3, “Ordering of weeks represented as an entity”.

sales[store, sku, week]         = s   -> Store(store), Sku(sku), Week(week), decimal(s).
returns[store, sku, week]       = r   -> Store(store), Sku(sku), Week(week), decimal(r).
weeksOfSupply[store, sku, week] = wos -> Store(store), Sku(sku), Week(week), decimal(wos).

stock[store, sku, week] = k -> Store(store), Sku(sku), Week(week), decimal(k).
stock[store, sku, week] = k <-
   inventory<< k = uncover<`week_first, `week_next>[week](wos, sls) extend with sales[store, sku, week] = sls >>
      sls = sales[store, sku, week] - returns[store, sku, week],
      wos = weeksOfSupply[store, sku, week].

Note

The time series order is also used to order the data of the sales time series extension predicate.

If a unit sales time series extension predicate is specified with an extend with clause, then neither a from clause nor a to clause can be specified (as stated in the section called “Restrictions on the form of rules”).