Measures
This table shows the sales order line fact measures:
| Measure | Mapping | Note |
|---|---|---|
| Average Deal Size | Net Amount / Sales Order Count Distinct | |
| Average Purchase Value | Net Amount / Sales Order Line Count | |
| Back Order Line | 1 if Order Line Type (tdsls401.oltp) = Backorder, else 0 | |
| Back Order Line Ratio | Back Order Line / Sales Order Line Count | |
| Back Order Quantity | tdsls401.qbbo | |
| Blocked | 1 if tdsls401.bkyn = yes, else 0 | |
| COGS Amount | For delivered quantity, COGS amount is read from Sales Order
Actual Delivery Line COGS (tdsls409.cogs is read for tdsls409.csto = no) For open quantity, COGS amount is read from Sales Order Estimated COGS (tdsls415.cogs is read for tdsls415.csto = no) |
(1) |
| COGS Amount Customer Owned | For delivered quantity, customer owned COGS amount is read from
Sales Order Actual Delivery Line COGS (tdsls409.cogs is read for tdsls409.csto =
yes) For open quantity, customer owned COGS amount is read from Sales Order Estimated COGS (tdsls415.cogs is read for tdsls415.csto = yes) |
(1) |
| COGS Ratio | COGS Amount / Gross Amount | |
| Commissions Amount | Order Commissions Amount + Line Commissions Amount | |
| Commissions Ratio | Commissions Amount / Gross Amount | |
| Contract Ignored | 1 if Contract Ignored (tdsls401.cnig) = Yes, else 0 | |
| Contract Ignored Ratio | Contract Ignored / Sales Order Line Count | |
| Contract Linked | 1 if tdsls401.cono is filled, else 0 | |
| Contract Linked Ratio | Contract Linked / Sales Order Line Count | |
| Contract Not Available | Sales Order Line Count - Contract Linked - Contract Ignored | |
| Contract Not Available Ratio | Contract Not Available / Sales Order Line Count | |
| Customer Count Distinct | Distinct customer count | |
| Delivered | 1 if Sales Order Line Status is Delivered, else 0 (Delivered means Ordered Quantity > 0 and Delivered Quantity >= Ordered Quantity) |
|
| Delivered Early Days | Planned Delivery Date - Delivery Date (tdsls401.ddta -
tdsls401.dldt) 0 if negative |
|
| Delivered Late Days | Delivery Date - Planned Delivery Date (tdsls401.dldt -
tdsls401.ddta) 0 if negative |
|
| Delivered Quantity | tdsls401.qidl | |
| Discount Amount | Gross Amount - Net Amount | |
| Discount Ratio | Discount Amount / Gross Amount | |
| Early | 1 if Delivery Date is before Planned Delivery Date
(tdsls401.dldt < tdsls401.ddta) 0 if Delivery Date is on or after Planned Delivery Date (tdsls401.dldt >= tdsls401.ddta) 0 if not delivered (no Delivery Date: tdsls401.dldt = 0) |
|
| Early Ratio | Early / (Early + On Time + Late) | |
| External Sales Rep Count Distinct | Distinct external sales rep count | |
| Gross Amount | Quantity * Price
|
(1) |
| Gross Margin Amount | Net Amount - COGS Amount (= Profit Amount) | |
| Gross Margin Ratio | Gross Margin Amount / Gross Amount (= Profit Ratio) | |
| Gross Price | Gross Amount / Ordered Quantity | |
| Hold Back Quantity | tdsls401.qohb | |
| Invoice Only | 1 if Sales Order Line Status is Invoice Only, else 0 (Invoice Only means Ordered Quantity = 0) |
|
| Invoiced | 1 if Invoice Date exists (tdsls401.invd <> 0), else 0 | (3) |
| Item Count Distinct | Distinct item count | |
| Late | 1 if Delivery Date is after Planned Delivery Date (tdsls401.dldt
> tdsls401.ddta) 0 if Delivery Date is before or on Planned Delivery Date (tdsls401.dldt <= tdsls401.ddta) 0 if not delivered (no Delivery Date: tdsls401.dldt = 0) |
|
| Late Ratio | Late / (Early + On Time + Late) | |
| Line Commissions Amount | tdcms050.cmam for tdcms050.type = Commission and tdcms050.pono <> 0 | |
| Line Discount Amount | tdsls401.amld | |
| Line Promotion Discount Amount | tdsls401.tprd | |
| Line Rebates Amount | tdcms050.cmam for tdcms050.type = Rebate and tdcms050.pono <> 0 | |
| Net Amount | gross amount – discounts For the delivered quantity, the discounts are order discount amount, line discount amount and total promotion discount from Sales Order Actual Delivery Lines (tdsls406.amod, tdsls406.amld and tdsls406.tprd). For the open quantity, the discounts are total promotion discount from Sales Order (tdsls400.tprd) and order discount amount, line discount amount and total promotion discount from Sales Order Lines (tdsls401.amod, tdsls401.amld and tdsls401.tprd). |
(1) |
| Net Amount Ratio | Net Amount / Gross Amount | |
| Net Margin Amount | Gross Margin Amount - Commissions Amount - Rebates Amount | |
| Net Margin Ratio | Net Margin Amount / Net Amount | |
| Net Price | Net Amount / Ordered Quantity | |
| New Customer Sales | 1 if no sales order lines exist for the Sold-to BP (tdsls400.ofbp) in the quarter before the Invoice Date (tdsls401.invd) or if not invoiced, the Planned Delivery Date (tdsls401.ddta). Else, 0. | (4) |
| New Customer Sales Ratio | New Customer Sales / Sales Order Line Count | |
| New Item Sales | 1 if no sales order lines exist for the Item (tdsls401.item) in
the quarter before the Invoice Date (tdsls401.invd) or if not invoiced the Planned
Delivery Date (tdsls401.ddta) 0 otherwise |
(4) |
| New Item Sales Ratio | New Item Sales / Sales Order Line Count | |
| On Time | 1 if Delivery Date is on Planned Delivery Date (same day)
(tdsls401.dldt = tdsls401.ddta) 0 if Delivery Date is before or after Planned Delivery Date (tdsls401.dldt <> tdsls401.ddta) 0 if not delivered (no Delivery Date: tdsls401.dldt = 0) |
|
| On Time Ratio | On Time / (Early + On Time + Late) | |
| Open | 1 if Sales Order Line Status is Open, else 0 (Open means Ordered Quantity > 0 and Delivered Quantity = 0) |
|
| Order Commissions Amount | tdcms050.cmam for tdcms050.type = Commission and tdcms050.pono = 0 (divided across lines by gross amount) | |
| Order Discount Amount | tdsls401.amod | (1) |
| Order Promotion Discount Amount | tdsls400.tprd (divided across lines by gross amount) | |
| Order Rebates Amount | tdcms050.cmam for tdcms050.type = Rebate and tdcms050.pono = 0 (divided across lines by gross amount) | |
| Ordered Quantity | tdsls401.qoor, but if Back Order Confirmed (tdsls401.bqco) then Ordered Quantity – Back Order Quantity | |
| Partially Delivered | 1 if Sales Order Line Status is Partially Delivered, else 0 (Partially Delivered means Quantity Delivered < Ordered Quantity and Quantity Delivered > 0) |
|
| Perfect Order Line | 1 if not late and no shortage (Late = 0 and Shortage = 0) 0 if late or shortage |
|
| Perfect Order Line Ratio | Perfect Order Line / Sales Order Line Count | |
| Promotion Discount Amount | Order Promotion Discount Amount + Line Promotion Discount Amount | |
| Punctuality | (Early + On Time) / (Early + On Time + Late) | |
| Quantity Negative Variance | -1 * Quantity Variance if negative, else 0 | |
| Quantity Positive Variance | Quantity Variance if positive, else 0 | |
| Quantity Variance | Delivered Quantity – Ordered Quantity | |
| Rebates Amount | Order Rebates Amount + Line Rebates Amount | |
| Rebates Ratio | Rebates Amount / Gross Amount | |
| Repeat Customer Sales | 1 if New Customer Sales = 0 and 0 if New Customer Sales = 1 | (4) |
| Repeat Item Sales | 1 if New Item Sales = 0 and 0 if New Item Sales = 1 | (4) |
| Return | 1 if Sales Order Line Status is Return, else 0 | |
| Return Ratio | (Return means Ordered Quantity < 0) Return / Sales Order Line Count |
|
| Sales Growth Amount | Net Amount current year - Net Amount previous year | (5) |
| Sales Order Count Distinct | Distinct sales order count | |
| Sales Order Line Count | Always 1 but 0 for null records | |
| Shortage | 1 if Back Order Quantity is more than 0.0 1 if Sales Order Line Status is Partially Delivered Else 0 |
(2) |
| Shortage Ratio | Shortage / Sales Order Line Count |
- (1) Gross and net amount are calculated in LN using tdsls.dll4000.calculate.order.line.amounts.for.order.totals. COGS Amount is calculated in LN using tdsls.dll4000.retrieve.order.line.total.cost.prices.in.order.currency. The calculations aggregate amounts to sales order line level and are published along with the tdsls401 records. The Sales Order Actual Delivery Lines (tdsls406) table is not part of the data sets and is not published from LN. Mentioned functions are also used to calculate amounts in the Sales Order Margin (tdsls0588m000) session.
- (2) Shortage compares the ordered quantity with the delivered quantity for sales orders that have status Closed. Shortage is always set to 0 for orders that have not been closed.
- (3) 1 means sales invoice is processed in Central Invoicing.
- (4) New Customer Sales and Repeat Customer Sales are based on the invoice
date on the sales order line (planned delivery date is used if the sales order line is not
invoiced). If a customer is new in a certain quarter, then all sales order lines in that
quarter count as new customer sales. After the quarter, sales order lines are considered
repeat customer sales.
The default bucket is Quarter. This bucket can be changed to Year, Quarter, or Month in user variable LN_usr_ReportNewCustomerBucket.
For New Item Sales and Repeat Item Sales, the default is Year. This bucket can be changed to Year, Quarter, or Month in user variable LN_usr_ReportNewItemBucket.
- (5) Sales Growth Amount is based on the invoice date on the sales order
line (planned delivery date is used if the sales order line not invoiced). Sales order lines
with an invoice date in the current quarter are compared with sales order lines with an
invoice date in the previous quarter. The net amount of the previous quarter is subtracted
from the net amount of the current quarter.
The default period used is Quarter. This period can be changed to Year, Quarter, or Month in user variable LN_usr_ReportSalesGrowthPeriod.