Your first Envision script

The quantitative optimization of a supply chain requires extensive data crunching and data visualization. Envision features both, and below, we introduce a simple script as a hands-on way to gain familiarity with Envision. This script generates a dashboard which gather some simple descriptive statistics about the data. Through this example, you will become more familiar with key concepts relative to Envision.

Editing your first script

If you do not already have one, you will need to open a Lokad account which comes with a 30-day free trial. Once you have your Lokad account, you will need to load the Envision sample dataset. This dataset represents the historical data of a small imaginary retailer, and this is the data we are going to use below. The fastest way to add these files to your Lokad account is to click the Connect data source button, and then select the sample data at the top of the list. Running the data source automatically copies the files into your account, without the need to go through the download/upload process.

Once you have successfully imported the sample files into your account, click on the “Projects” tab. Once on the project page, click on the Create Envision script link at the bottom and pick a title name. You should see a new page featuring a near empty blank page. This view is called the code editor, and it allows you edit an Envision script. Copy the code below, and then click on the green button Start Run on the right.

/// This is my first sample script.
read "/sample/Lokad_Items.tsv" as Items with
  Id : text
  Name : text
  StockOnHand : number
  StockOnOrder : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  Quantity : number
  NetAmount : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
  Id : text
  Date : date
  NetAmount : number

oend = max(Orders.Date)

// Top indicators about the dataset
show label "This is a sample script" a1f1 tomato
show scalar "Product Lines" a2b2 with sum(Items.1)
show scalar "Order Lines" c2d2 with sum(Orders.1)
show scalar "Purchase Order Lines" e2f2 with sum(PO.1)

// Two linecharts
Week.sold = sum(Orders.NetAmount)
Week.purchased = sum(PO.NetAmount)
lastDay = monday(oend)
firstDay = lastDay - 52 * 7
when date >= firstDay and date < lastDay
  show linechart "Purchased and sold" a3f4 tomato with
    Week.sold as "Sold"
    Week.purchased as "Purchased"
// Worst rotating inventory
lastYear = oend - 365
UnitSold = sum(Orders.Quantity) when date > lastYear
UnitStock = max(1, StockOnHand + StockOnOrder)
Turns = UnitSold  / UnitStock
show table "Slowest inventory turns" a5f8 with
  order by Turns

Once the script execution is complete, a new green line should appear below the Start Run button. The color green indicates that the operation was completed successfully. Click on this line, and this will bring you to the newly generated dashboard. Click the top button Edit Script above the dashboard to get back to the code editor. Now, you know how to navigate back and forth between the dashboard view and the code editor view.


Understanding what’s going on

At this point, depending on your familiarity with programming concepts, the script above might still feel quite cryptic. Let’s review this script line by line in order to understand what is going on.

On line 1, we have a line that starts with //. All such lines are comment. They do not play any role in the logic of the script, and are only provided for readability purposes. The first line can even have a triple dash ///. With this triple dash, this line becomes the project subtitle, and gets displayed below the project title in the project list view.

On lines 2 to 4, we have three read statements that load the data needed to create the dashboard. Each of these statements uploads one file.

On line 6, we define a variable that stores the date of the latest order made. It is useful to define it, because this date will be used several times in the rest of the script.

On lines 8 to 15, we have the first two rows of the dashboard, which comprises 4 tiles in total. The topmost tile is merely a label, while the three tiles below are the simple 1x1 tables containing only a single indicator. As we will see below, tables can also be used to display actual tabular data, not just a single value at a time.

On lines 17 to 25, we define the line chart that is displayed immediately below the three small tiles. We begin by computing weekly totals for both sales and purchases on lines 18 and 19. We then define on lines 20 and 21 the latest and the earliest date we want to consider: respectively the last monday and the monday 52 full weeks before. On line 22, we filter the data in order to keep only the last 52 full weeks. The logic is a tiny bit complex, but it boils down to a start condition and an end condition. Line 23 begins with 2 spaces at the very beginning of the line. These spaces are important: the when defines a block, and everything that is within this block, i.e. all the lines that have the 2 extra spaces, get filtered accordingly. Finally, the line chart is defined in the lines 23 to 25. In line 23, we set the title, color and position of the chart. In lines 24 and 25, we define the two curves that are displayed. Note that both lines start with 4 spaces: 2 spaces needed by the when bloc and 2 extra spaces for the show block.

On lines 27 to 37, we have the table that comes below the line chart. This table displays the top list of products that are rotating the least within one’s inventory. On lines 28 and 29, we compute the number of units sold for each product over the last 365 days. On line 30, we compute the inventory for every unit. The max() function is merely used to avoid divisions by zero in line 31 if a products happens to have zero inventory. On line 31, we compute the inventory turns, i.e. number of rotations of the inventory within a year. On lines 32 to 37, we have the table display statement itself. In line 32, we set the title and the position of the title. From line 33 to 36, we define all the columns of the table: one line per column, from right to left. Finally, in line 37, we use the order by option in order to put the lower turns at the top of the table.

Moving forward

Unless you are already familiar with programming, the amount of information in this first script probably feels a little overwhelming. However, as you can see, within just 37 lines of code it is possible to compose a non-trivial dashboard that includes KPIs, some data visualization and a prioritized action list of products that probably requires some attention in order to avoid having these products clutter your inventory. By using less than 40 lines of script, we have already gone quite far.

More generally, the syntax used to compose calculation expressions is very similar to the one in Excel. For example, the total stock value taking into account both the stock-on-hand and the stock-on-order could be written as sum(PurchasePrice * (StockOnHand + StockOnOrder)). Don’t hesitate to experiment with this sample, modifying some bits of the script and immediately observing the corresponding effects on your newly generated dashboards.