Sample Stock Value Analysis with Envision

The sample dataset for Envision










Home » Resources » Here

bla bla

here a screenshot example of syntax

[image||{UP}/Products/Envision/aggregating-data.png]

link to the dashboard

Bla bla

why no number of lines, no syntax highlighting and yellow background
/// reads from /input/newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Category : text
  Supplier : text
  Name : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *]
read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO[Id, Date, *]

oend := max(Orders.Date)

// Default currency, and currency conversions
when date >= oend - 365
  currency := mode(Orders.Currency)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)
PO.NetAmount = forex(PO.NetAmount, PO.Currency, currency, PO.Date)

// FIFO inventory valuation
PO.Fifo = fifo(StockOnHand + StockOnOrder, PO.Date, PO.Quantity)
Fifo = sum(PO.Fifo * PO.NetAmount / PO.Quantity)

PO.Fifo2 = fifo(StockOnOrder, PO.Date, PO.Quantity)
Fifo2 = sum(PO.Fifo2 * PO.NetAmount / PO.Quantity)

// FIFO inventory age
StockAge = sum(PO.Fifo * (oend - PO.Date)) /. sum(PO.Fifo)

// Top row of KPIs
show label "Stock value analysis" a1f1 tomato

when date > oend - 31
where sum(Orders.Quantity) by Id > 0
  unstockedItems := sum(1)

show summary "Stocks information" a2f2 tomato with
  sum(Fifo - Fifo2) as "Stock on hand (FIFO)" unit:" \{currency}"
  sum(Fifo2) as "Stock on order (FIFO)" unit:" \{currency}"
  count(StockOnHand + StockOnOrder > 0) as "Items in stock"
  unstockedItems as "Items unstocked"

// Stock by category and supplier
show barchart "Stock by category" a3c5 tomato with
  sum(Fifo)
  group by Category
  order by sum(Fifo) desc

show barchart "Stock by supplier" d3f5 tomato unit:" \{currency}" with
  sum(Fifo)
  group by Supplier
  order by sum(Fifo) desc

// Most heavy stocked items, most aged stock items
show table "Most heavy stock items" a6c8 tomato with
  Id
  Name
  StockOnHand as "On Hand"
  StockOnOrder as "On Order"
  Fifo as "Value" unit:" \{currency}"
  order by Fifo desc

show table "Most aged stock items" d6f8 tomato with
  Id
  Name
  StockOnHand as "On Hand"
  Fifo as "Value" unit:" \{currency}"
  StockAge as "Age (days)"
  order by Fifo * StockAge desc

// Most pressured stock items
D = sum(Orders.Quantity) when date >= oend - 13 * 7
where StockOnHand + StockOnOrder <= 0 & D > 0
  show table "Most demanded unstocked items" a9c11 tomato with
    Id
    Name
    D as "Units sold last quarter"
    order by D desc

V = sum(Orders.NetAmount) when date >= oend - 13 * 7
where sum(V) by Supplier > 0
  show barchart "Stock age by supplier" d9f11 tomato unit:" days" with
    sum(V * StockAge) / max(1, sum(V))
    group by Supplier
    order by sum(Fifo) desc