Supplier's Analysis

The script below illustrates how a supplier’s analysis dashboard can be composed with Envision.

read "/sample/Lokad_Items.tsv" as Items with
  Id : text
  Category : text
  Supplier : text
  "LeadTime" as SupplierLeadTime : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  NetAmount : number
  Currency : text
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
  Id : text
  Date : date
  DeliveryDate : date
  Quantity : number
  NetAmount : number
  Currency : text

show label "Supplier's analysis" a1f1 tomato

PO.Supplier = same(Supplier) by Id at PO.Id
PO.SupplierLeadTime = same(SupplierLeadTime) by Id at PO.Id
PO.OrderId = "\{PO.Date}-\{PO.Supplier}"

oend = max(Orders.Date)
lastMonday = monday(oend)

// Default currency, and currency conversions
where 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)

// Top KPIs
show label "Current year" a2b2
where date >= oend - 365
  show scalar "Purchased " c2 unit:" \{currency}" with sum(PO.NetAmount)
  show scalar "Suppliers" d2 with distinct(PO.Supplier)
  show scalar "POs" e2 with distinct(PO.OrderId)
  show scalar "Units purchased" f2 with sum(PO.Quantity)
  show table "Units per order" g2 with
    avg(sum(PO.Quantity) by PO.OrderId)

show label "Previous year" a3b3
where date >= oend - 2 * 365 and date < oend - 365
  show scalar "Purchased " c3 unit:" \{currency}" with sum(PO.NetAmount)
  show scalar "Suppliers" d3 with distinct(PO.Supplier)
  show scalar "POs" e3 with distinct(PO.OrderId)
  show scalar "Units purchased" f3 with sum(PO.Quantity)
  show table "Units per order" g3 with
    avg(sum(PO.Quantity) by PO.OrderId)

// Linecharts
Week.purchased = sum(PO.NetAmount)
where date < lastMonday and date >= lastMonday - 52 * 7
  show linechart "Weekly purchase volumes" a4g6 tomato unit:" \{currency}" with
    Week.purchased as "Current year"
    Week.purchased[-52] as "Previous year"

// Split by supplier
show barchart "Purchase by supplier (1 year)" a7c8 tomato unit:" \{currency}" with
  sum(PO.NetAmount)
  group by PO.Supplier

show barchart "Purchase by category (1 year)" e7g8 tomato unit:" \{currency}" with
  sum(PO.NetAmount)
  group by Category

// Supplier analysis with lead times
where date >= oend - 365
  show table "Supplier analysis (1 year)" a9g11 tomato with
    PO.Supplier as "Supplier"
    distinct(PO.OrderId) as "POs"
    round(avg(mode(PO.DeliveryDate - PO.Date) \
      by PO.OrderId),2) as "Supplier Lead Time" unit:" days"
    round(365 / distinct(PO.OrderId),2) as "Ordering Lead Time" unit:" days"
    avg(sum(PO.NetAmount) \
      by PO.OrderId) as "Avg POs amount" unit:" \{currency}"
    avg(sum(PO.Quantity) by PO.OrderId) as "Avg POs units"
    group by PO.Supplier
    order by sum(PO.NetAmount) desc