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