read "/sample/Lokad_Items.tsv" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO show label "Stock value analysis" a1f1 tomato oend := max(Orders.Date) // Moneda predeterminada y conversiones de moneda 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) // Valoración de inventario FIFO PPO.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) // Antigüedad de inventario FIFO StockAge = sum(PO.Fifo * (oend - PO.Date)) /. sum(PO.Fifo) // Primera línea de KPI show table "Stock on hand (FIFO){ \{currency}}" a2b2 with sum(Fifo - Fifo2) show table "Stock on order (FIFO){ \{currency}}" c2d2 with sum(Fifo2) show table "Items in stock" e2 with count(StockOnHand + StockOnOrder > 0) when date > oend - 31 where sum(Orders.Quantity) by Id > 0 show table "Items unstocked" f2 with sum(1) // Stock por categoría y proveedor show barchart "Stock by category" a3c5 tomato with sum(Fifo) group by Category order by sum(Fifo) desc show barchart "Stock by supplier{ \{currency}}" d3f5 tomato with sum(Fifo) group by Supplier order by sum(Fifo) desc // Artículos con más stock, artículos con stock más antigüo show table "Most heavy stock items" a6c8 with Id, Name, StockOnHand as "On Hand" StockOnOrder as "On Order" Fifo as "Value{ \{currency}}" order by Fifo desc show table "Most aged stock items" d6f8 with Id Name StockOnHand as "On Hand" Fifo as "Value{ \{currency}}" StockAge as "Age (days)" order by Fifo * StockAge desc //combina antigüedad y valor adrede // Artículos con más presión de stock 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 { days}" d9f11 tomato with sum(V * StockAge) / max(1, sum(V)) group by Supplier order by sum(V * StockAge) / max(1, sum(V)) desc