read "/sample/Lokad_Items.tsv" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO show label "Gross margin analysis (LIFO inventory cost)" a1f1 tomato Orders.OrderId = "\{Orders.Date}-\{Orders.Client}" oend := max(Orders.Date) // Moneda predeterminada y conversiones predeterminadas 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) // Valor de inventario LIFO PO.UnitPrice = PO.NetAmount / max(1, PO.Quantity) Orders.CostAmount = latest(PO.UnitPrice) * Orders.Quantity Orders.Margin = Orders.NetAmount - Orders.CostAmount Orders.Supplier = latest(PO.Supplier) // Fila superior de KPI show label "Current year" a2 when date > oend - 365 show table "Gross margin{ \{currency}}" b2 with sum(Orders.Margin) show table "Gross margin{%}" c2 with sum(Orders.Margin) / sum(Orders.NetAmount) show table "Orders" d2 with distinct(Orders.OrderId) show table "Margin per order{ \{currency}}" e2 with sum(Orders.Margin) / distinct(Orders.OrderId) show table "Margin per unit{ \{currency}}" f2 with sum(Orders.Margin) / sum(Orders.Quantity) show label "Previous year" a3 when date > oend - 2 * 365 & date <= oend - 365 show table "Gross margin{ \{currency}}" b3 with sum(Orders.Margin) show table "Gross margin{%}" c3 with sum(Orders.Margin) / max(1, sum(Orders.NetAmount)) show table "Orders" d3 with distinct(Orders.OrderId) show table "Margin per order{ \{currency}}" e3 with sum(Orders.Margin) / max(1, distinct(Orders.OrderId)) show table "Margin per unit{ \{currency}}" f3 with sum(Orders.Margin) / max(1, sum(Orders.Quantity)) // Gráfico de líneas Week.margin := sum(Orders.Margin) when date >= monday(oend) - 52 * 7 & date < monday(oend) show linechart "Weekly gross margin{ \{currency}}" a4f6 tomato with Week.margin as "Current year", Week.margin[-52] as "Previous year" // Dividido por categoría y proveedor when date > oend - 365 show barchart "Margin per category (1 year){ \{currency}}" a7c9 tomato with sum(Orders.Margin) group by Category show barchart "Margin per supplier (1 year){ \{currency}}" d7f9 tomato with sum(Orders.Margin) group by Orders.Supplier // Éxitos y fracasos when date > oend - 365 where sum(Orders.NetAmount) > 0 show table "Most profitable items (1 year)" a10c12 with Id Name sum(Orders.Margin) as "Margin{ \{currency}}" sum(Orders.Margin) / sum(Orders.NetAmount) as "Margin{%}" order by sum(Orders.Margin) desc R = rank(sum(Orders.Margin)) where R <= 0.2 * max(R) by 1 show table "Least profitable items (1 year, among top 20%)" d10f12 with Id Name sum(Orders.Margin) as "Margin{ \{currency}}" sum(Orders.Margin) / sum(Orders.NetAmount) as "Margin{%}" order by sum(Orders.NetAmount) / sum(Orders.Margin) desc