Sales

This section lists the Key Performance Indicators (KPI) for the Sales domain and the calculation used to determine their results.
KPI name KPI calculation
Profit/Margin
(([Extended_Amount] – [Discount_Amount]) – ([Extended_Product_Cost] – [Rebate_Amount]))
Extended Net Amount
[Extended_Amount] – [Discount_Amount]
Net Sales
[oeel_invoicedt: Extended Net Amount]
[ExtAmtByDiscPct]=[OEEL.price] * [OEEL.qtyship]*(100-[OEEL.discpct])* [ICSS.csunperstk]*([OEEH.salesexratee](If any))
Fill Rate Order
IF([oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] = 0 or [oeeh_invoicedt: # OEEH_shipcom_flag] is null,0, (([oeeh_invoicedt: # OEEH_shipcom_flag] where [OEEH.OEEH_shipcom_flag] ='Yes') / ([oeeh_invoicedt: # orderno])) * 100)
Fill Rate Line
IF([oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] = 0 or [oeel_invoicedt: Sum: oeel_shipcomplete_Boolean] is null,0,([oeel_invoicedt: Sum: oeel_shipcomplete_Boolean]/[oeel_invoicedt: # lineno])*100)
Fill Rate Unit
IF([oeel_invoicedt: Sum: oeel_qtyship] is null or [oeel_invoicedt: Sum: oeel_qtyship] =0 or [oeel_invoicedt: Sum: oeel_qtyord] = 0 or [oeel_invoicedt: Sum: oeel_qtyord] is null,0,([oeel_invoicedt: Sum: oeel_qtyship]/[oeel_invoicedt: Sum: oeel_qtyord])*100)
On Time
IF([OEEH.oeeh_shipdt] is null or [OEEL.oeel_origpromisedt] is null,1,IIF( DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) = 0,1,0))
On Time 2 Days
IF([OEEH.oeeh_shipdt] is null or [OEEL.oeel_origpromisedt] is null,1,IIF( DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) > 2 or DATEDIFF(DAY,[OEEH.oeeh_shipdt],[OEEL.oeel_origpromisedt]) < -2,0,1))
On Time 5 Days
IF((IFNULL([OEEL.oeel_qtyord],0.00)-IFNULL([OEEL.oeel_qtyship],0.00)) > 0,0,1 )
On Time Orders Perc
IF([oeeh_invoicedt: Sum: oeeh_ontime] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime] / [oeeh_invoicedt: # orderno])*100 )
On Time Orders 2 Days Perc
IF([oeeh_invoicedt: Sum: oeeh_ontime_2days] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime_2days] / [oeeh_invoicedt: # orderno])*100 )
OE On Time Orders 5 Days Perc
IF([oeeh_invoicedt: Sum: oeeh_ontime_5days] is Null or [oeeh_invoicedt: # orderno] is null or [oeeh_invoicedt: # orderno] =0 ,0,([oeeh_invoicedt: Sum: oeeh_ontime_5days] / [oeeh_invoicedt: # orderno])*100 )
On Time Lines Perc
IF([oeel_invoicedt: Sum: oeel_ontime] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime] / [oeel_invoicedt: # lineno])*100 )
On Time Lines 5 Days Perc
IF([oeel_invoicedt: Sum: oeel_ontime_5days] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime_5days] / [oeel_invoicedt: # lineno])*100 )
On Time Lines 2 Days Perc
IF([oeel_invoicedt: Sum: oeel_ontime_2days] is Null or [oeel_invoicedt: # lineno] is null or [oeel_invoicedt: # lineno] =0 ,0,([oeel_invoicedt: Sum: oeel_ontime_2days] / [oeel_invoicedt: # lineno])*100 )([aret_invdt: Sum: aret_Open Amount] where ([ARET.aret_transcode_description] = 'Scheduled Pay' or [ARET.aret_transcode_description] = 'Debit Memo' or [ARET.aret_transcode_description] = 'Credit Memo' or
OE Line Commission Profit
([aret_invdt: Sum: aret_Open Amount(([Extended_Amount] – [Discount_Amount]) – ([Extended_Commission_Cost] – [Rebate_Amount]))
Margin %
IF([oeel_invoicedt: Extended Net Amount] IS NULL OR [oeel_invoicedt: Extended Net Amount] = 0,0,(((IFNULL([oeel_invoicedt: Sum: Extended_Amount],0.00)-IFNULL([oeel_invoicedt: Sum: Discount_Amount],0.00)) - (IFNULL([oeel_invoicedt: Sum: Extended_Product_Cost],0.00) - IFNULL([oeel_invoicedt: Sum: Rebate_Amount],0.00))) / [oeel_invoicedt: Extended Net Amount])*100)
Rebate Amount
IIF([PDER.pder_seqno] <> 0 AND [PDER.pder_rebatecd] <> 's',0,IIF( ([OEEL.oeel_returnfl] = 'yes' or  [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_ speccostty] is null,((IFNULL([OEEL.oeel_qtyship] ,0.00) * IFNULL([PDER.pder_rebateamt],0.00)) * -1), IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00) *  IFNULL([PDER.pder_rebateamt],0.00)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder _rebateamt],0.00))* ([ICSS.icss_csunperstk] / 100)) ,
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder _rebateamt],0.00))* ( 1 / 100)) ,
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0, (((IFNULL( [OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER .pder_rebateamt],0.00))* ([ICSS.icss_csunperstk]  / 100)) * -1),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)) *(1/100))*-1),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ([ICSS.icss_csunperstk] / 1000)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ( 1 / 1000)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0, (((IFNULL([OEEL.oeel_stkqtyship],0.00)  * IFNULL([PDER.pder_rebateamt],0.00))*  ([ICSS.icss_csunperstk] / 1000)) * -1),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)) *(1/1000))*-1),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk]  > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))* ([ICSS.icss_csunperstk])),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) * IFNULL([PDER.pder_rebateamt],0.00))*([ICSS.icss_csunperstk])) *-1) ,
IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([PDER.pder_rebateamt],0.00))*-1), IIF([ICSS.icss_csunperstk] IS NULL, 0, 0))))))))))))))))
Extended Product Cost
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and  [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_ csunperstk] = 0, ((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([OEEL.oeel_prodcost],0.00))*-1),
IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS. icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL. oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_prodcost],0.00))*([ICSS. icss_csunperstk]*-1)),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship] ,0.00)*IFNULL([OEEL.oeel_prodcost],0.00)),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00)) * ([ICSS.icss_csunperstk])),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] is null,((IFNULL( [OEEL.oeel_qtyship],0.00)*IFNULL([OEEL. oeel_prodcost],0.00))*-1),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and  [ICSS.icss_csunperstk] > 0,((IFNULL( [OEEL.oeel_stkqtyship], 0.00)*IFNULL([OEEL.oeel_prodcost],0.00))/( [ICSS.icss_csunperstk]/100)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL( [OEEL.oeel_stkqtyship],0.00)*IFNULL([ OEEL.oeel_prodcost],0.00))*(1/100)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*[ICSS.icss_csunperstk])/100) * -1,
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/100))*-1),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and  [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL. oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost] ,0.00))*[ICSS.icss_csunperstk])/1000),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/1000)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and  [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL. oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))* [ICSS.icss_csunperstk])/1000) * -1,
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_prodcost],0.00))*(1/1000))*-1),
IIF([ICSS.icss_csunperstk] IS NULL, 0,0)))))))))))))))
Extended Commission Cost
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*-1) ,IIF(([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'Y' and [ICSS .icss_csunperstk] > 0,((IFNULL([OEEL.oeel_ stkqtyship],0.00)*IFNULL([OEEL.oeel_commco st],0.00))*([ICSS.icss_csunperstk]*-1)),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] = 0,(IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)),
IIF(([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'Y' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)) * ([ICSS.icss_csunperstk])),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] is null,((IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*-1),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] is null,(IFNULL([OEEL.oeel_qtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_commcost],0.00))/([ICSS. icss_csunperstk]/100)),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*(1/100)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/100) * -1,
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'H' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*(1/100))*-1),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/1000),
IIF( ([OEEL.oeel_returnfl] = 'no' or [OEEL.oeel_returnfl] = 'false') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,((IFNULL([OEEL.oeel_stkqtyship],0.00)* IFNULL([OEEL.oeel_commcost],0.00))*(1/1000)),
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] > 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00)*IFNULL([OEEL.oeel_commcost],0.00))*[ICSS.icss_csunperstk])/1000) * -1,
IIF( ([OEEL.oeel_returnfl] = 'yes' or [OEEL.oeel_returnfl] = 'true') and [ICSS.icss_speccostty] = 'T' and [ICSS.icss_csunperstk] = 0,(((IFNULL([OEEL.oeel_stkqtyship],0.00) *IFNULL([OEEL.oeel_commcost],0.00))*(1/1000)) *-1),
IIF([ICSS.icss_csunperstk] IS NULL, 0,0)))))))))))))))
OE Line Commission Margin
((([oeel_invoicedt: Sum: Extended_Amount]-[oeel_invoicedt: Sum: Discount_Amount]) - ([oeel_invoicedt: Sum: Extended_Commission_Cost] - [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])
Commission Margin %
((([oeel_invoicedt: Sum: Extended_Amount]-[oeel_invoicedt: Sum: Discount_Amount]) - ([oeel_invoicedt: Sum: Extended_Commission_Cost] - [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])*100
Commission Amount
[oeel_commamtout]
Commission Cost
[Rebate Commission Cost]
Discount Amount
If ([OEEL.oeel_returnfl] = 'yes', ([OEEL.oeel_wodiscamt] + [OEEL.oeel_discamtoth]) * -1,([OEEL.oeel_wodiscamt] + [OEEL.oeel_discamtoth]))
Extended Amount
Rebate Cost
[Extended_Product_Cost] - [Rebate_Amount]If ([OEEL.oeel_returnfl]='yes', IFNULL([OEEL.oeel_netamt],0.00) * -1,IFNULL([OEEL.oeel_netamt],0.00))
Rebate Commission Cost
[Extended_Commission_Cost] - [Rebate_Amount]
Number of Orders
Count of Total orders (#orderno)
Total Sales
SUM (Oeeh_totinvamt) By Oeeh_invoicedt
Total Profit
(IFNULL([oeel_invoicedt: Sum: Extended_Amount],0.0) - IFNULL(SavedExpression('Extended product cost'),0.0))/IFNULL([oeel_invoicedt: Sum: Extended_Amount],1.0)
Gross Margin
(([Extended_Amount] – [Discount_Amount]) – ([Extended_Product_Cost] – [Rebate_Amount]))
Gross Margin %
((([oeel_invoicedt: Sum: Extended_Amount] – [oeel_invoicedt: Sum: Discount_Amount]) – ([oeel_invoicedt: Sum: Extended_Product_Cost] – [oeel_invoicedt: Sum: Rebate_Amount])) / [oeel_invoicedt: Sum: Extended_Amount])*100
Cost
[Rebate Cost]
Line Extended Margin by Discount Percentage
[oeel_line_extended_margin]=([ExtAmtByDiscPct]-[DiscAmt])-([PRODCOST]-[RebAmt])
Extended Sales by Discount Percentage
[oeel_extended_sales_by_discpct]=([ExtAmtByDiscPct]-[DiscAmt])