KPI

From UG

Revision as of 21:13, 13 February 2013 (edit) Alex (Talk | contribs) (→Admin) ← Older edit		Current revision as of 13:24, 31 March 2014 (edit) (undo) Andrei (Talk | contribs) (→SOW 89 Cost per TEU)
(543 intermediate revisions not shown)
Line 1:		Line 1:
-	[[Category:DR KPI Phase One]]	+	[[Category:DRs and KPIs]]
	==Info==		==Info==
Line 5:		Line 5:
	=== Mantis ===		=== Mantis ===
-	Mantis:	+	parent: 3930
	* categories: '''DR/KPI:ph1''', '''DR/KPI:ph2''', '''DR/KPI:ph3'''		* categories: '''DR/KPI:ph1''', '''DR/KPI:ph2''', '''DR/KPI:ph3'''
Line 86:		Line 86:
	* Overlap		* Overlap
-		+	==== Individual KPIs ====
	'''Cost per unit (CPU)'''		'''Cost per unit (CPU)'''
Line 221:		Line 221:
	This is a title for current page (for selected tab).		This is a title for current page (for selected tab).
-	=== Main menu ===	+	=== Main Menu ===
-		+
-	* '''Client Company''' - single select, list restricted by user visibility	+
-	** '''this might soon be removed !!!'''	+
-		+
-	* '''MOT''' - single select	+
-		+
-	* '''View type''' - controls what is displayed inside panels. Options:	+
-	** Main View	+
-	** Shippers View	+
-	** Consignees View	+
-	** Carriers View	+
-	** Lanes View	+
-	** '''this might soon be removed !!!'''	+
-		+
-	* '''Ttl Weight/Avg Cost/Count/Avg Weight selector'''	+
-	** This defines axis Y for Top Right Panel	+
-	** '''this might soon be removed !!!'''	+
-	** '''not used for Main View ! '''	+
-	** options:	+
-	*** Total Weight	+
-	*** Average Cost	+
-	*** Shipment Count	+
-	*** Average Weight	+
	=== KPI Panel ===		=== KPI Panel ===
Line 257:		Line 234:
	*'''percentage''': change in % comparatively to previous period defined by "timeframe"		*'''percentage''': change in % comparatively to previous period defined by "timeframe"
-
-	*'''timeframe''': options: weekly, monthly, quarterly, yearly
	*'''traffic light''': Gage, indicates tolerance level, see [[#Tolerance levels parameters]]		*'''traffic light''': Gage, indicates tolerance level, see [[#Tolerance levels parameters]]
-	==== Tolerance levels parameters ====	+	==== WMQY selector ====
-	* this is a list of parameters managed by user in user profile	+	4 options:
-	* controls what is considered green vs yellow vs red zone for KPI	+
-	=== Left panel, Top right panel, Bottom right panel ===	+	* Weekly
		+	* Monthly
		+	* Quarterly
		+	* Yearly
-	Content on these panels is controlled by "View Type" selector. See details below.	+	==== Tolerance levels parameters ====
-	=== Views ===	+	This is aka "Traffic light"
-	We defined 5 views: Main, Consignees, Shippers, Carriers, Lanes.	+	* this is a list of parameters managed by user in user profile
		+	* controls what is considered green vs yellow vs red zone for KPI
-	See details below.	+	===== Notifications for Traffic light =====
-	=== Main View ===	+	* When color changes from yellow to red send e-mail with notification to user
-	Main View aka MV	+	* In user profile on Client under specific KPI allow to turn on/off notification. By default it is off
-	==== MV Left panel ====	+	=== Left panel ===
	Left column:		Left column:
Line 293:		Line 271:
	:* Mouse over value description		:* Mouse over value description
-	===== MV Left panel drilldown =====	+	* single click on a table row of the Left Panel functionality:
-		+
-	'''For Main view'''	+
-		+
-	:* single click on a table row of the Left Panel	+
	:** Highlight a table row for a particular week/quarter/month to the row that is selected		:** Highlight a table row for a particular week/quarter/month to the row that is selected
	:** A dot on the graph for a particular week/quarter/month on top right panel is highlighted. If there is no data (empty), no highlight is needed.		:** A dot on the graph for a particular week/quarter/month on top right panel is highlighted. If there is no data (empty), no highlight is needed.
	:** Apply a bottom right panel to show a particular week/quarter/month information chart for each tab (Shippers, Consignees, Carriers, and Lanes). Display a ''frame label'' to show what data/value based on such as ''X Week''/''Y Quarter''/''Z Quarter'' or ''2012 YTD average''		:** Apply a bottom right panel to show a particular week/quarter/month information chart for each tab (Shippers, Consignees, Carriers, and Lanes). Display a ''frame label'' to show what data/value based on such as ''X Week''/''Y Quarter''/''Z Quarter'' or ''2012 YTD average''
	:** ie. Cost per LB with weekly KPI, user clicks on 28 week (08-14 Jul 2012) then a dot on the graph for 28 week on the Top right is highlighted. The bottom right panel shows bar or pie segment chart with number for top/bottom display for a particular 28 week information. Display a ''frame label'' as '''28 week (08-14 July 2012)'''.		:** ie. Cost per LB with weekly KPI, user clicks on 28 week (08-14 Jul 2012) then a dot on the graph for 28 week on the Top right is highlighted. The bottom right panel shows bar or pie segment chart with number for top/bottom display for a particular 28 week information. Display a ''frame label'' as '''28 week (08-14 July 2012)'''.
		+
		+	==== Left panel drilldown ====
		+
		+
		+
	:* double clicks on a table row of Left Panel		:* double clicks on a table row of Left Panel
	:** Show pop-up panel with download to excel button		:** Show pop-up panel with download to excel button
Line 307:		Line 286:
	:** Download Excel spread sheet		:** Download Excel spread sheet
-	'''For Other views'''	+	=== Top Right Panel ===
-	Commented out this until we decide to enable other views.	+
-	<!--	+
-	''General information for other views''	+
-		+
-	:* Left Panel list of shippers/consignees/carriers/Lanes for current year to date KPI value	+
-	:* Top right panel shows graph for current YTD average, and last year YTD average for each shipper/consignee/carrier/lane's Total Weight/Average Cost/ Shipment Count/Average Weight selected depend upon view selected that is not Main view.	+
-	:* Bottom right panel shows bar or pie segment of chart for numbers of top/bottom display current YTD average for each tab (Total Weight, Average Cost, Shipment Count, Average Weight)	+
-		+
-		+
-	''Single click on a table row of the Left Panel''	+
-		+
-	:* Highlight a table row for a particular shipper/consignee/carrier/lane to the row that is selected	+
-	:* Weekly/Quarterly/Monthly buttons are available for user to select and view info for a particular period for a particular shipper/consignee/carrier/lane is selected. Default to Weekly for Left panel drill down.	+
-	:* Once Weekly/Quarterly or Monthly is selected then applies Top right panel by showing KPI value of a specific shipper/consignee/carrier/lane for each week/quarter/month depend upon a time frame button is selected.	+
-	:* ie. Cost per LB with MOT = Air and View = carrier view with Total Weight selected dropdown. Once user clicks on a specific carrier (Jet Blue) on the left Panel, then it applies the top right panel for the graph to show a particular carrier (Jet Blue) for weekly information with current year, last year, previous year, current YTD average, and last year YTD average (dot with line connected for the weeks that have data).	+
-	:* Also bottom right panel shows a particular week/quarter/month for ''current year'' information chart for each tab (Total Weight, Average Cost, Shipment Count, Average Weight). Display a ''frame label'' to show what data/value based on such as ''Weekly', ''Quarterly'', ''Monthly''.	+
-	:* ie. Per example above for carrier view, weekly, Air, Total Weight, it applies the top right panel as well as bottom right panel. This will help user compare the top right and bottom right information for side by side. User can flip to other tabs on the bottom right to compare with top right graph with Ttl Weight/Avg Cost/count/Avg Weight selector. It will show bar/pie segment top/bottom display for a particular carrier (Jet Blue) for weekly information (w1, w2, ...w52) for the current year information. Display a ''Frame label'' as '''weekly - 2012'''	+
-	-->	+
-		+
-	==== MV Top right panel ====	+
	Axis X:  all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)		Axis X:  all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)
Line 337:		Line 296:
	* etc... ;what is the oldest data to display is manager through KPI Admin		* etc... ;what is the oldest data to display is manager through KPI Admin
-	===== YTD average line =====	+	==== YTD average line ====
	This is a horizontal line on top right plot indicating "YTD average" for KPI, say cost per kg.		This is a horizontal line on top right plot indicating "YTD average" for KPI, say cost per kg.
-	===== Year Selector Panel =====	+	==== Year Selector Panel ====
	Has been added in SOW 33.		Has been added in SOW 33.
Line 362:		Line 321:
	[[File:Allyears.JPG]]		[[File:Allyears.JPG]]
-	==== MV Bottom right panel ====	+	=== Bottom right panel ===
-	This panel has 4 tabs: Shippers, Consignees, Carriers, Lanes.	+	==== Mock up ====
-	Each tab has same design.	+	[[File:Bottom right.JPG|800px]]
-	*Type selector: bar/pie	+	==== Lanes Tab ====
-	* Display X selector and Display Top/Bottom selector:	+
-	** indicating by how many items to group, for example: "show top 5 Shippers"	+
-	* Plot:	+
-	**Axis X: top/bottom 25, 20, 15, 10, 5 for Shippers or Consignees or etc	+
-	**Axis Y: KPI value	+
-	===== MV Bottom right panel drill down =====	+	Mapping: [[Sh#Shipper to Consignee Lane]].
-	* If user clicks on a particular bar or pie segment of chart then system should produce pop up with CT/load details for relate data (use same format as was defined by Cost per Pound KPI implemented by AK)	+	==== Bottom right panel drill down ====
-	:* ''' Bottom right chart'''. See figure below. This mock up is from Cost per LB with Load#. Other KPI might be slightly different such as for other MOTs that has no Load, do not show load.	+
-	:** Step 1). User can click on a bar or pie segment of chart.	+
-	[[File:BottomRightChart.JPG]]	+
-	:** Step 2). As of this panel shows bar chart per carrier for 2012 YTD average, user clicks on FEDEX FREIGHT bar chart on Carrier tab, then system shows pop up HTML report with ability to download to excel. This HTML report shows information for a specific carrier.	+
-	[[File:DrillDown.JPG]]	+
-	:** Step 3). User can download information to excel by clicking ''Download to Excel'' button then selecting ''output fields'' and clicking ''OK''. Excel report should show as small detail as possible per line for this particular carrier, so user can use the excel spreadsheet to create their own pivot tables, ...for their own analysis. Output list is upon user selecting output fields.	+
-	NOTE: Output fields lists should be available for user to select (similar to Main report) as many fields as possible.	+
-	[[File:SelectOutputFields.JPG]]	+
-	[[File:LoadList.JPG|500]]	+	see [[#SOW 71 Drilldowns v3]]
-	=== Other Views ===	+	=== Visibility ===
-	We decided to implement other views later or possibly have only one (Main) View.	+	For given KPI that is enabled for given Client user use only CT records that satisfy "default client user visibility" (E0 or E0 Group).
-	==== Carriers View ====	+	=== KPI Admin v1 ===
-	I commented out this section - use wiki Edit to see text.	+	==== System wide settings ====
		+
		+	Add Admin > KPI to manage system wide KPI module related settings.
		+
		+	Currently only one parameter to be managed: [[#KPI Earliest Date]].
		+
		+	===== KPI Earliest Date =====
		+
		+	Defines cut off date for CT records based on Created on date. Records created earlier than this date will not be used in KPIs.
		+
		+	==== User specific settings ====
		+
		+	Note that currently we have some sort of admin. This is re-design.
		+
		+	* On internal users with role=Client will be set up by Jag operators to get access to some KPIs.
		+
		+	* We need to have list of all KPIs available in the system be present in this UI.
		+
		+	* Also we should be able to enable KPI just for some MOTs.
		+
		+	* Need enable/disable drill down switch per user
		+
		+	* Finally many KPIs have "KPI parameters" (such as intervals for Gages). They should be set through this Admin as well.
		+
		+	* As far as layout It should have tree like structure:
		+
		+	* KPI class A
		+	** KPI 1
		+	** KPI 2
		+	** ...
		+
		+	* KPI class B
		+	** KPI 1
		+	** KPI 2
		+	** ...
		+
		+	Once some KPIs are enabled these client users should be able to manage enabled KPIs in their users profiles.
		+
		+	=== KPI Admin v2 ===
		+
		+	This is a version 2 of a logic that we did not get completely right first time due to the bugs, spec bugs, change requests.
		+
		+	Link to the spec for previous version of Admin: [[#KPI Admin]].
		+
		+	* 0/ '''(on hold!)''' All parameters will be managed for individual KPIs and not classes as programmed now. See mockups below
		+	:* 0.1/ Top level will look like this:
		+	[[File:Admin1.png | 300px]]
		+	:* 0.2/ If user selects Cost per Lb and clicks edit he will see pop up where he can edit indiv KPI settings:
		+	[[File:Admin2.png| 300px]]
		+	:* '''Design above is on hold pending Multi Editor Proposal'''
		+
		+	* 1/ '''(on hold!)''' Add CT2 Internal MAIN MENU > Admin > '''KPI Global Admin''' section to manage system wide KPI module related settings
		+	* 2a/ Make #KPI Earliest Date (now set to 01-Jan-2011) to be system wide:
		+	** 2.1a manage through servers config file
		+	** 2.2a display current value as read only in above mentioned Admin section
		+	** 2.3a remove this date as a view only date from '''individual KPI''' panel
		+	:* '''Design above is on hold pending Multi Editor Proposal !!!'''
		+
		+	* 2b/ '''(moved to SOW 50)''' tag each individual(!) KPI as internal vs external (hardcode)
		+
		+	* 3/ '''(on hold!)''' Add new parameter to individual KPIs: enable/disable client company filter (when visibility is linked to the client group)
		+	** [give 3 options: all together; All and list of individual E0 in the group; individual its only]
		+	** '''this is on hold - do not implement now !!!'''
		+
		+	* 4/ '''(on hold!)''' Add KPI section to Internal MAIN MENU > Admin > Client Companies
		+	** to manage KPI parameters
		+	** in this section we should:
		+	*** enable / disable individual '''(!)''' KPis for Client users linked to given company through default E0 visibility
		+	*** manage all parameters for this company
		+	** settings above will propagate into user profile on internal where they could be tuned further for specific user
		+	** changes in parent Client Comp record should propagate into user profile on internal
		+	** '''Design above is on hold pending Multi Editor Proposal !!!'''
		+
		+	* 5/ None of the settings needs to be available for change in users profile (by user) on Client. See mock up below:
		+
		+	[[File:KPi admin 4.png|200px]]
		+
		+	* 6/ Gage parameters changes:
		+	** 6.0/ Should be only managed through "Settings" panel right on the KPI page as well. (!) Remove These settings from Indiv KPI Admin.
		+	** 6.1/ Remove "From created on date" from this panel.
		+	** 6.2/ See mock up below:
		+
		+	[[File:KPI Admin 3.png|300px]]
		+
		+	==== KPI Admin v2 Double click functionality ====
		+
		+	* 7/ Double click functionality to edit should be available as well.
		+	** Currently it works when click on the list and on "edit" button. This functionality works well but would be great to allow double click feature to save time. As to do one by one by Edit button is time-consuming.
		+
		+
		+
		+	=== Misc Standards ===
		+
		+	==== Rounding ====
		+
		+	For selected period:
		+	* If values are max-min<=10 then system shows 3 decimals, else shows 2 decimals.
		+
		+	==== Shipment defined ====
		+
		+	see [[Sh#Shipment]]
		+
		+	==== MOTs ====
		+
		+	Use all MOTs.
		+
		+	If there is no data for given MOT - do not show it in selector.
		+
		+	==== Mappings ====
		+
		+	Shippers:
		+	* same for all MOTs
		+	* maps to [[Ct#Shipper]]
		+
		+	Consignees:
		+	* same for all MOTs
		+	* maps to [[Ct#Consignee]]
		+
		+	Carriers:
		+
		+	We have two fields for Trucking company:  Export Pick-up Trucker,  Delivery Trucker. '''So if one shipment has both fields set then I assume both companies get credit as far as shipment count and weight.' THIS NEEDS TO A NEW SOW !!!!''
		+
		+	==== Association Inclusion and Group logic ====
		+
		+	[[#SOW 53 Association Inclusion and Group logic]]
		+
		+	=== Parameters used in KPIs ===
		+
		+	==== Cost ====
		+
		+	Calculated as a sum of all sales invoices minus credit notes amounts across all CTs.
		+
		+	'''Exception is TMS case:''' in this case cost comes from TMS with loads.
		+
		+	this formula will change! - see [[#SOW 52 Cost v2]].
		+
		+	==== Gross Weight ====
		+
		+	Calculated as a sum of Gross Weights of individual CTs.
		+
		+
		+	=== Drilldowns v2 ===
		+
		+	Hidden - old version
	<!--		<!--
-	See mockup below: NOTE: Mock up below is incorrect. Under construction. It will be posted soon	+	This is a 2nd attempt to define and describe this feature with sufficient level of details. Particularly 1st version of spec did not have details about xls format and a list of fields to be selected for inclusion.
-	File:Body carriers view dr3 final 5.png	+	Drilldown is a functionality that gives "GRP/CT/Commodity item level details" for particular segment of visual representation for given KPI.
-	'''Left panel'''	+	Defined for:
		+	* selected time frame (week/month/quarter/year) defined on Left Panel
		+	* same data but restricted to specific Shipper/Consignee/Carrier/Lane(Bottom Right Panel)
-	Left column:	+	Produce pop-up where user can select output fields from this list - see below.
-	* Carriers	+
-	Right column:	+	==== HTML view ====
-	* KPI value (Current YTD)	+
-	'''KPI Panel'''	+	No need to show html view first ('''change'''!).
-	Action 1: Carrier view is selected	+	==== List of fields and Field Selection Pop Up for Drilldown ====
-	* Time frame: options for weekly, monthly are '''hiding'''	+	Standard list of fields for all KPIs:
-	* Show number and percentage	+
-	* Show Total Weight/Cost/Count/Avg Weight selector	+
-	* Arrow change indicator and traffic light are '''hiding'''	+
-	Action 2: A particular row of Left panel is selected such as Jet blue	+	<pre>
-	* Jetblue is selected (one click)	+	CT attributes (all KPIs):
-	:* Time frame: options for weekly, monthly are '''appeared''' and available for user to select	+	-------------------------
-	:** Default: Weekly	+
-	:* Top right Panel: show graph for a particular carrier (Jet blue) for weekly information (w1, w2...)	+	GRP#(Load#)
-	:* Show number for that particular carrier KPI (avg last full week/month) value and percentage compared last full week/month to previous week/month	+	CT#
-	:* Bottom right Panel: show chart for a particular carrier (Jet blue), apply to all tabs (Total Weight, Average Cost, Sgupment Count, Average Weight)	+	Shipper
		+	Created On Date
		+	Authorized Date
		+	Act. Pick Up date
		+	Act. Delivery
		+	* Created by
		+	Pick Up Location
		+	Ship To Location    // map to TO BE CARIFIED!!!
		+	P.O. Issued by
		+	Pick Up Instructions
		+	Commodity Description
		+	Cargo Available
		+	Cargo Due date
		+	* Planner Name
		+	* Approved by (planner)
		+	* Approved by (super planner)
		+	* Pick up country
		+	* Delivery country
		+	* Pick up region
		+	* Delivery region
		+
		+	Shipment TTL GW (lb)
		+	Shipment TTL GW (kg)
		+	Haz
		+	Pick up Trucker Name
		+	Est. Pick Up date
		+	Delivery Trucker Name
		+	Est. Delivery
		+	Created by
		+	Authorization Status
		+	Last Update Date
		+	Last Update by
		+
		+	MOT
		+	Created On
		+	Approved On
		+	Approved For
		+	Rejected On
-	'''Top right panel'''	+	Load attributes:
		+	-------------
		+	Load#
		+	Date of receipt
		+	Total charge
		+	Other charges
-	Carrier View: general information	+	Commodity table attributes (all KPIs):
		+	--------------------------------------
		+
		+	P.O.
		+	SKU
		+	Qty
		+	Piece Price
		+	Total Value
		+	Item Description
		+	# Plt
		+	Pkgs on plt
		+	Loose pkgs
		+	Stack
		+	TTL GW (lb)
		+	TTL GW (kg)
		+	* TTL Chargeable weight (AIR mode only)
		+	* Total CBM (LCL Mode only)
		+	* Total Freight Costs (with OR without DUTY/VAT)
-	Axis X: all carriers (YTD average for current year and YTD average for last year)	+	KPIs
		+	----
		+	* Current KPI
		+	* For “counters” we need “created to delivered”
		+	* For CPU we need “created to delivered” and “approved to due date” and “picked up to delivered” and “average transit time”
		+	* For Lead times we need “average cost per KG” and “average cost per item”
		+
		+	</pre>
		+	'''Changes''': all fields with *
-	Carrier View: Specific information (select from Left panel)	+	==== Misc Field Selector pop up logic ====
-	Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)	+	* 1/ Use diff font or color to show commod items vs shipment items vs load items or postfix items with "Shipment fields", "(commod field)", "load field" to indicate.
		+	* 2/ list sections one after another (CT fields, Commod fields, etc)
-	Axis Y: options:	+	* 3/ Above is one list ('''change'''!) with items in alphabetical order (per section).
-	General information	+	* 4/ Use drag and drop to select ('''change'''!)
-	* Total weight (kg)	+
-	* Average Cost (USD)	+
-	* Shipment Count	+
-	* Average weight (kg) per shipment	+
-	Specific information (particular carrier)	+	* 5/ Default value for “downloads” should always be:
-	* Total weight (kg)	+	** GRP# or load# for TMS
-	* Average Cost (USD)	+	** CT#
-	* Shipment Count	+	** Shipper
-	* Average weight (kg) per shipment	+	** PO
		+	** SKU
		+	** QTY
		+	** current KPI field
		+	* 6/ make GRP#(Load#) and CT# first 2 firlds required and always leftmost
-	plot A: this year	+	* 7/ Add these buttons/fetures:
		+	** Save - saves current list of selected fields. Will be used as a default for THIS user in all KPIs from now on
		+	** Reset - reset above to Systems default
-	plot B: last year	+	==== xls format for drill downs ====
-	plot C: YTD average(this year)	+	===== Header =====
-	plot D: YTD average (last year)	+	* A the top print title in large bold that consists of: <KPI class name> + " KPI"
		+	** example:
		+	Cost per Unit KPI
		+	* below - list KPI name and number of shipments
		+	** example:
		+	KPI name: Cost per Kg
		+	Num of shipments: 12
-	'''Bottom right panel Tab 1'''	+	* below - all additional conditions as defined by filters
		+	** example
		+	Client Company: EA
		+	Mode of Transport: Air
		+	Timeframe: 01-30 Jan 2013
		+	Shipper: Elmar
-	*Type: bar/pie	+	* below - additional info
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+	** example
-	*Axis Y: Total weight (kg)	+	Currency: USD
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 2'''	+	===== Body =====
-	*Type: bar/pie	+	[[File:Commod table 2 cases.JPG | 600px]]
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Average Cost (USD)	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 3'''	+	* 1/ See mock up above.
		+	* 2/ Each line represents commodity line.
		+	* 3/ If value is CT based then post into first commodity line of first CT in GRP (example: CBM)
		+	* 4/ Post "n/a" if no value is defined (example: CBM)
		+	* 5/ In some cases opers do not enter all info
		+	** Case 1 - when all commodity line info is entered by oper
		+	** Case 2 - when Oper enters only totals on one (normally first line).
		+	* 6/ Fill in all cells with values even in case they repeat (common values for all commode lines). For example Shipper value will repeat for all commode lines.
		+	* 7/ the currency / total currency should be downloaded into a ‘currency’ format without the need to manually change the formatting of these cells
-	*Type: bar/pie	+	===== Group and Load Logic =====
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Shipment Count	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 4'''	+	In case of Truck TMS replace GRP# with Load#.
-	*Type: bar/pie
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers
-	*Axis Y: Average weight (kg) per shipment
-	*Apply:
-	:* General information: YTD average
-	:* Specific information: a particular carrier (weekly/monthly)
	-->		-->
-	==== Consignees View ====
-	Same data/layout as in [[#Carriers View]] but for Consignees.	+	=== Drilldowns v3 ===
-	==== Shippers View ====	+	Drilldown is a functionality that gives "GRP/CT/Commodity item level details" for particular segment of visual representation for given KPI.
-	Same data/layout as in [[#Carriers View]] but for Shippers.	+	Defined for:
-	==== Lanes View ====	+	* selected time frame (week/month/quarter/year) as defined on Left Panel
		+	* same data but restricted to specific Shipper/Consignee/Carrier/Lane(Bottom Right Panel)
-	Same data/layout as in [[#Carriers View]] but for Lanes.	+	Produce pop-up where user can select output fields.
-	=== Visibility ===	+	'''What did not change:'''
-	For given KPI that is enabled for given Client user use only CT records that satisfy "default client user visibility" (E0 or E0 Group).	+	* [[#drilldowns header]]
-	=== KPI Admin ===	+	'''Number of things will change:'''
-	==== System wide settings ====	+	* [[#HTML view]]
		+	* [[#new admin options v3]]
		+	* [[#fields list v3]]
		+	* [[#field selector v3]]
		+	* [[#xls format v3]]
		+	* [[#filenaming]]
-	Add Admin > KPI to manage system wide KPI module related settings.	+	==== HTML view ====
-	Currently only one parameter to be managed: [[#KPI Earliest Date]].	+	No need to show html view first
-	===== KPI Earliest Date =====	+	==== drilldowns header ====
-	Defines cut off date for CT records based on Created on date. Records created earlier than this date will not be used in KPIs.	+	* A the top print title in large bold that consists of: <KPI class name> + " KPI"
		+	** example:
		+	Cost per Unit KPI
-	==== User specific settings ====	+	* below - list KPI name and number of shipments
		+	** example:
		+	KPI name: Cost per Kg
		+	Num of shipments: 12
-	Note that currently we have some sort of admin. This is re-design.	+	* below - all additional conditions as defined by filters
		+	** example
		+	Client Company: EA
		+	Mode of Transport: Air
		+	Timeframe: 01-30 Jan 2013
		+	Shipper: Elmar
-	* On internal users with role=Client will be set up by Jag operators to get access to some KPIs.	+	* below - additional info
		+	** example
		+	Currency: USD
-	* We need to have list of all KPIs available in the system be present in this UI.	+	==== filenaming ====
-	* Also we should be able to enable KPI just for some MOTs.	+	KPI Type + Week #, Year + (if appropriate: Company Name (first word in name only))
-	* Need enable/disable drill down switch per user	+	Example:
-	* Finally many KPIs have "KPI parameters" (such as intervals for Gages). They should be set through this Admin as well.	+	Shipment Count wk 13 2013 CEI.xls
-	* As far as layout It should have tree like structure:	+	==== fields list v3 ====
-	* KPI class A	+	Below fields defined as:
-	** KPI 1	+	* <label as it appears on pop-up and xls> // <meaning and/or mapping>
-	** KPI 2	+
-	** ...	+
-	* KPI class B	+	===== Load attributes =====
-	** KPI 1	+
-	** KPI 2	+
-	** ...	+
-	Once some KPIs are enabled these client users should be able to manage enabled KPIs in their users profiles.	+	* Load# //
		+	* Load Kg //
		+	* Load Lb //
		+	* Load cost // Est or Actual Sales Charges (not anymore from XML!)
		+	* Load cost per Lb //
		+	* Load cost per Kg //
-	=== Misc Standards ===	+	* Carrier(s)
-	==== MOTs ====	+	===== Shpt attributes =====
-	Use all MOTs.	+	* Shpt# // post GRP# if CT is grouped and CT# if not
		+	* MOT
		+	* Shpt Kg // Gross weight for GRP in kg
		+	* Shpt Lb //
		+	* Shpt cost // Est or Actual Sales Charges (not anymore from XML!)
		+	* Shpt cost per Lb //
		+	* Shpt cost per Kg //
-	If there is no data for given MOT - do not show it in selector.
-	==== Mappings ====	+	* Origin City // City from [[Sh#Origin Door]]  (from CT with a lowest number in a group)
		+	* Origin State // State from [[Sh#Origin Door]]  (from CT with a lowest number in a group)
		+	* Origin Country // Country from [[Sh#Origin Door]]  (from CT with a lowest number in a group)
		+	* Origin Region // Region from [[Sh#Origin Door]]  (from CT with a lowest number in a group)
-	Shippers:
-	* same for all MOTs
-	* maps to [[Ct#Shipper]]
-	Consignees:	+	* Destination City // City from [[Sh#Destination Door]]  (from CT with a lowest number in a group)
-	* same for all MOTs	+	* Destination State // State from [[Sh#Destination Door]]  (from CT with a lowest number in a group)
-	* maps to [[Ct#Consignee]]	+	* Destination Country // Country from [[Sh#Destination Door]]  (from CT with a lowest number in a group)
		+	* Destination Region // Region from [[Sh#Destination Door]]   (from CT with a lowest number in a group)
-	Carriers:
-	We have two fields for Trucking company:  Export Pick-up Trucker,  Delivery Trucker. So if one shipment has both fields set then I assume both companies get credit as far as shipment count and weight.	+	* Carrier(s)
-	Lanes:	+	===== CT attributes =====
-	* Ocean = origin/destination terminals	+	* CT#
-	* Air = airport of departure / airport of arrival/destination	+	* MOT
		+	* Shipper // Company name, do we need this field?
		+	* Approved by Planner // name, ASN only
		+	* Approved by Super Planner // name, ASN only
		+	* P.O. Issued by // ASN only
		+	* Created On // date
		+	* Authorized // date
		+	* Act. Pick Up // date
		+	* Act. Delivery // date
		+	* Est. Pick Up // date
		+	* Est. Delivery // date
-	== Cost Per Unit KPI Class ==
-	This is aka CPU.	+	* Origin City // City from [[Sh#Origin Door]]
		+	* Origin State // State from [[Sh#Origin Door]]
		+	* Origin Country // Country from [[Sh#Origin Door]]
		+	* Origin Region // Region from [[Sh#Origin Door]]
-	=== CPU General Info and List ===
-	==== CPU group A ====	+	* Destination City // City from [[Sh#Destination Door]]
		+	* Destination State // State from [[Sh#Destination Door]]
		+	* Destination Country // Country from [[Sh#Destination Door]]
		+	* Destination Region // Region from [[Sh#Destination Door]]
-	* Cost per Lb - average cost per pound
-	* Cost per Kg - average cost per kg
-	==== CPU group B ====	+	* Carrier(s)
		+
		+	* Commodity Description // commodity attribute
		+	* Haz // commodity attribute
		+	* CT Lb // commodity attribute
		+	* CT Kg // commodity attribute
		+	* Stack // commodity attribute
-	TBD	+	===== SKU attributes  =====
-	=== CPU Layout ===	+	* SKU
		+	* PO
		+	* Qty
		+	* Description
		+	* Plts
		+	* Pkg on plts
		+	* Loose
		+	* Gross Kg
		+	* Gross  Lb
		+	* Piece price // ASN only
		+	* Haz // ASN only
		+	* Stack // ASN only
-	See [[#Standard Layout]].	+	===== Default selected values in fields selector =====
-	=== Title and Main Menu ===	+	GRP# (or load# for TMS)
		+	CT#
		+	Shipper
		+	PO
		+	SKU
		+	QTY
		+	current KPI
-	'''Title:'''  Cost per unit KPI by Shipper Consignee Carrier Lane	+	===== KPIs in drilldown =====
-	'''Main Menu:'''	+	* list of KPIs enabled for particular users in User Admin
		+	* if there are performance issues with adding many KPIs as output option then limit to say 3 KPIs max (advice user about this in some way)
-	* '''MOT''' - single select	+	==== xls format v3 ====
-	* '''View type''' - controls what is displayed inside panels. Options:	+	* see mock up below
-	** Main View	+
-	** Shippers View	+
-	** Consignees View	+
-	** Carriers View	+
-	** Lanes View	+
-	* '''Ttl Weight/Avg Cost/Count/Avg Weight selector'''	+	* xls will have groups of columns:
-	** This defines axis Y for Top Right Panel	+	** Load or GRP
-	** not used for Main View	+	** CT
-	** options:	+	** SKU
-	*** Total Weight	+
-	*** Average Cost	+
-	*** Shipment Count	+
-	*** Average Weight	+
-	=== KPI Panel ===	+	* if at least one field from particular section above is defined (GRP/Load or CT or SKU) then this section is defined and its id should be autoselected by system (GRP#/Load# or CT# or SKU#)
-	*'''label''': cost per kg (KPI type and unit of measurement)	+	* total values are defined on top line with "-" below (see for example Load Lb column)
-	*'''currency''': USD	+	* if user chooses one field from GRP level and one from item level (skipping CT#) then system should select CT# field automatically
-	*'''number''': actual value of KPI (if "weekly" is selected then average for last full week, if "monthly" - average for last full month)	+	===== note on cost associated with SKU line item =====
-	*'''arrow change indicator''':  comparatively to previous period defined by "timeframe" (up - red, down - green)	+	Cost should be distributed across line items proportionally based on Gross Weight
-	*'''percentage''': change in % comparatively to previous period defined by "timeframe"	+	===== drilldown v3 xls examples =====
-	*'''timeframe''': options: weekly, monthly	+	!!! This mock up is out of date !!!
-	*'''traffic light''': Gage, indicates tolerance level, see [[#Tolerance levels parameters]]	+	<!-- [[File:Xls for kpis.JPG]] -->
-	==== Tolerance levels parameters ====	+	[[File:Dd.png | 600px]]
-	* this is a list of parameters managed by user in user profile	+	X - GRP fields
-	* controls what is considered green vs yellow vs red zone for KPI	+
-	=== Left panel, Top right panel, Bottom right panel ===	+	Y - GRP KPI
-	Content on these panels is controlled by "View Type" selector. See details below.	+	Z - CT fields
-	=== Main View ===	+	W - CT KPI
-	See mockup below:	+	V - SQU fields
-	[[File:CostPerPound.JPG| 800px]]	+	Q - Dates KPI
-	'''Left panel'''	+	===== How to output KPIs =====
-	Left column:	+	* always output "current KPI" (if it is enabled in Admin)
-	* weeks to date sorting from current week to previous weeks (current week, last week, previous week .... )	+
-	* arrow indicator - change from previous week	+
-	:* Mouse over value description	+
-	* gage indicator	+
-	:* Mouse over value description	+
-	Right column:	+	User selections:
-	* KPI value	+
-	:* Mouse over value description	+
-	'''Top right panel'''	+	VOLUMES:
-	Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)	+	* sh count // remove from the list
		+	* ct count // remove
		+	* total spend // calculate and output per shipment
		+	* gross kg // remove
		+	* cwt kg // calculate and output per shipment
		+	* containers/TEU // calculate and output per shipment
		+	* CBM  // calculate and output per shipment
-	Axis Y:	+	CPU:
-	* plot A: KPI this year	+
-	* plot B: KPI last year	+
-	* plot C: YTD Avarage	+
-	'''Bottom right panel Tab 1'''	+	* cost per lb // calculate and output per shipment
		+	* cost per kg // calculate and output per shipment
		+	* cost per item // calculate and output per shipment
		+	* cost per package // calculate and output per shipment
		+	* cost per pallet  // calculate and output per shipment
-	*Type: bar/pie	+	LEAD TIMES:
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Shippers	+	* 6 date KPIs // calculate and output per shipment
-	*Axis Y: KPI	+
-	*Logic: SUM(Total cost for a shipper)/SUM(Total weight for a shipper)	+
-	'''Bottom right panel Tab 2'''	+	LO:
		+	* shipments per lane //  calculate and output per shipment (show number of shpments on lane it belongs to, see example below)
		+
		+	Example (LO as an output parameter)
		+	-----------------------------------
		+	if for given "reporting segment" on which user clicks:
		+	lane X: sh# 1,3,7, 9 (display 4 for every shpmnt on this list)
		+	lane Y: sh# 2,5 (display 2 for every shpmnt on this list)
		+	by the way LO for this "reporting segment" is (4+2)/2=3 shpmnt per lane on average
-	*Type: bar/pie	+	EU:
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Consignees	+	* kg per shmt // remove
-	*Axis Y: KPI	+	* lb per shmt // remove
-	*Logic: SUM(Total cost for a consignee)/SUM(Total weight for a consignee)	+	* plts per shmt // remove
		+	* cwt kg per shmt // remove
-	'''Bottom right panel Tab 3'''	+	===== xls datatype =====
-	*Type: bar/pie	+	* currency should be of type currency
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+	* numerical should be of type numerical with no decimals (digits after dot)
-	*Axis Y: KPI	+
-	*Logic: SUM(Total cost for a carrier)/SUM(Total weight for a carrier)	+
-	'''Bottom right panel Tab 4'''	+	==== field selector v3 ====
		+	UI idea is to use trees: one on the left (select from) and one on the right (select to)
-	*Type: bar/pie	+	===== unit for weight control =====
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Lanes	+
-	*Axis Y: KPI	+
-	*Logic: SUM(Total cost for a lane)/SUM(Total weight for a lane)	+
-	=== Carriers View ===	+	* options: Kg, Lb
		+	* this selector will identify unit of measurement for weight. This will be used for all weight related fields. For example if Kg is set then "CT Kg" field option will not show up.
-	I commented out this section.	+	===== KPI value label =====
-	<!--	+	Now it generically says "KPI value".
-	See mockup below: NOTE: Mock up below is incorrect. Under construction. It will be posted soon	+
-	File:Body carriers view dr3 final 5.png	+	We should name it after actual KPI.
-	'''Left panel'''	+	==== SOW 71 Change requests ====
-	Left column:	+	* re-design pop up layout (details will be provided by Kostya to Misha and functional prototype will be demonstrated)
-	* Carriers	+
-	Right column:	+	== Cost Per Unit KPI Class ==
-	* KPI value (Current YTD)	+
-	'''KPI Panel'''	+	This is aka CPU.
-	Action 1: Carrier view is selected	+	=== CPU General Info and List ===
-	* Time frame: options for weekly, monthly are '''hiding'''	+	a. Cost per Lb
-	* Show number and percentage	+
-	* Show Total Weight/Cost/Count/Avg Weight selector	+
-	* Arrow change indicator and traffic light are '''hiding'''	+
-	Action 2: A particular row of Left panel is selected such as Jet blue	+	b. Cost per Kg
-	* Jetblue is selected (one click)	+	c. Cost per Item
-	:* Time frame: options for weekly, monthly are '''appeared''' and available for user to select	+
-	:** Default: Weekly	+
-	:* Top right Panel: show graph for a particular carrier (Jet blue) for weekly information (w1, w2...)	+
-	:* Show number for that particular carrier KPI (avg last full week/month) value and percentage compared last full week/month to previous week/month	+
-	:* Bottom right Panel: show chart for a particular carrier (Jet blue), apply to all tabs (Total Weight, Average Cost, Sgupment Count, Average Weight)	+
		+	d. Cost per Pallet
-	'''Top right panel'''	+	e. Cost per Carton
-	Carrier View: general information	+	f. Cost per chargeable Weight (air)
-	Axis X: all carriers (YTD average for current year and YTD average for last year)	+	g. Cost per container (FCL)
		+	h. Cost per CBM (LCL)
-	Carrier View: Specific information (select from Left panel)	+	=== CPU group A ===
-	Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)	+	==== Cost per Lb ====
		+	This is average cost per pound.
-	Axis Y: options:	+	Formula: [[#Cost]] / [[#Gross Weight]].
-	General information	+	==== Cost per Kg ====
-	* Total weight (kg)	+
-	* Average Cost (USD)	+
-	* Shipment Count	+
-	* Average weight (kg) per shipment	+
-	Specific information (particular carrier)	+	This is same as  [[#Cost per Lb]] but using different unit of measurement.
-	* Total weight (kg)	+
-	* Average Cost (USD)	+
-	* Shipment Count	+
-	* Average weight (kg) per shipment	+
		+	=== CPU group B ===
-	plot A: this year	+	==== Cost per chargeable weight ====
-	plot B: last year	+	* This is for MOT Air only
		+	* Formula: [[#Cost]] / (sum of all chargeable weights across all CTs participating)
-	plot C: YTD average(this year)	+	==== Cost per CBM ====
-	plot D: YTD average (last year)	+	* Formula: [[#Cost]] / (sum of all CBMs across all CTs participating)
		+	* MOT = LCL only
		+	=== CPU group C ===
-	'''Bottom right panel Tab 1'''	+	==== Cost per TEU ====
-	*Type: bar/pie	+	TBD
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Total weight (kg)	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 2'''	+	* MOT = FCL only
-	*Type: bar/pie	+	==== Cost per Container ====
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Average Cost (USD)	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 3'''	+	'''!!! This is on hold''
-	*Type: bar/pie	+	'''Spec to be rewritten'''
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Shipment Count	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	'''Bottom right panel Tab 4'''	+	* OLD Formula: [[#Cost]] / (sum of all container counts across all CTs participating)
		+	** not important what cont type
		+	* MOT= FCL only
-	*Type: bar/pie	+	==== Cost per item ====
-	*Axis X: top/bottom 25, 20, 15, 10, 5 for Carriers	+
-	*Axis Y: Average weight (kg) per shipment	+
-	*Apply:	+
-	:* General information: YTD average	+
-	:* Specific information: a particular carrier (weekly/monthly)	+
-	-->	+	Formula: [[#Cost]] / (sum of Qty values from CT commodity table across all CTs participating)
-	=== Consignees View ===	+	==== Cost per Pallet ====
-	Same data/layout as in [[#Carriers View]] but for Consignees.	+	Formula: [[#Cost]] / (sum of all pallet counts across all CTs participating)
-	=== Shippers View ===	+	==== Cost per Carton ====
-	Same data/layout as in [[#Carriers View]] but for Shippers.	+	Formula: [[#Cost]] / (sum of all carton counts across all CTs participating)
-	=== Lanes View ===	+	=== CPU Layout ===
-	Same data/layout as in [[#Carriers View]] but for Lanes.	+	See [[#Standard Layout]].
		+
		+	Also see [[#KPI Framework]].
	=== DR3 Phase 1A: EA DOM Portal only ===		=== DR3 Phase 1A: EA DOM Portal only ===
		+	Commented out this spec as outdated.
		+
		+	<!--
	In this case:		In this case:
Line 847:		Line 1,030:
	* Tolerance levels parameters:		* Tolerance levels parameters:
	** in case connected to TMS/ASN is On the I suggest to define [[#Tolerance levels parameters]] as Clint Company specific and define these params there		** in case connected to TMS/ASN is On the I suggest to define [[#Tolerance levels parameters]] as Clint Company specific and define these params there
-
-	==== DR3 ph 1A Mock up Main View ====
-
-	[[File:Main window dr33a simplify.png| 800px]]
	==== TMS flag ====		==== TMS flag ====
Line 879:		Line 1,058:
	Some related code is in the system. Please re-use! See [[Cost_per_pound_KPI_(TMS_based_Arden_)_(solution) ]].		Some related code is in the system. Please re-use! See [[Cost_per_pound_KPI_(TMS_based_Arden_)_(solution) ]].
-	=== DR3 Phase 1A1: EA DOM Portal only Main View only ===	+	-->
-		+
-	This is a sub-phase of phase 1A.	+
-		+
-	It characterized by:	+
-	* Main View only	+
	== Dates KPI Class ==		== Dates KPI Class ==
Line 895:		Line 1,069:
	=== List of Dates KPIs ===		=== List of Dates KPIs ===
-	==== Dates KPI group A ====	+	See below links to group A, B, C, .... .
-	* created to delivered	+	=== Dates KPI group A ===
-	* approved to delivered	+
-	* created to approved	+	* created to delivered *
-	* approved to pick up	+	* approved to delivered *
		+	* created to approved *
		+	* approved to pick up *
	* pick up to departure (air and ocean only)		* pick up to departure (air and ocean only)
-	* departure to arrival  (air and ocean only)	+	* departure to arrival  (air and ocean only) '''- moved to group B !!!'''
	* arrival to delivery (air and ocean only)		* arrival to delivery (air and ocean only)
-	* pick up to delivery (trucking only)	+	* pick up to delivery
-	NOTE: Some dates such as Approved currently are used only forASN users therefore some KPIs are available only for them.	+	=== Dates KPI group B ===
-	==== Dates KPI group B ====	+	see SOW 44
-	TBD	+	=== Dates KPI group C ===
		+
		+	==== Created on Date to Cargo Due Date ====
		+
		+	* Meaning: Number of days since the date of creation of the shipment to the date of cargo due date
		+	* Show label as: Created to Cargo Due
		+	* Only for ASN users and TMS Client user roles
		+	* formerly SOW 29
		+
		+
		+
		+	==== Basic to Super approved ====
		+
		+	* Add additional lead time date range to measure time for shipment to get approve by double approval process.
		+	* Meaning: This is measuring between a date that basic planner approves shipment and a date that super planner approves shipment
		+	* Show label as: Basic to Super approved
		+	* If there is some shipment that only got approved by super planner, DO NOT consider this shipment. Only consider shipments that have approval activity from both basic planner and super planner
		+	* Only for ASN users and TMS Client user roles
		+	* formerly SOW 31
		+
		+	==== Average Transit Times KPI ====
		+
		+	* Transit in days:
		+	** Truck: door to door in days
		+	** Air: airport to airport
		+	** Ocean: port to port
		+	* Label: Transit Times
		+
		+	* formerly 3966
		+
		+	==== Time To Update ====
		+
		+	* Meaning: number of days between date that is set and date on which this date is set by operator. For these date field:
		+	** Actual Pick Up
		+	** Actual Delivery
		+	* If date was updated in advance (before that date) then show KPI value as negative, if on same day then show as zero, if updated after then show as positive
		+	* Show label as:
		+	** Time to update <date field>
		+	* formerly SOW 30, mant 3752
		+
		+	=== Dates KPI group D ===
		+
		+	==== File Closing Metrics ====
		+
		+	* '''type''': Internal KPI
		+	* '''name''': Arrived to Closed
		+	* '''meaning''': number of days from actual arrival date (delivery for Truck modes) to date of closing (of that CT)
		+	* Add filters to main menu
		+	** By office (associate CT with office through operator that closed that CT last time)
	===Dates KPI Title ===		===Dates KPI Title ===
Line 941:		Line 1,165:
	See [[#KPI Framework]]		See [[#KPI Framework]]
-	=== Dates KPI Mock Up ===	+	=== Precision for Dates class ===
-	[[File:Dates KPI 3.png|800px]]	+	Maximum one digit after dot.
	== Counters KPI Class ==		== Counters KPI Class ==
		+
		+	Another name for this KPI class is "Volumes".
		+
		+	IMPORTANT: see also [[#Association Inclusion and Group logic]]
	=== Counters General Info ===		=== Counters General Info ===
Line 966:		Line 1,194:
	* Chargeable Weight Count		* Chargeable Weight Count
-	Another name for this KPI class is "Volumes".	+
	=== Counters group A ===		=== Counters group A ===
Line 977:		Line 1,205:
	For all other cases/MOTs:		For all other cases/MOTs:
-	* it is ''a sum of all GRPs plus individual CTs not included in GRPs'' that were created during given timeframe for given MOT. Use "Created On Date".	+	* it is ''all GRPs plus individual CTs not included in GRPs'' that were created during given timeframe for given MOT. Use "Created On Date".
-	//?? Do we have created on for groups in DB. If I suggest to use Created On Date from CT with lowest CT number in the group.//	+	See also [[#Shipment defined]].
		+
		+	Note: Created On Date for a group is Created On from CT with lowest CT number in the group.
	==== CT Count ====		==== CT Count ====
Line 996:		Line 1,226:
	* consider only shipment that has ''Actual Delivery date'' is set (not empty).		* consider only shipment that has ''Actual Delivery date'' is set (not empty).
-	Additional parameter (managed through user admin/user profile):	+	see also '''Exclude Duty and VAT''' section
-	*  exclude "Duty/Taxes/VAT" from this KPI	+
-		+
-	=== Counters group B ===	+
-		+
-	==== Gross KG shipped ====	+
-		+
-	* Grand total of Gross KGs for all CTs for specific time frame.	+
-	** Gross KG data mapping: CT>TableA.GrandTotal.Ttl GW [[Commodity#Grand_Total:_Gross_Kg]]	+
-		+
-	* Condition: consider only shipment that have these dates set:	+
-	** AIR -> ''CT.ExpTab.14c.ATA - Actual Time of Arrival'' is not empty  [[CT#Airport_Of_Destination_Actual_Date]]	+
-	** Truck Dom -> ''CT.Gen.y.Actual Delivery'' is not empty [[CT#Actual_Delivery_Date]]	+
-	** Truck Air -> ''CT.Gen.y.Actual Delivery'' is not empty [[CT#Actual_Delivery_Date]]	+
-	** Truck Ocean -> ''CT.Cont.ContainerTable.Actual Delivery'' is not empty (at least one container has actual delivery date for the case of many containers in one CTs) [[Container#Actual_Delivery_Date]]	+
-	** FCL & Vendor Console -> ''CT.ExpTab.14c. ATA'' is not empty [[CT#Port_Of_Discharge_Actual_Date]]	+
-	** LCL -> ''CT.ExpTab.14c.ATA'' is not empty  [[CT#Port_Of_Discharge_Actual_Date]]	+
-		+
-	==== Cartons count ====	+
-		+
-	* This counts number of cartons; consider sum(AMS) cartons for all CTs for each specific time frame.	+
-	** Mapping: ContTab.Table A. Grand Total AMS	+
-		+
-	:** All modes:	+
-		+
-	:*** TRUCK-DOM (TMS)	+
-	Load A: CT1: 4 AMS, CT2: 3 AMS, CT3: 3 AMS  Total of load A = 10 AMS	+
-	Load B: CT4: 2 AMS, CT5: 4 AMS, CT6: 4 AMS  Total of load B = 10 AMS	+
-	SUM(AMS) = 20 AMS = 20 cartons	+
-		+
-	:*** TRUCK-Air/Truck Ocean (NON-TMS)	+
-	Master A: CT1: 5 AMS, CT2: 5 AMS, CT3: 5 AMS  Total of load A = 15 AMS	+
-	Master B: CT4: 5 AMS, CT5: 6 AMS, CT6: 4 AMS  Total of load B = 15 AMS	+
-	SUM(AMS) = 30 AMS = 30 cartons	+
-		+
-	:*** TOTAL Truck	+
-	SUM(AMS of TMS) and (AMS of Non-TMS) = 20+30 = 50 AMS (cartons)	+
-		+
-	:*** Other modes (Air, FCL&Vendor console, and LCL) calculates as NON-TMS case	+
-		+
-	==== Pallets Count ====	+
-		+
-	:** Count number of actual # of pallets for a specific time frame. SUM(Ttl #Plts).	+
-	:** All modes	+
-	:** Mapping:ContTab.TableA.Grandtotal.Ttl # of plts  [[Commodity#Grand_Total:_Plts]]	+
-		+
-	==== Containers Count ====	+
-		+
-	:** This counter is for FCL and Vendor consoles specific. This is available only when ''FCL & Vendor console'' is selected as MOT	+
-	:** Mapping: Master> Master Details > # container for FCL and Vendor Console. SUM(#containers)	+
-		+
-	==== CBMs count ====	+
-		+
-	:** This counter is for LCL specific. This is available only when ''LCL only'' is selected as MOT	+
-	:** Mapping: CT> ContTab.TableB.Total(in cbm) [[Commodity#Grand_Total:_cbm]] for LCL only. SUM(CBM)	+
-		+
-	==== Chargeable Weights count ====	+
-		+
-	:** This counter is for Air specific. This is available only when ''Air'' is selected as MOT	+
-	:** Mapping: CT> ExpTab.17b: Chargeable weight [[CT#Chargeable_Weight]] SUM(Chargeable Weight)	+
	=== Main Menu ===		=== Main Menu ===
Line 1,132:		Line 1,303:
	== LO and EU KPI classes ==		== LO and EU KPI classes ==
-	The basic premise of transportation is that the more you ship the smaller your unit cost (if you buy 2 pens from Amazon.com the cost of shipping will usually be the same for 2 pens as it is for 1 pen, thus by shipping 2 pens together your cost per pen decreased by 50%).	+	=== LO class ===
-	If we would want to add another layer of complexity we would start “modeling” the “current data” to show something like this:
-
-	You ship 10 shipments per month from company A to company B with 1000 kg each at the moment. Your average cost per xx (item, kg etc) is $ 5.-
-
-	IF you started shipping 2 shipments per month from Company A to Company B with 5000 kg each, your average cost per xx (item, kg etc) would be $ 2.50**
-
-	To get to the **$2.50 number some slightly more complicated referencing/estimating system would have to be implemented, one that would measure different quantity ranges (1, 100, 500, 1000, 2000, 3000, 5000, 10000 KG – for example) and measure the proportional reduction in unit cost (cost per KG for example) for each weight break, then apply this proportional reduction to the “model based” quantity per shipment.
-
-
-	1) How many shipments for every "shipper/consignee" pairing per week, per quarter, per month, etc
-
-	2) How much quantity, weight in KG/LB, pallets, CBM, etc per shipment per container per a specific time frame
-
-	* To be able to measure:
-	:* KG per/ LB per/ Pallets per/ CBM per/
-	:** Per shipment (all modes)
-	:*** Consider shipments as load for TMS and Master for Non-TMS
-	:* Pallets per/ CBM per/
-	:** Per container (FCL)
-	:* Per week/quarter/month
-
-
-
-	=== 1). Lane Optimization View General Info ===
-	* This DR KPI is on an additional tab
	* name: "Lane Optimization"		* name: "Lane Optimization"
-	* Admin: user profile it should be defined/managed separately	+	* has one KPI [[#Avg Shipments per Lane]] - see below
-	* It is available when it is set from admin to client and allow user accessibility to my profile (Client application) for tolerance Gage setting.	+
-	* This shows '''how many shipments''' for every lane per week, quarter, and month	+
-	:* Consider shipments move:	+
-	:** TMS - consider load as shipment. Number of loads = Number of shipments	+
-	:** NON-TMS - consider master as shipment. Number of masters = Number of shipments	+
-	:** Start with Truck-DOM TMS only for now	+
-	:** Future - other modes (TBD)	+
-	* Main Menu:	+
-	:* Client company	+
-	:* MOT - Single select	+
-	:** Trucking - Truck-Dom (TMS)	+
-	:** Trucking - (Non-TMS) - (TBD)	+
-	:** Other modes (TBD)	+
-	:* View Type	+
-	:** Main View	+
-	:** Other views (TBD)	+
		+	==== Avg Shipments per Lane ====
-	==== Layout ====	+	* '''label''': "Weekly/ Monthly Avg Shipments per Lane"
-	Use [[#Standard Layout]] similar to Cost per unit KPI	+
-	except bottom right display tabs	+
		+	* '''definition''': This shows how many shipments there are on average per lane per week or month
-	==== Lane optimization View Title ====	+	Weekly: Weekly Average
-	Lane optimization KPI for best median and worst	+	Monthly: Month Average
		+	Quarter: Total Quarter Count - divided by 3 month (average per month)
		+	Annual: Total annual count - divided by 12 month (average per month)
		+	* hint label:
-	==== KPI Panel ====	+	If "period" is 3 weeks or less, show the "average shipment for this week"
-	* '''Label''': Avg shipments per lane	+	If "period" is 1 month or more: Show  'average shpt count per month for this period'
-	* '''Number''': Average value of shipments for Lane optimization of the last full week/quarter/month	+
-	* '''Arrow change indicator''': Same indicator as other KPIs	+
-	* '''Percentage''': comparatively to previous period time frame same as other KPI	+
-	* '''Timeframe''': Options: Weekly, Quarterly, Monthly	+
-	* '''Traffic light''': Gage. Indicates tolerance level same as other KPIs	+
-	* '''Weekly/Quarter/Monthly''' switch: Control type of axis X on Top Right panel	+
-		+
-	==== Logic ====	+
-	* How to count shipments:	+
-		+
-	TMS --> 1 Load = 1 Shipment	+
-	NON-TMS --> 1 Master = 1 Shipment	+
-		+
-	:* MOTs:	+
-	:** Air : count # of masters = # of shipments	+
-	:** Truck (Truck Dom, Truck Ocean and Truck Air) : count # of loads (Truck Dom) + count # of Masters (Truck Air and Truck Ocean)	+
-	:** FCL and Vendor console : count # of Masters = # of shipments	+
-	:** LCL : count # of masters = # of shipments	+
-		+
-		+
-		+
-	==== Left Panel ====	+
-		+
-	Similar to Cost per Unit KPI but this is reported on average shipments per lane	+
-		+
-	Include Drill down concept and ability to download to excel	+
-		+
-	* When a specific week/quarter/month is selected (one click) THEN applies the chart on the bottom right	+
-	* When there is a double click then download to excel option is available for the shipments for the specific week/quarter/month	+
-		+
-	==== Top Right Panel ====	+
-		+
-	Similar to Cost per Unit KPI but this is reported on average shipments per lane	+
-		+
-	==== Bottom Right Panel ====	+
-		+
-	Similar to Cost per Unit KPI but this is reported on selected type of counter KPIs	+
-		+
-	Include drill down concept with HTML report and ability to download to excel	+
-		+
-	* This chart is to show average shipments for lanes in the top 25 lanes, median 25 lanes, and the bottom worst 25 lanes for Current YTD value.	+
-	* Once a specific lane for specific week/quarter/year is selected on bottom right (on chart per lane), option to download to excel on specific lane on specific time frame.	+
-		+
-		+
-		+
-	==== Apply to panels ====	+
-		+
-	Apply to bottom right panel when a specific row of left panel is selected, show lanes for specific week/quarter/month	+
-		+
-		+
-	==== Main View ====	+
-	See mock up below	+
-		+
-	[[File:LaneOptimization.JPG]]	+
		+	=== EU class ===
-	=== 2). Equipment Utilization View General Info ===
-	* This DR KPI is on an additional tab next to Lane optimization tab
	* name: "Equipment Utilization"		* name: "Equipment Utilization"
-	* Admin: user profile it should be defined/managed separately
-	* It is available when it is set from admin to client and allow user accessibility to my profile (Client application) for tolerance Gage setting.
	* This is a KPI measuring quantity, weight in Kg and Lb, pallets, CBM, etc per shipment/ per container for a specific time frame		* This is a KPI measuring quantity, weight in Kg and Lb, pallets, CBM, etc per shipment/ per container for a specific time frame
-	* List of Equipment Utilization KPI for a specific time frame (week/quarter/month)
-	:* Universal list for all modes
-	:** Avg Kg per shipment
-	:** Avg Lb per shipment
-	:** Avg Pallets per shipment
-	:** Avg CBM per shipment
-	:* Specific list for specific mode(s)
-	:** Avg Pallets per container (FCL)
-	:** Avg CBM per container (FCL)
-	* Note: consider load for TMS and master for NON-TMS for shipments.
-	* Main Menu:
-	:* Client company
-	:* MOT
-	:* View Type
-	:** List of equipment utilization KPIs
		+	* group A:
		+	** [[#KG per shipment]]
		+	** [[#LB per shipment]]
		+	** [[#Pallets per shipment]]
		+	** [[#CBM per shipment]]
		+	** [[#Chargeable Weight per shipment]]  - added
-	==== Layout ====	+	* group B:
-	Use [[#Standard Layout]] similar to Cost per unit KPI	+	** [[#Pallets per container]] - on hold as well
		+	** [[#CBM per container]]  - on hold
		+	* Layout: [[#Standard Layout]]
-	==== Equipment Utilization View Title ====	+	==== KG per shipment ====
-	Equipment Utilization KPI by Shipper Consignee Carrier Lane	+
		+	average KG per shipment(load for Truck Dom TMS, [[Shipment]] for Non-TMS Trucking) for specific time frame. Sum up KGs for all shipments in a specific time frame and divide by number of shipments
-	==== KPI Panel ====	+	==== LB per shipment ====
-	* '''Label''': ie. Average(Kgs)per shipment(KPI type of equipment utilization - See List of equipment utilization KPI above)	+
-	* '''Number''': Average value of KPI of type of measurement for equipment utilization of the last full week/quarter/month	+
-	* '''Arrow change indicator''': Same indicator as other KPIs	+
-	* '''Percentage''': comparatively to previous period time frame same as other KPI	+
-	* '''Timeframe''': Options: Weekly, Quarterly, Monthly	+
-	* '''Traffic light''': Gage. Indicates tolerance level same as other KPIs	+
-	* '''Weekly/Quarter/Monthly''' switch: Control type of axis X on Top Right panel	+
-		+
-	==== Logic ====	+
-		+
-	===== How to calculate shipment =====	+
-		+
-	TMS --> 1 Load = 1 Shipment	+
-	NON-TMS --> 1 Master = 1 Shipment	+
-		+
-	===== KG per shipment =====	+
-		+
-	average KG per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame. Sum up KGs for all shipments in a specific time frame and divide by number of shipments	+
-		+
-	===== LB per shipment =====	+
	average LB per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame. Sum up Lbs for all shipments in a specific time frame and divide by number of shipments		average LB per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame. Sum up Lbs for all shipments in a specific time frame and divide by number of shipments
-	===== Pallets per shipment =====	+	==== Pallets per shipment ====
	average Pallets per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame.		average Pallets per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame.
Line 1,307:		Line 1,356:
	Sum up number of pallets for all shipments in a specific time frame and divide by number of shipments		Sum up number of pallets for all shipments in a specific time frame and divide by number of shipments
-	===== CBM per shipment =====	+	==== CBM per shipment ====
-	average CBM per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame. Sum up CBM value for all shipments in a specific time frame and divide by number of shipments	+	average CBM per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame.
-	===== Pallets per container =====	+	Sum up CBM value for all shipments in a specific time frame and divide by number of shipments
-	average KG per container for specific time frame. Sum up number of pallets for all number of containers in a specific time frame and divide by number of containers	+	==== Chargeable Weight per shipment ====
-	===== CBM per container =====	+	* Average for Chargeable Weight across participating CTs
		+	* MOT Air only
-	average CBM per container for specific time frame. Sum up CBM value for all number of containers in a specific time frame and divide by number of containers	+	==== Pallets per container ====
-	==== Left Panel ====	+	average plts per container for specific time frame. Sum up number of pallets for all number of containers in a specific time frame and divide by number of containers
-	Similar to Cost per Unit KPI but this is reported on selected type of measurement average value of equipment utilization KPIs	+	==== CBM per container ====
-	Include Drill down concept and ability to download to excel	+	'''!!! Per Marc Keep ON HOLD for International ASN Clients'''
-	* Types of measurement for equipment utilization are universal for all MOTs and some are specific for some specific MOT.	+	average CBM per container for specific time frame. Sum up CBM value for all number of containers in a specific time frame and divide by number of containers
-	:* Universal KPI: this is available for quantity for ALL MOTs	+
-	:** Average Kg per shipment for a specific week/quarter/month	+
-	:** Average Lb per shipment for a specific week/quarter/month	+
-	:** Average Pallets per shipment for a specific week/quarter/month	+
-	:** Average CBM per shipment	+
-	:* Specific MOT: this is available for only specific MOT(s) only	+
-	:** Pallets per container (FCL) for a specific week/quarter/month	+
-	:** CBM per container (FCL) for a specific week/quarter/month	+
-	* When a specific row of week/quarter/month is selected (one click) THEN applies the chart on the bottom right	+
-	* When there is a double click then download to excel option is available for the shipments for the specific week/quarter/month	+
		+	==== Misc ====
		+	N/A
-		+	<!--[[File:EquipmentUtilization.JPG]]-->
-	==== Top Right Panel ====	+
-		+
-	Similar to Cost per Unit KPI but this is reported on selected type of measurement average value of equipment utilization KPIs such as kg, lb, pallets, cbm etc...per shipments or per container for years comparison, current year, previous year, YTD current year average, YTD previous year average	+
-		+
-		+
-		+
-	==== Bottom Right Panel ====	+
-		+
-	Similar to Cost per Unit KPI but this is reported on selected type of measurement average value of equipment utilization KPIs	+
-		+
-	Include drill down concept with HTML report and ability to download to excel	+
-		+
-	* This chart is to show average value for current YTD for specific mode on shippers, consignees, carriers, and lanes tab	+
-	* Once a specific shipper/consignee/carrier/lane for a specific week/quarter/year is selected on bottom right, option to download to excel on specific information on specific time frame.	+
-		+
-		+
-		+
-	==== Apply to panels ====	+
-		+
-	Apply to all panels (top right, bottom right, left panel) with KPI and MOT values combination.	+
-		+
-		+
-		+
-	==== Apply to panels ====	+
-		+
-	Apply to bottom right panel when a specific row of left panel is selected, show lanes for specific week/quarter/month	+
-		+
-		+
-		+
-	==== Main View ====	+
-	See mock up below	+
-		+
-	[[File:EquipmentUtilization.JPG]]	+
	== SOWs Part A ==		== SOWs Part A ==
Line 2,007:		Line 2,016:
	Mantis: [http://ct.jaguarfreight.com/mantis/view.php?id=3741 3741]		Mantis: [http://ct.jaguarfreight.com/mantis/view.php?id=3741 3741]
-	Adding 2 pairing of specific date KPIs for Truck-DOM (TMS)	+	outdated mantis, moved to another SOW
-		+
-	* Created on Date to Cargo Due Date	+
-	:* Number of days since the date of creation the shipment to the date of cargo due date	+
-	:* Show label as: Created to Cargo Due	+
-	:* Logic: Created to Cargo Due = Avg(Created on Date - Cargo Due Date) in specific time frame	+
-		+
-	* Approved on Date to Cargo Due Date	+
-	:* Number of days since the date of the shipment gets approval to the date of cargo due date	+
-	:* Show label as: Approved to Cargo Due	+
-	:* Logic: Approved to Cargo Due = Avg(Created on Date - Cargo Due Date) in specific time frame	+
-		+
-	KPI value, functionality, time frame, drill down, Left panel, Top and Bottom right panels are similar to other date pairing KPIs.	+
-		+
-		+
	=== SOW 30 (DR4 Lead Time KPI) who and when - Updates and Time to update Actual Departure date and Actual delivery date KPIs ===		=== SOW 30 (DR4 Lead Time KPI) who and when - Updates and Time to update Actual Departure date and Actual delivery date KPIs ===
Line 2,298:		Line 2,293:
	Review existing KPIs in this group and make them work as re-defined in [[#KPI Framework]] - main changes are visibility and use with all MOTs.		Review existing KPIs in this group and make them work as re-defined in [[#KPI Framework]] - main changes are visibility and use with all MOTs.
-	Spec: [[#CPU KPI group A]]	+	Spec: [[#CPU group A]]
	Kostya: SQL and Admin		Kostya: SQL and Admin
Line 2,308:		Line 2,303:
	=== SOW 41 Pull weight when calculating KPIs always from CT2 DB ===		=== SOW 41 Pull weight when calculating KPIs always from CT2 DB ===
-	Mantis:	+	Mantis: 0003987
	Spec:		Spec:
Line 2,314:		Line 2,309:
	Currently we pull weight in some cases from TMS we need to change that to CT2 DB as this info is more correct (operators will update some records that have incorrect weight coming from TMS).		Currently we pull weight in some cases from TMS we need to change that to CT2 DB as this info is more correct (operators will update some records that have incorrect weight coming from TMS).
-	=== SOW 32 ALL KPIs: Add PO Issue by tab on the bottom right panel for all KPIs ===	+	=== SOW 32 Add PO Issue By filter to main menu ===
-	Mantis: [http://ct.jaguarfreight.com/mantis/view.php?id=3767 3767]	+	Mantis: 3767
-	* breakdown per PO Issued By	+	* Add PO Issue By filter to main menu
-	* Currently only for Truck Dom (TMS)	+	* need config option on User Admin
-	* Future this will expand to other modes once we have enable KPI module for ASN 20 users.	+
-	* probably an additional graph at the bottom next to lanes and carriers.	+
	=== SOW 43 Counters KPI:group B ===		=== SOW 43 Counters KPI:group B ===
-	Mantis:	+	mant: 3988
-	Spec: [[#Counters group B]]	+	spec:
-	Kostya: SQL	+	List:
-	Misha: Drilldowns and Admin	+	* Gross KG // all
		+	* Containers count // FCL
		+	* CBM count // LCL
		+	* Chargeable Weight count // Air
-	Sasha: Front End	+	==== Gross KG ====
		+
		+	* Grand total of Gross KGs for all shipments for specific time frame.
		+	** Gross KG data mapping: CT>TableA.GrandTotal.Ttl GW [[Commodity#Grand_Total:_Gross_Kg]]
		+
		+	==== Package count ====
		+
		+	'''(per Marc this is on hold!)'''
		+
		+	* This counts number of cartons; consider sum(AMS) cartons for all CTs for each specific time frame.
		+
		+	* Mapping: ContTab.Table A. Grand Total AMS
		+
		+	AMS for shipment: (Loose pkgs + Ttl pkgs on plts)
		+
		+	==== Pallets Count ====
		+
		+	'''(per Marc this is on hold!)'''
		+
		+	* Count number of actual # of pallets for a specific time frame. SUM(Ttl #Plts).
		+
		+	* Mapping:ContTab.TableA.Grandtotal.Ttl # of plts  [[Commodity#Grand_Total:_Plts]]
		+
		+	==== Containers Count ====
		+
		+	* total [[TEU]] value for shipment
		+	** include only assigned containers
		+	* label: TEU
		+	* MOTs: FCL only
		+	* in drilldown:
		+	** show column with total TEU value
		+	** show additional columns - one for each type / size combinations with respective counts.
		+	** see mock up below
		+
		+	[[File:TEU.png | 400px]]
		+
		+	==== CBMs count ====
		+
		+	* MOT: LCL only
		+
		+	* Mapping: CT> ContTab.TableB.Total(in cbm) [[Commodity#Grand_Total:_cbm]] for LCL only. SUM(CBM)
		+
		+	==== Chargeable Weights count ====
		+
		+	* This counter is for Air specific. This is available only when ''Air'' is selected as MOT
		+
		+	* Mapping: CT> ExpTab.17b: Chargeable weight [[CT#Chargeable_Weight]] SUM(Chargeable Weight)
		+
		+	==== Drilldowns and XLS for Counters KPI group B ====
		+
		+	* Apply this: [[#Drilldowns v3]]
		+
		+	How to display KPI value:
		+
		+	* Out of counters above the following KPIs are "non-standard":
		+	** CBM count, Containers count, CWT
		+	*** only per CT value is available in DB, not per commodity line so display total on first commodity line, the rest are zeros
		+
		+	* for all others display per commodity item
		+	** ! make sure total per column is equal total on html for segment that user clicked on
	=== SOW 44 Dates KPI:group B ===		=== SOW 44 Dates KPI:group B ===
Line 2,339:		Line 2,394:
	Mantis:		Mantis:
-	Spec: [[#Dates KPI group B]]	+	Spec:
-	Kostya: SQL	+	==== On time performance ====
-	Misha: Drilldowns and Admin	+	1/ '''Definition''': Actual Delivery date - Cargo Due date; Difference in days
-	Sasha: Front End	+	2/ '''label''': On Time Performance
		+
		+	3/ '''MOT''': all
		+
		+	4/ those KPI should be visible for current Client Company in filters if this Client Company is used in ASN Portal
		+
		+	===== Negatives on Left Panel =====
		+
		+	Marc suggested to put "0" in the middle of scale in this case.
		+
		+	Plot in a standard way as in Cartesian system of coordinates.
		+
		+	==== Departure to Arrival  ====
		+
		+	1/ '''Definition''': Difference in days between Actual Departure and Actual Arrival Date
		+
		+	2/ '''label''':  Departure to Arrival
		+
		+	3/ '''MOT''': air and ocean only
		+
		+	==== Approved on Date to Cargo Due Date ====
		+
		+	* Meaning: Number of days since the date of the shipment gets approval to the date of cargo due date
		+	* Show label as: Approved to Cargo Due
		+	* those KPI should be visible for current Client Company in filters if this Client Company is used in ASN Portal
		+	* formerly SOW 29
		+
		+	==== SOW 44 Changes ====
		+
		+	* Change the LABEL to "On Time Performance" instead of OTIP
		+	* formula is clarified - see spec above (what dat is subtract from what indicating when output negative vs positive values)
	=== SOW 45 CPU KPI:group B ===		=== SOW 45 CPU KPI:group B ===
Line 2,351:		Line 2,436:
	Mantis:		Mantis:
-	Spec: [[#CPU KPI group B]]	+	Spec: [[#CPU group B]]
	Kostya: SQL and Admin		Kostya: SQL and Admin
Line 2,371:		Line 2,456:
	Sasha: Front End		Sasha: Front End
-	=== SOW 18 DR6 Lane Optimization Equipment Utilization ===	+	=== SOW 18 LO and EU classes ===
	Mantis:		Mantis:
Line 2,383:		Line 2,468:
	Sasha: Front End		Sasha: Front End
-	== Misc ==	+	=== SOW 46 Dates KPI group C ===
-	* Vics reports: https://docs.google.com/spreadsheet/ccc?key=0AicSe1YRkhifdGdYMndSSkU4ZXpPZ1BDSzFwRXl5bkE#gid=3	+	combined with SOW 44
		+
		+	=== SOW 47 CPU group C ===
		+
		+	combined with SOW 45
		+
		+	=== SOW 48 File Closing Metrics ===
		+
		+	Mantis: 4043
		+
		+	Add 2 KPIS:
		+
		+	* [[#File Closing Metrics]]
		+	* [[#Time_To_Update]]
		+
		+	=== SOW 49 Notifications for Traffic light ===
		+
		+	mant: 4012
		+
		+	spec: see new section that has been added - [[#Notifications for Traffic light]]
		+
		+	=== SOW 50 Enable KPIs for internal users ===
		+
		+	mantis: [http://mantis.jaguarfreight.com/mantis/view.php?id=4009 4009]
		+
		+	spec: below
		+
		+	* '''KPIs for Internal users''' are almost the same as in Client UI with some differences''':'''
		+	** The Internal KPIs located in separate section of Internal App ''(click "Flashlight" link on the top of CT2 Homepage)''
		+	** "Total Spend" for the Internal KPIs is based on Jaguar Purchases (use PI amounts) and not filtered by "Bill To" or "Charge Codes"
		+	* '''Settings:'''
		+	** Internal KPIs are available for all Internal user roles ''(basic ops, super ops, basic accounting, super accounting, management & super users)''
		+	** User can select needed KPIs for viewing on "Dashboard Configuration" tab in User Profile section of CT2 Admin
		+	** New KPIs (only for internal):
		+	*** Total Sales ''(Client Costs)''. It is based on information from Sales Invoices (or Est tab if CT has no invoices).
		+	*** Margin. Sales ''(Client Costs)'' minus Purchases ''(Jaguar Costs)''.
		+	* '''Look and Feel:'''
		+	** Transport Mode filter remains on top level (as on Client's Flashlight)
		+	** "Client Company (E0)" moved to Additional Filters and provides multi-select like other filters:
		+	*** Client Company
		+	*** Shipper
		+	*** Consignee
		+	*** From/To Region
		+	*** From/To Country
		+	*** SKU
		+	** '''''Differences''''' from Client KPIs (Right-Bottom part)
		+	*** Added distribution of KPI per Client Companies in "Client Companies" tab
		+	*** Added ability to display Sales/Purchase/Margin distribution (available in "Needle Movers" mode)
		+	**** Every column has its own floating summary (Sales/Purchase/Margin) pop-up window
		+	*** Single click on a Bottom Right Panel updates Top Right Panel
		+	**** Selected column becomes grey-coloured
		+	*** Drilldown from right-bottom part is called via right-click mouse button
		+	*** Added Zoom feature (with mouse-drag selection)
		+
		+	=== SOW 51 KPI Admin v2 ===
		+
		+	==== KPI Admin ====
		+
		+	'''mant''':  4051
		+
		+	'''spec''': see [[#KPI Admin v2]]
		+
		+	=== SOW 53 Association and Exclusion logic v2 ===
		+
		+	'''mant''':  4072
		+
		+	'''spec''': below
		+
		+	This is a version 2 of a logic that we did not get completely right first time due to the bugs, spec bugs, change requests.
		+
		+	Please apply to all existing and all future KPIs.
		+
		+	==== Association between week and shipment ====
		+
		+	For every KPI we need to decide what criteria to use to associate shipment with a particular week/month/Quarter/Year.
		+
		+	Same '''association criteria''' for all classes of KPIs now:
		+
		+	Actual arrival date (air/ocean)
		+	or
		+	Actual Delivery Date (all truck modes)
		+
		+	See also [[#SOW 57 Association logic for TMS shipments]]
		+
		+	===== Association in case of groups =====
		+
		+	If [[Shipment]] is a group '''or load''' - see below.
		+
		+	All dates below for CTs in the group/load should be defined
		+
		+	Actual arrival date (air/ocean)
		+	or
		+	Actual Delivery Date (all truck modes)
		+
		+	Specific week/month/quarter/year will be associated with earliest date above among CTs in the group.
		+
		+	==== Exclusion logic ====
		+
		+	===== Exclusion for Arrived and Delivered =====
		+
		+	see association logic
		+
		+	===== Exclusion if shipment is deleted or alike =====
		+
		+	Exclude CTs that are:
		+	* canceled or
		+	* deleted or
		+	* rejected (from ASN)
		+
		+
		+	NOTE: do not exclude transmited to 3PL.
		+
		+	===== Exclusion if parameter in KPI formula is undefined =====
		+
		+	Examples:
		+
		+	* If KPI is a time frame then exclude shipments that have only one out of two dates/times that define the interval present in the system.
		+
		+	* In CPUs if cost is undefined or unit is undefined
		+
		+	===== Exclusion if need to divide by zero =====
		+
		+	=== SOW 55 ===
		+	==== Bugs and Changes from UAT of March 7 and 8 ====
		+
		+	NOTE: Marc/Alex tested with E0=Elizabeth Arden on dev4.
		+
		+	Format: ''Issue#  Summary // dev bug OR spec bug OR change // <status>''.
		+
		+	* 1 For some reason xls says "TimeFrame: 2013 YTD Average" (instead of weekly timeframe) for Left panel drilldowns for one week // dev bug // dev, qa - done
		+
		+	* 2 Add "Cost per Lb" as an output column option (for download to xls) // change // dev, qa - done
		+
		+	* 3 Left panel weekly number is different from same number on drill down xls // dev bug // dev, qa - done
		+	** (example: Pick Up to Delivered)
		+	** example:  total spend ocean
		+
		+	* 4 Shipment Group column contain strange info: decimals instead of integer // dev bug // dev - done, qa - pending
		+
		+	* 5 Each drill down xls should contain KPI itself as an output (as a column and as a total) // change request // dev - done, qa - pending
		+	** example: KPI name: Created to Delivered
		+
		+	* 6 Cost per Kg KPI calculating incorrectly on xls (per CT) // dev bug // dev, qa - done
		+
		+	* 7 Remove KPI title from HTML // change request // dev, qa - done
		+
		+	* 8 Mapping of Ship to Location needs to be changed for AIR / OCEAN  (air / ocean – Consignee SHOULD BE CONSIGNEE and/or Import Tab “delivery location” … not the “export pick up deliver to address”…
		+	** see also "0004014: [ASN 20] change mapping for "Ship To Location" in Air/Ocean records" // change // postponed till next Sprint
		+
		+	* 9 do not show CTs that have only one date out of two in the interval // spec bug // dev- done, in qa
		+
		+	* 10 = 5 (duplicate) always show KPI itself as a column on download to xls
		+
		+	* 11 Default value for “downloads” should always be: // change  // dev,qa - done
		+	** CT #
		+	** Shipper
		+	** PO
		+	** SKU
		+	** QTY
		+
		+	* 12 Include DUTY/VAT or Exclude DUTY/VAT option is not visible on screen (SPEC IS MISSING? LOW PRIORITY?) // postponed till next release
		+
		+	* 13 The labels for the “bottom right” pannel should be vertical // change // dev, qa - done
		+
		+	* 14 cost per pallet // dev in progress
		+	** calculations are incorrect on xls // dev bug
		+	** exclude CTs with plt=0 values // spec bug? or change request?
		+
		+	* 15 output columns selection to be replaced with drag and drop version // change // planned for next sprint
		+
		+	* 16 we found reports that include canceled or deleted CTs // spec bug // not done
		+
		+	=== SOW 56 Carrier defined ===
		+
		+	For Bottom Right Panel please use the following logic.
		+
		+	* For Air associate with Airline
		+	* For Ocean associate with Steamshipline
		+	* For all All Truck modes associate with only Pick Up  trucker only for now
		+
		+	=== SOW 57 Association logic for TMS shipments ===
		+
		+	In case of TMS associate CTs and timeframes (Weeks/month/quaters/years) based on this date:
		+	* Actual Delivery Date
		+
		+	This will ensure that all CTs associated with a load will be part of same timeframe. (per Marc/Stacy)
		+
		+	=== SOW 59 Round total spend and CPU values to 2 values after dot ===
		+
		+	mantis: 4061
		+
		+	See [[#Rounding]].
		+
		+	=== SOW 60 Split MOT Ocean options into FCL and LCL ===
		+
		+	'''Mantis''': 0004066
		+
		+	'''spec''':
		+
		+	In KPI main menu we have one option for all Ocean shipments.
		+
		+	Marc changed his mind and would like to have two options instead now:
		+
		+	* Ocean FCL (map fromFCL(AW) and FCL(MLB) internal options)
		+	* Ocean LCL (map from LCL and Client Consol internal options)
		+
		+	So we need to associate shipments with those two separately.
		+
		+	=== SOW 61 Changes to main menu values on one tab/KPI class should propagate to other tabs ===
		+
		+	'''mantis''': 0004060
		+
		+	'''spec''': below
		+
		+	Changes to some Main Menu values on one tab/KPI class should propagate to other tabs. Relevant to these two parameters:
		+
		+	* Client Company
		+	* MOT
		+
		+	For example if you are on "Counters" tab and change MOT to Air then if you click on Lead Times tab you should see MOT changed to Air.
		+
		+	Comment from developer: not as easy as we might think on a development side. But not too bad either.
		+
		+	=== SOW 62 TMS charges should populate Est tab ===
		+
		+	mant: 4062
		+
		+	moved to [[Estimation#SOW_2_TMS_charges_should_populate_Est_tab_automatically]]
		+
		+	=== SOW 63 Inclusion logic in case of transmit to 3PLs ===
		+
		+	'''mant''':  4089
		+
		+	'''spec''': draft below
		+
		+	Core requirement:
		+
		+	* we want these CTs to be avail in KPIs only for some group of users
		+
		+	Solution:
		+
		+	* always use separate E0 for Transmit to option
		+	* associate user with client group
		+	* add this E0 to the group to make Transmit to CTs available in KPI reports under this E0
		+
		+	Questions:
		+
		+	* I assume no programming required for this
		+
		+	=== SOW 65 Validation for Actual Delivery Date ===
		+
		+	'''mant''': 4118
		+
		+	'''spec''':
		+
		+	Create additional validation for Actual Delivery date:
		+
		+	System can not set / save Actual Delivery Date
		+	without Estimated Delivery Date being defined
		+
		+	'''Mapping''':
		+
		+	* [[CT#Actual Delivery Date]]
		+	* [[CT#Estimated Delivery Date]]
		+
		+	For all MOTs.
		+
		+	'''Special Case:'''
		+
		+	If this is empty when Actual Delivery Date is updated from external source such as TMS etc then update both with Actual.
		+
		+	'''Added''':
		+
		+	Automatically fill est date when enter actual date:
		+
		+	IF estimated date is EMPTY then copy actual date into estimated field
		+
		+	=== SOW 66 Change logic for KPI Earliest Date ===
		+
		+	'''mant''':  4102
		+
		+	'''spec''':
		+
		+	Base KPI Earliest Date on the same date logic that we use for association btw CT and week:
		+
		+	Actual arrival date (air/ocean)
		+	or
		+	Actual Delivery Date (all truck modes)
		+
		+
		+	NOTE: Old logic is here [[#KPI Earliest Date]]
		+
		+
		+	=== SOW 67 Replace Created On with Notification Date for all Dates KPI ===
		+
		+	'''mant''':  4096
		+
		+	'''spec''':
		+
		+	1/ Business reason:
		+
		+	In some cases operators create CT after it was departed or delivered. This creates problem on reporting side with such dates KPIs as:
		+
		+	* created to approved
		+	* created to delivered
		+
		+	2/ If we use notification date instead of created on date we can fix this by editing the date on internal.
		+
		+	3/ Apply this change to 2 existing dates KPI:
		+
		+	* created to approved
		+	* created to delivered
		+
		+
		+
		+	END OF WIKI
		+
		+	=== Cost related SOWs ===
		+
		+	==== SOW 52 Cost v2 ====
		+
		+	mant:  4071
		+
		+	spec: below
		+
		+	* New formula is the following:
		+
		+	'''If CT is closed'''
		+
		+	'''AND there is at least one inv linked to E0'''      [this line was added to old logic and to address case with TMS when most of the CTs will not have invoices OR any CT that are closed without having any invoices]
		+
		+	'''then show Actual Cost'''
		+
		+	'''else show Estimated Cost'''
		+
		+	NOTE:
		+	* Actual Cost = Sum of All Sales invoices minus APPROVED Credit Notes
		+	* Estimated Cost = Total of Sell Charges table in [[Estimation]] module.
		+
		+
		+	* (new!) Show only charges "linked to E0" through '''Client Company to Bill To link'''. See SOW 68 below.
		+
		+	==== SOW 68 Client Company to Bill To link ====
		+
		+	mant: 4078
		+
		+	moved to [[Client_Company#SOW_3_Client_Company_to_Bill_To_link]]
		+
		+	==== SOW 58 Add Duty and VAT switch ====
		+
		+	'''mantis''': 4074
		+
		+	'''spec''':
		+
		+	1/ We need to provide option to exclude all VAT/duty related charges that we apply:
		+
		+	* through charge codes
		+	** VAT = charge code 1204
		+	** Customs Duties = charge code 1201
		+
		+	2/ This should be applied to all cost related KPIs. Currently:
		+
		+	* Total Spend KPI from counters KPI Class
		+	* CPU
		+
		+	3/ UI:
		+	* drop down with options: "Exclude VAT/Duty; Include VAT/Duty"
		+	* make it a part of KPIs Main Menu
		+	* default is "Exclude..."
		+
		+	4/ user selection should be stored for next time when KPI will be opened
		+
		+	5/ (added)  In case of TMS must work the same way
		+
		+	==== SOW 64 Inclusion logic based on CC number ====
		+
		+	'''mant''': 4090
		+
		+	'''spec''':
		+
		+	0/ Business reason: CTs should not be included into KPI if it does not contain certain "core" charges. In some cases Jaguar plays "minor" role.
		+
		+	1/ If '''at least one cc from the cc list defined for particular MOT''' is present in TOC as defined in Inclusion CC Table (see example below) then include  this CT from KPI report.
		+
		+	2/ Inclusion CC Table example:
		+
		+	MOT              cc#
		+	----------------------------
		+	AIR              1101
		+	OCEAN FCL        1301
		+	OCEAN LCL        1401
		+	TRUCK            1501, 1503, 1505, 1598
		+
		+	3/ This is '''for CPU class only'''
		+
		+	=== SOW 69 Validation for Origin and Destination country ===
		+
		+	'''mant''': 4120
		+
		+	'''spec''':
		+
		+	At the moment Actual Departure is set and ready to be saved system should require that Origin and Destination country fields are set.
		+
		+	MOTs/mapping:
		+
		+	* ocean LCL/Client Consol: 11c. ATD:
		+	* air: 1c. ATD - Actual Time of Dept:
		+	* ocean FCL: 11c. ATD:
		+
		+	Added:
		+
		+	==== In case of grouped CTs system should validate for all CTs ====
		+
		+	Ask user to ENTER origin/dest countries and process GROUP SAVE (copy this value to all records).
		+
		+	==== Closed group option ====
		+
		+	'''added later!'''
		+
		+	If act dep date is set for at least one shipment in the group then any operations for a group is prohibited:
		+
		+	block edit/del group
		+
		+	==== Case when creating a group from some CT with Dep date and other without ====
		+
		+	In this case warning:
		+
		+	Please clear Dep date on all CTs or set all Orig/Dest countries.
		+
		+	==== if we set act dep date in one groupped CT then we have to force to set Orig and Dest country for all CTs in group ====
		+
		+	This is in only for CTs that have empty Origin / Dest country.
		+
		+	=== SOW 70 KPI Menu v2 ===
		+
		+	'''mant''': 4126
		+
		+	'''spec''':
		+
		+	'''Tweak layout:'''
		+
		+	* narrow the left panel by about 25%
		+
		+	'''Remove:'''
		+
		+	* remove "CT Home, Watch List, ..." menu
		+	* remove Main View dropdown
		+
		+	'''Introduce this hierarchy:'''
		+
		+	* Top level controls (selection applies to all KPIs)
		+	** Client Company
		+	** MOT
		+	** [[Sh#Geography Lane]]
		+	** Time period (weekly, monthly, ...)
		+
		+	* KPI class level controls
		+	** select KPI class
		+
		+	* KPI specific level controls
		+	** KPI name
		+	** KPI specific parameters (Ex: Duty/VAT control)
		+	** Settings button
		+	** "Number/Trend/%/Gage"
		+
		+	=== SOW 71 Drilldowns v3 ===
		+
		+	'''mant''': 4128
		+
		+	'''spec''':  see [[#Drilldowns v3]]
		+
		+	=== SOW 72 Import old Load records ===
		+
		+	'''mant''': 4125
		+
		+	'''spec''':
		+
		+	We need these data for period since we have started get loads from TMS till July of 2011. This is due to the fact that originally we did not have a code in place to copy all info into CT2 DB.
		+
		+	Descartes is to provide this data as an xls.
		+
		+
		+	=== SOW 73 Validation to ensure that containers are assigned ===
		+
		+	'''mant''': 4143
		+
		+	'''spec''':
		+
		+	Actual Departure Date can not be assigned if there are unassigned containers.
		+
		+	MOTs:
		+
		+	* Ocean, all submodes
		+	* Truck Ocean
		+
		+	Mapping: [[Sh#Actual Departure Date]].
		+
		+	=== SOW 74 CPU group C ===
		+
		+	'''mant''':
		+
		+	'''spec''':
		+
		+	See  [[#CPU group C]]
		+
		+	=== SOW 75 Needle Movers feature ===
		+
		+	'''mant''': 4152
		+
		+	'''spec''':
		+
		+	Changes are needed for bottom right panel:
		+
		+	* add "Needle movers option"
		+	* if selected show another plot over existing panel that displays Total spend in USD
		+	* sort values left to right from highest Total Spend to lowest
		+
		+	As implemented - see below:
		+
		+	[[File:Needle.JPG | 600px]]
		+
		+	==== sow 75 change requests ====
		+
		+	round 1:
		+
		+	* add "show 25" option
		+	* add average as dotted line
		+
		+	round2:
		+	* remove Avg Total Spend line
		+	* make dotted line lighter
		+
		+	=== SOW 76 Change colors for Gages ===
		+
		+	'''mant''': 0004155
		+
		+	'''spec''':
		+
		+	Low Med High now is expressed as Green - Yellow - Red.
		+
		+	Change to: Light Yellow - Green - Light Red. (to be confirmed)
		+
		+
		+	=== SOW 77 Layout and Graphics changes ===
		+
		+	'''mant''': 4157
		+
		+	'''spec''':
		+
		+	* make buttons more rounded
		+	* make buttons colored (blue as in "weekly")
		+	* move Settings to Main Menu bar
		+
		+	=== SOW 78 Change layout for No data to display case  ===
		+
		+	'''mant''': 4158
		+
		+	'''spec''':
		+
		+	Change layout for No data to display case: replace all panels by a blank page and print "No data to display" in the center.
		+
		+	=== SOW 79 ===
		+
		+	====  Change Rounding logic ====
		+
		+	'''mant''': 4138
		+
		+	'''spec''':  [[#Rounding]] section is completely re-defined.
		+
		+	=== SOW 80 ===
		+
		+	====  Redefine Top Right panel in case WMQY control is Yearly  ====
		+
		+	'''mantis:''' 0004192
		+
		+	'''related section:''' [[#WMQY selector]]
		+
		+	'''spec''':
		+
		+	For WMQY control option = "Yearly" change this on Top Right Panel:
		+
		+	* remove horizontal dotted average lines for years
		+	* remove "YTD Average" label on the right
		+	* in dot associated pop-ups remove "YTD Average words"
		+
		+	=== SOW 81 ===
		+
		+	==== Enable single click on a Bottom Right Panel to update Top Right Panel ====
		+
		+	'''mantis:'''  0004193
		+
		+	'''related section:''' [[#Top Right Panel]]
		+
		+	'''spec''':
		+
		+	Changes:
		+
		+	* make drilldowns available on dbl click instead of single (if user clicks on a Bar)
		+	* on single click instead re-draw Top Right Panel - it will show KPI plot only of that value (say Shipper X)
		+
		+	=== SOW 82 ===
		+
		+	==== Logic and labels for Bottom panel depending on values of WMQY selector ====
		+
		+	'''mantis:'''  4197
		+
		+	'''related section:''' [[#WQMY selector]]
		+
		+	'''spec''':
		+
		+	Changes for Bottom Panel Label:
		+
		+	- if no specific time period on the Top Panel is selected then:
		+	for options: W, M, Q: display YTD <current year>
		+	for options: Y: display Year <last full year>
		+
		+	- else:
		+	display extended description of selected Time Period
		+
		+	=== SOW 83 ===
		+
		+	==== Average line for bottom chart in non Volumes KPI redefined ====
		+
		+	'''mantis:'''  0004198
		+
		+	'''related section:'''
		+
		+	'''spec''':
		+
		+	Average line for bottom chart in Line Optimization KPI: it should be equal to value of selected time period at the top chart.
		+
		+	Should be in the all KPIs except Volumes KPI.
		+
		+	=== SOW 84 ===
		+
		+	==== Add Avg to some KPI prefixes  ====
		+
		+	'''mantis:'''    4201
		+
		+	'''related section:'''
		+
		+	'''spec''':
		+
		+	* Add Avg to all KPI prefixes but Volumes KPIs
		+	* do it everywhere: UI, xls, etc
		+
		+	=== SOW 85 ===
		+
		+	==== Add more global filters: SKU, Shipper, Consignee, PO Issued By  ====
		+
		+	'''mantis:'''    4223
		+
		+	'''related section:'''
		+
		+	'''spec''':
		+
		+	* Add to Main Menu new filters:
		+
		+	SKU,
		+	Shipper,
		+	Consignee,
		+	PO Issued By
		+
		+	(will be applied to all KPIs)
		+
		+	Shipper, Consignee:
		+	* combobox
		+	* select from lists in Address Book
		+
		+	SKU:
		+	* text field
		+	* if SKU# is present in shipment then include that shipment
		+
		+	PO Issued By:
		+	* combobox
		+	* show options associated with planner role
		+	* if not planner do not show this filter
		+
		+	=== SOW 86 ===
		+
		+	==== Add additional field options to drilldowns  ====
		+
		+	'''mantis:'''  4222
		+
		+	'''related section:'''
		+
		+	'''spec''':
		+
		+
		+	Add additional field options to drill downs:
		+	- Pallets
		+	- Loose
		+	- CBM (for LCL)
		+	- CWT (for AIR)
		+
		+	=== SOW 87 Cost per CBM ===
		+
		+	'''mantis:'''    4288
		+
		+	'''related section:'''
		+
		+	'''spec''': [[#Cost per CBM]]
		+
		+
		+	=== SOW 88 Cost per Chargeable weight ===
		+
		+	'''mantis:'''    4289
		+
		+	'''related section:'''
		+
		+	'''spec''': [[#Cost per chargeable weight]]
		+
		+	=== SOW 89 Cost per TEU ===
		+
		+	'''mantis:'''    4290
		+
		+	'''related section:'''
		+
		+	'''spec''': [[#Cost per TEU]]
		+
		+
		+	=== SOW 90 (DR4 Lead Time KPI) - Departure to Delivery ===
		+
		+	Mantis: [http://ct.jaguarfreight.com/mantis/view.php?id=4522 4522]
		+
		+	* Add additional lead time date range to measure time for shipment to get number of days since shipment departed to shipment delivered.
		+	* Meaning: This is measuring between actual date of departure of shipment and actual date of shipment's delivery.
		+	* Label: '''Departed to Delivered'''
		+	* MOT: For any Truck shipments use Actual Pick up Date as date of departure. For all other MOTs use Actual Departure Date.
		+	* KPI value, functionality, time frame, drill down, Left panel, Top and Bottom right panels are similar to other date pairing KPIs.

---

Current revision as of 13:24, 31 March 2014

Contents 1 Info 1.1 Mantis 1.2 Environments 1.3 SVN 1.4 Team 2 Glossary 3 Requirements 3.1 2012 3.2 2013 3.2.1 Free KPIs 3.2.2 KPI Classes 3.2.3 Individual KPIs 4 DR1 and DR2 5 KPI Framework 5.1 Link to KPIs 5.2 Standard Layout 5.3 KPI class tabs 5.4 Title 5.5 Main Menu 5.6 KPI Panel 5.6.1 WMQY selector 5.6.2 Tolerance levels parameters 5.6.2.1 Notifications for Traffic light 5.7 Left panel 5.7.1 Left panel drilldown 5.8 Top Right Panel 5.8.1 YTD average line 5.8.2 Year Selector Panel 5.9 Bottom right panel 5.9.1 Mock up 5.9.2 Lanes Tab 5.9.3 Bottom right panel drill down 5.10 Visibility 5.11 KPI Admin v1 5.11.1 System wide settings 5.11.1.1 KPI Earliest Date 5.11.2 User specific settings 5.12 KPI Admin v2 5.12.1 KPI Admin v2 Double click functionality 5.13 Misc Standards 5.13.1 Rounding 5.13.2 Shipment defined 5.13.3 MOTs 5.13.4 Mappings 5.13.5 Association Inclusion and Group logic 5.14 Parameters used in KPIs 5.14.1 Cost 5.14.2 Gross Weight 5.15 Drilldowns v2 5.16 Drilldowns v3 5.16.1 HTML view 5.16.2 drilldowns header 5.16.3 filenaming 5.16.4 fields list v3 5.16.4.1 Load attributes 5.16.4.2 Shpt attributes 5.16.4.3 CT attributes 5.16.4.4 SKU attributes 5.16.4.5 Default selected values in fields selector 5.16.4.6 KPIs in drilldown 5.16.5 xls format v3 5.16.5.1 note on cost associated with SKU line item 5.16.5.2 drilldown v3 xls examples 5.16.5.3 How to output KPIs 5.16.5.4 xls datatype 5.16.6 field selector v3 5.16.6.1 unit for weight control 5.16.6.2 KPI value label 5.16.7 SOW 71 Change requests 6 Cost Per Unit KPI Class 6.1 CPU General Info and List 6.2 CPU group A 6.2.1 Cost per Lb 6.2.2 Cost per Kg 6.3 CPU group B 6.3.1 Cost per chargeable weight 6.3.2 Cost per CBM 6.4 CPU group C 6.4.1 Cost per TEU 6.4.2 Cost per Container 6.4.3 Cost per item 6.4.4 Cost per Pallet 6.4.5 Cost per Carton 6.5 CPU Layout 6.6 DR3 Phase 1A: EA DOM Portal only 7 Dates KPI Class 7.1 Dates KPI General Info 7.2 List of Dates KPIs 7.3 Dates KPI group A 7.4 Dates KPI group B 7.5 Dates KPI group C 7.5.1 Created on Date to Cargo Due Date 7.5.2 Basic to Super approved 7.5.3 Average Transit Times KPI 7.5.4 Time To Update 7.6 Dates KPI group D 7.6.1 File Closing Metrics 7.7 Dates KPI Title 7.8 Dates KPI Main Menu 7.9 Dates View KPI panel 7.10 Dates KPI Left panel 7.11 Dates KPI Top Right panel 7.12 Dates View Bottom Right panel 7.13 Precision for Dates class 8 Counters KPI Class 8.1 Counters General Info 8.2 Counters group A 8.2.1 Shipments Count 8.2.2 CT Count 8.2.3 Total Spend 8.3 Main Menu 8.3.1 View Type 8.4 Layout 8.5 Counter View Title 8.6 KPI Panel 8.7 Left Panel 8.8 Top Right Panel 8.9 Bottom Right Panel 8.10 Apply to panels 9 LO and EU KPI classes 9.1 LO class 9.1.1 Avg Shipments per Lane 9.2 EU class 9.2.1 KG per shipment 9.2.2 LB per shipment 9.2.3 Pallets per shipment 9.2.4 CBM per shipment 9.2.5 Chargeable Weight per shipment 9.2.6 Pallets per container 9.2.7 CBM per container 9.2.8 Misc 10 SOWs Part A 10.1 Sequence 10.2 3567 DR1 Simple Shipment count DR 10.3 3575 Evaluate LogiXML 10.4 0003583 DR2 Complex Shipment Data per Client per Year DR, PART A 10.5 0003595: [DR/KPI] DR3 Phase 1A: EA DOM Portal only 11 SOWs Part B 11.1 SOW 0 DR4 Dates KPI 11.2 SOW 1 Spec gaps and some changes 11.3 SOW 2 Look and feel changes 11.4 SOW 3 KPI Admin on Internal and Client 11.5 SOW 4 (DR3 Cost Per Unit KPI, EA DOM only) Add all other Views 11.6 SOW 5 Add Quarters 11.7 SOW 6 Cost per pound is incorrect for some records 11.8 SOW 7 Remove pick up and/delivery trucker switch and display for only Pick up data 11.9 SOW 8 For Bottom Right panel add drill down to CT and load level 11.10 SOW 9 Show all data from KPI Earliest Date 11.11 SOW 10 Left panel drill down 11.12 SOW 11 DR4 Dates KPI (On time performance: Truck dom (TMS) only ) 11.12.1 Additional info from Leadtime KPI 11.12.2 Layout 11.12.3 KPI Panel 11.12.4 Left Panel 11.12.5 Top Right Panel 11.12.6 Bottom Right Panel 11.12.6.1 CT count Tab 11.12.6.2 Other tabs 11.12.7 OTIP (Day) 11.12.8 OTIP (%) 11.13 SOW 12 Elevate 0 value graph in order to see a bar chart for zero value 11.14 SOW 13 DR3 Cost per Unit (cost per LB and cost per KG for all modes) 11.15 SOW 14 Add Yearly as well as make button narrower and rename buttons 11.16 SOW 15 Change status bar value display 11.17 SOW 16 Connect dot timeline chart 11.18 SOW 19 DR7 Overlap 11.19 SOW 20 DR3 Cost Per Unit KPI: Extend this KPI to other Client Companies 11.20 SOW 21 Top Right Panel: Show all years graphs since May 2010 with all YTD average 11.21 SOW 22 Bottom Right Panel: Spell out what value based on 11.22 SOW 23 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units) 11.22.1 Layout and Cost per unit view title 11.22.2 KPI Panel 11.22.3 Logic 11.22.4 Left Panel 11.22.5 Top Right and Bottom right panel 11.22.6 Apply to panel 11.22.7 Main View 11.23 SOW 24 (DR4 Lead Time KPI) Extend this KPI to other MOTs (all units) 11.24 SOW 25 (DR5 Total counter KPI) Extend this KPI to other MOTs (all units) 11.25 SOW 26 DR5 Total counter KPI : Extend this KPI to other views 11.26 SOW 27 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other MOTs (all units) 11.27 SOW 28 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other views 11.28 SOW 29 (DR4 Lead Time KPI) Add additional KPIs to measure cargo due date 11.29 SOW 30 (DR4 Lead Time KPI) who and when - Updates and Time to update Actual Departure date and Actual delivery date KPIs 11.29.1 KPI Panel 11.29.2 Logic 11.29.3 Left Panel 11.29.4 Top Right Panel 11.29.5 Bottom Right Panel 11.29.6 Apply to panels 11.29.7 Updates 11.29.8 KPI Panel 11.29.9 Logic 11.29.10 Left Panel 11.29.11 Top Right Panel 11.29.12 Bottom Right Panel 11.29.13 Apply to panels 11.29.14 Time to update 11.30 SOW 31 (DR4 Lead Time KPI) - how long it takes between Basic planner and Super planner to approve 11.31 SOW 33 Add Year Selector Panel to the top right panel 11.32 SOW 34 RFC: change the chart color for bar chart to be one color instead of multicolor 11.33 SOW 35 Lead Time: The vertical lable of top right panel shows "Cost per LB, USD" --> change to "Average Lead Times, in Days" 11.34 SOW 36 RFC: Cost and Lead time KPI - decimal points 11.35 SOW 37 RFC: Bar's width of top/bottom 5 12 SOWs part C 12.1 SOW 38 Define and implement user data visibility for KPIs 12.2 SOW 17 Counters:group A 12.3 SOW 39 Dates KPI:group A 12.4 SOW 40 CPU KPI:group A 12.5 SOW 41 Pull weight when calculating KPIs always from CT2 DB 12.6 SOW 32 Add PO Issue By filter to main menu 12.7 SOW 43 Counters KPI:group B 12.7.1 Gross KG 12.7.2 Package count 12.7.3 Pallets Count 12.7.4 Containers Count 12.7.5 CBMs count 12.7.6 Chargeable Weights count 12.7.7 Drilldowns and XLS for Counters KPI group B 12.8 SOW 44 Dates KPI:group B 12.8.1 On time performance 12.8.1.1 Negatives on Left Panel 12.8.2 Departure to Arrival 12.8.3 Approved on Date to Cargo Due Date 12.8.4 SOW 44 Changes 12.9 SOW 45 CPU KPI:group B 12.10 SOW 42 Implement Overlap KPIs 12.11 SOW 18 LO and EU classes 12.12 SOW 46 Dates KPI group C 12.13 SOW 47 CPU group C 12.14 SOW 48 File Closing Metrics 12.15 SOW 49 Notifications for Traffic light 12.16 SOW 50 Enable KPIs for internal users 12.17 SOW 51 KPI Admin v2 12.17.1 KPI Admin 12.18 SOW 53 Association and Exclusion logic v2 12.18.1 Association between week and shipment 12.18.1.1 Association in case of groups 12.18.2 Exclusion logic 12.18.2.1 Exclusion for Arrived and Delivered 12.18.2.2 Exclusion if shipment is deleted or alike 12.18.2.3 Exclusion if parameter in KPI formula is undefined 12.18.2.4 Exclusion if need to divide by zero 12.19 SOW 55 12.19.1 Bugs and Changes from UAT of March 7 and 8 12.20 SOW 56 Carrier defined 12.21 SOW 57 Association logic for TMS shipments 12.22 SOW 59 Round total spend and CPU values to 2 values after dot 12.23 SOW 60 Split MOT Ocean options into FCL and LCL 12.24 SOW 61 Changes to main menu values on one tab/KPI class should propagate to other tabs 12.25 SOW 62 TMS charges should populate Est tab 12.26 SOW 63 Inclusion logic in case of transmit to 3PLs 12.27 SOW 65 Validation for Actual Delivery Date 12.28 SOW 66 Change logic for KPI Earliest Date 12.29 SOW 67 Replace Created On with Notification Date for all Dates KPI 12.30 Cost related SOWs 12.30.1 SOW 52 Cost v2 12.30.2 SOW 68 Client Company to Bill To link 12.30.3 SOW 58 Add Duty and VAT switch 12.30.4 SOW 64 Inclusion logic based on CC number 12.31 SOW 69 Validation for Origin and Destination country 12.31.1 In case of grouped CTs system should validate for all CTs 12.31.2 Closed group option 12.31.3 Case when creating a group from some CT with Dep date and other without 12.31.4 if we set act dep date in one groupped CT then we have to force to set Orig and Dest country for all CTs in group 12.32 SOW 70 KPI Menu v2 12.33 SOW 71 Drilldowns v3 12.34 SOW 72 Import old Load records 12.35 SOW 73 Validation to ensure that containers are assigned 12.36 SOW 74 CPU group C 12.37 SOW 75 Needle Movers feature 12.37.1 sow 75 change requests 12.38 SOW 76 Change colors for Gages 12.39 SOW 77 Layout and Graphics changes 12.40 SOW 78 Change layout for No data to display case 12.41 SOW 79 12.41.1 Change Rounding logic 12.42 SOW 80 12.42.1 Redefine Top Right panel in case WMQY control is Yearly 12.43 SOW 81 12.43.1 Enable single click on a Bottom Right Panel to update Top Right Panel 12.44 SOW 82 12.44.1 Logic and labels for Bottom panel depending on values of WMQY selector 12.45 SOW 83 12.45.1 Average line for bottom chart in non Volumes KPI redefined 12.46 SOW 84 12.46.1 Add Avg to some KPI prefixes 12.47 SOW 85 12.47.1 Add more global filters: SKU, Shipper, Consignee, PO Issued By 12.48 SOW 86 12.48.1 Add additional field options to drilldowns 12.49 SOW 87 Cost per CBM 12.50 SOW 88 Cost per Chargeable weight 12.51 SOW 89 Cost per TEU 12.52 SOW 90 (DR4 Lead Time KPI) - Departure to Delivery

[edit] Info

[edit] Mantis

parent: 3930

• categories: DR/KPI:ph1, DR/KPI:ph2, DR/KPI:ph3

[edit] Environments

• QA/SIT/UAT:
  • http://dev4-kuchma.jaguarfreight.com/internal
  • http://dev4-kuchma.jaguarfreight.com/Client

Good account to test with:

• name: KPI_User@com.com
• pwd: Admin1234

[edit] SVN

• /branches/DR_KPI

[edit] Team

• Sponsor: Simon
• UAT/Product Manager: Marc
• SA: Alex
• PM: Tira
• Dev1: Sasha
• Dev2: Misha
• Dev3: KU
• QA: Roma
• Graphics: Vic

Email groups:

alex@jaguarfreight.com; a.pivniak@elcosol.com; k.ushakov@elcosol.com; v.makhankov@elcosol.com; r.lakhno@elcosol.com; m.tymoshenko@elcosol.com; montira@jaguarfreight.com;

[edit] Glossary

• BI - Business Intelligence

[edit] Requirements

[edit] 2012

• This Phase One should include first portion of DRs/KPIs delivered

• Must be production quality

• This phase is for Arden primarily (all MOTs) but also for Sales Dept to show possibilities to prospects and existing Clients

• This project is very important top visibility project

• Also right tool/lib should be evaluated/selected for the future (example: LogiXML vs ZK API/other Java APIs)

[edit] 2013

Simon asked to release all classes of KPIs by April One.

Simon/Marc identified limited list of "Free Client KPIs", to be released ASAP:

[edit] Free KPIs

1. Counters: Shipment Count

2. Counters: Total spend (including or excluding VAT)

3. EU: Average KG per shipment

4. EU: Average pallets or cartons per shipment

5. Dates: On time performance (estimated vs actual date unless an actual “due date” is available (ASN Portal customers)

6. Dates: Average transit times

[edit] KPI Classes

KPI classes (groups):

• Cost per unit (CPU)
• Lead Times
• Counters
• Lane Optimization (LO)
• Equipment Utilization (EU)
• Overlap

[edit] Individual KPIs

Cost per unit (CPU)

a. Cost per Lb

b. Cost per Kg

c. Cost per Item

d. Cost per Pallet

e. Cost per Carton

f. Cost per chargeable Weight (air)

g. Cost per container (FCL)

h. Cost per CBM (LCL)

Dates (Lead Times)

a. Created to delivered

b. Approved to delivered

c. Created to Approved

d. Approved to pick up

e. Pick up to delivery

f. On Time performance (OTIP)

g. Created to Cargo Due

h. Approved on Date to Cargo Due

i. Basic to Super planner approved

k. Time to update

Counters

a. Shipment Count

b. CTs count

c. Gross KG Shipped

d. Cartons count

e. Pallets count

f. Container count

g. CBMs count

h. Chargeable weight count

i. Total Spend

Lane Optimization (LO)

a. Average shipments per lane (per week, per month)

Equipment Utilization (EU)

a. KGs per shipment

b. LBs per shipment

c. Pallets per shipment

d. CBM per shipment

e. Avg Pallets per container (FCL)

f. CBM per container

Overlap

a. Overlap Equipment Utilization against CPU

b. Overlap Lane Optimization against CPU

[edit] DR1 and DR2

! These reports are no longer in use

Moved to DR KPI Phase One DR1 DR2

[edit] KPI Framework

[edit] Link to KPIs

KPI link will show up for users who have access to at least one KPI. See Figure below.

This link will lead to KPIs multi tab view - see another Figure in the #Standard Layout section below.

[edit] Standard Layout

KPIs are to conform to standard layout that is well defined.

KPI link leads to KPI Home page - see Figure below.

Page has multiple tabs - one per class.

On each tab there is a KPI selector that allows user to choose one KPI from the class.

Page contains info for one KPI at each moment in time.

KPI Page consists of the following sections/panels:

• #KPI class tabs
• #Title
• #Main menu
• #KPI panel
• #Left panel
• #Top right panel
• #Bottom right panel

Please notre that definitions of above sections differ depending on what type of View is selected. See #Views

[edit] KPI class tabs

On the highest level we spread all KPIs across tabs. One KPI class per tab.

[edit] Title

This is a title for current page (for selected tab).

[edit] Main Menu

[edit] KPI Panel

• individual KPI selector: Ex: cost per kg

• Unit of measurement: Ex: USD

• number: actual value of KPI (if "weekly" is selected then average for last full week, if "monthly" - average for last full month)

• arrow change indicator: comparatively to previous period defined by "timeframe" (up - red, down - green)

• percentage: change in % comparatively to previous period defined by "timeframe"

• traffic light: Gage, indicates tolerance level, see #Tolerance levels parameters

[edit] WMQY selector

4 options:

• Weekly
• Monthly
• Quarterly
• Yearly

[edit] Tolerance levels parameters

This is aka "Traffic light"

• this is a list of parameters managed by user in user profile
• controls what is considered green vs yellow vs red zone for KPI

[edit] Notifications for Traffic light

• When color changes from yellow to red send e-mail with notification to user
• In user profile on Client under specific KPI allow to turn on/off notification. By default it is off

[edit] Left panel

Left column:

• weeks to date sorting from current week to previous weeks (current week, last week, previous week .... )
• arrow indicator - change from previous week

• Mouse over value description

• gage indicator

• Mouse over value description

Right column:

• KPI value

• Mouse over value description

• single click on a table row of the Left Panel functionality:

• • Highlight a table row for a particular week/quarter/month to the row that is selected
  • A dot on the graph for a particular week/quarter/month on top right panel is highlighted. If there is no data (empty), no highlight is needed.
  • Apply a bottom right panel to show a particular week/quarter/month information chart for each tab (Shippers, Consignees, Carriers, and Lanes). Display a frame label to show what data/value based on such as X Week/Y Quarter/Z Quarter or 2012 YTD average
  • ie. Cost per LB with weekly KPI, user clicks on 28 week (08-14 Jul 2012) then a dot on the graph for 28 week on the Top right is highlighted. The bottom right panel shows bar or pie segment chart with number for top/bottom display for a particular 28 week information. Display a frame label as 28 week (08-14 July 2012).

[edit] Left panel drilldown

• double clicks on a table row of Left Panel
  • Show pop-up panel with download to excel button
  • Select output fields option window pop-up for user to select output list and click OK
  • Download Excel spread sheet

[edit] Top Right Panel

Axis X: all weeks to date (w1, w2, .... ) or months to date (Jan, Feb,....Dec)

Axis Y:

• plot A: KPI this year and #YTD average line for this year
• plot B: KPI this year -1 and #YTD average line for this year
• plot C: KPI this year -2 and #YTD average line for this year
• etc... ;what is the oldest data to display is manager through KPI Admin

[edit] YTD average line

This is a horizontal line on top right plot indicating "YTD average" for KPI, say cost per kg.

[edit] Year Selector Panel

Has been added in SOW 33.

• Compare graph for all years as well as YTD average for all years

• Ability to choose what years graph to show/hide

• Grid table control graph each year
• Each year has different color in order to differentiate years
• Trend shows data point each time frame each year
• YTD Average data show side by side with year trends selected
• Provide/show list of all years for trend and YTD with enable/disable graphs
• Scrolling up/down if there are long list of years
• Sorting years from earliest year to latest year top to bottom

• Currently we will list data from year 2011, so there will be year 2011 and 2012 on the table grid at this time. Future the list will be added under earlier year.

NOTE: See mock up below for concept of the requirements

[edit] Bottom right panel

[edit] Mock up

[edit] Lanes Tab

Mapping: Sh#Shipper to Consignee Lane.

[edit] Bottom right panel drill down

see #SOW 71 Drilldowns v3

[edit] Visibility

For given KPI that is enabled for given Client user use only CT records that satisfy "default client user visibility" (E0 or E0 Group).

[edit] KPI Admin v1

[edit] System wide settings

Add Admin > KPI to manage system wide KPI module related settings.

Currently only one parameter to be managed: #KPI Earliest Date.

[edit] KPI Earliest Date

Defines cut off date for CT records based on Created on date. Records created earlier than this date will not be used in KPIs.

[edit] User specific settings

Note that currently we have some sort of admin. This is re-design.

• On internal users with role=Client will be set up by Jag operators to get access to some KPIs.

• We need to have list of all KPIs available in the system be present in this UI.

• Also we should be able to enable KPI just for some MOTs.

• Need enable/disable drill down switch per user

• Finally many KPIs have "KPI parameters" (such as intervals for Gages). They should be set through this Admin as well.

• As far as layout It should have tree like structure:

• KPI class A
  • KPI 1
  • KPI 2
  • ...

• KPI class B
  • KPI 1
  • KPI 2
  • ...

Once some KPIs are enabled these client users should be able to manage enabled KPIs in their users profiles.

[edit] KPI Admin v2

This is a version 2 of a logic that we did not get completely right first time due to the bugs, spec bugs, change requests.

Link to the spec for previous version of Admin: #KPI Admin.

• 0/ (on hold!) All parameters will be managed for individual KPIs and not classes as programmed now. See mockups below

• 0.1/ Top level will look like this:

• 0.2/ If user selects Cost per Lb and clicks edit he will see pop up where he can edit indiv KPI settings:

• Design above is on hold pending Multi Editor Proposal

• 1/ (on hold!) Add CT2 Internal MAIN MENU > Admin > KPI Global Admin section to manage system wide KPI module related settings
• 2a/ Make #KPI Earliest Date (now set to 01-Jan-2011) to be system wide:
  • 2.1a manage through servers config file
  • 2.2a display current value as read only in above mentioned Admin section
  • 2.3a remove this date as a view only date from individual KPI panel

• Design above is on hold pending Multi Editor Proposal !!!

• 2b/ (moved to SOW 50) tag each individual(!) KPI as internal vs external (hardcode)

• 3/ (on hold!) Add new parameter to individual KPIs: enable/disable client company filter (when visibility is linked to the client group)
  • [give 3 options: all together; All and list of individual E0 in the group; individual its only]
  • this is on hold - do not implement now !!!

• 4/ (on hold!) Add KPI section to Internal MAIN MENU > Admin > Client Companies
  • to manage KPI parameters
  • in this section we should:
    • enable / disable individual (!) KPis for Client users linked to given company through default E0 visibility
    • manage all parameters for this company
  • settings above will propagate into user profile on internal where they could be tuned further for specific user
  • changes in parent Client Comp record should propagate into user profile on internal
  • Design above is on hold pending Multi Editor Proposal !!!

• 5/ None of the settings needs to be available for change in users profile (by user) on Client. See mock up below:

• 6/ Gage parameters changes:
  • 6.0/ Should be only managed through "Settings" panel right on the KPI page as well. (!) Remove These settings from Indiv KPI Admin.
  • 6.1/ Remove "From created on date" from this panel.
  • 6.2/ See mock up below:

[edit] KPI Admin v2 Double click functionality

• 7/ Double click functionality to edit should be available as well.
  • Currently it works when click on the list and on "edit" button. This functionality works well but would be great to allow double click feature to save time. As to do one by one by Edit button is time-consuming.

[edit] Misc Standards

[edit] Rounding

For selected period:

• If values are max-min<=10 then system shows 3 decimals, else shows 2 decimals.

[edit] Shipment defined

see Sh#Shipment

[edit] MOTs

Use all MOTs.

If there is no data for given MOT - do not show it in selector.

[edit] Mappings

Shippers:

• same for all MOTs
• maps to Ct#Shipper

Consignees:

• same for all MOTs
• maps to Ct#Consignee

Carriers:

We have two fields for Trucking company: Export Pick-up Trucker, Delivery Trucker. 'So if one shipment has both fields set then I assume both companies get credit as far as shipment count and weight.' THIS NEEDS TO A NEW SOW !!!!

[edit] Association Inclusion and Group logic

#SOW 53 Association Inclusion and Group logic

[edit] Parameters used in KPIs

[edit] Cost

Calculated as a sum of all sales invoices minus credit notes amounts across all CTs.

Exception is TMS case: in this case cost comes from TMS with loads.

this formula will change! - see #SOW 52 Cost v2.

[edit] Gross Weight

Calculated as a sum of Gross Weights of individual CTs.

[edit] Drilldowns v2

Hidden - old version

[edit] Drilldowns v3

Drilldown is a functionality that gives "GRP/CT/Commodity item level details" for particular segment of visual representation for given KPI.

Defined for:

• selected time frame (week/month/quarter/year) as defined on Left Panel
• same data but restricted to specific Shipper/Consignee/Carrier/Lane(Bottom Right Panel)

Produce pop-up where user can select output fields.

What did not change:

• #drilldowns header

Number of things will change:

• #HTML view
• #new admin options v3
• #fields list v3
• #field selector v3
• #xls format v3
• #filenaming

[edit] HTML view

No need to show html view first

[edit] drilldowns header

• A the top print title in large bold that consists of: <KPI class name> + " KPI"
  • example:

Cost per Unit KPI

• below - list KPI name and number of shipments
  • example:

KPI name: Cost per Kg
Num of shipments: 12

• below - all additional conditions as defined by filters
  • example

Client Company: EA
Mode of Transport: Air
Timeframe: 01-30 Jan 2013
Shipper: Elmar

• below - additional info
  • example

Currency: USD

[edit] filenaming

KPI Type + Week #, Year + (if appropriate: Company Name (first word in name only))

Example:

Shipment Count wk 13 2013 CEI.xls

[edit] fields list v3

Below fields defined as:

• <label as it appears on pop-up and xls> // <meaning and/or mapping>

[edit] Load attributes

• Load# //
• Load Kg //
• Load Lb //
• Load cost // Est or Actual Sales Charges (not anymore from XML!)
• Load cost per Lb //
• Load cost per Kg //

• Carrier(s)

[edit] Shpt attributes

• Shpt# // post GRP# if CT is grouped and CT# if not
• MOT
• Shpt Kg // Gross weight for GRP in kg
• Shpt Lb //
• Shpt cost // Est or Actual Sales Charges (not anymore from XML!)
• Shpt cost per Lb //
• Shpt cost per Kg //

• Origin City // City from Sh#Origin Door (from CT with a lowest number in a group)
• Origin State // State from Sh#Origin Door (from CT with a lowest number in a group)
• Origin Country // Country from Sh#Origin Door (from CT with a lowest number in a group)
• Origin Region // Region from Sh#Origin Door (from CT with a lowest number in a group)

• Destination City // City from Sh#Destination Door (from CT with a lowest number in a group)
• Destination State // State from Sh#Destination Door (from CT with a lowest number in a group)
• Destination Country // Country from Sh#Destination Door (from CT with a lowest number in a group)
• Destination Region // Region from Sh#Destination Door (from CT with a lowest number in a group)

• Carrier(s)

[edit] CT attributes

• CT#
• MOT
• Shipper // Company name, do we need this field?
• Approved by Planner // name, ASN only
• Approved by Super Planner // name, ASN only
• P.O. Issued by // ASN only
• Created On // date
• Authorized // date
• Act. Pick Up // date
• Act. Delivery // date
• Est. Pick Up // date
• Est. Delivery // date

• Origin City // City from Sh#Origin Door
• Origin State // State from Sh#Origin Door
• Origin Country // Country from Sh#Origin Door
• Origin Region // Region from Sh#Origin Door

• Destination City // City from Sh#Destination Door
• Destination State // State from Sh#Destination Door
• Destination Country // Country from Sh#Destination Door
• Destination Region // Region from Sh#Destination Door

• Carrier(s)

• Commodity Description // commodity attribute
• Haz // commodity attribute
• CT Lb // commodity attribute
• CT Kg // commodity attribute
• Stack // commodity attribute

[edit] SKU attributes

• SKU
• PO
• Qty
• Description
• Plts
• Pkg on plts
• Loose
• Gross Kg
• Gross Lb
• Piece price // ASN only
• Haz // ASN only
• Stack // ASN only

[edit] Default selected values in fields selector

   GRP# (or load# for TMS)
   CT#
   Shipper
   PO
   SKU
   QTY
   current KPI  

[edit] KPIs in drilldown

• list of KPIs enabled for particular users in User Admin
• if there are performance issues with adding many KPIs as output option then limit to say 3 KPIs max (advice user about this in some way)

[edit] xls format v3

• see mock up below

• xls will have groups of columns:
  • Load or GRP
  • CT
  • SKU

• if at least one field from particular section above is defined (GRP/Load or CT or SKU) then this section is defined and its id should be autoselected by system (GRP#/Load# or CT# or SKU#)

• total values are defined on top line with "-" below (see for example Load Lb column)

• if user chooses one field from GRP level and one from item level (skipping CT#) then system should select CT# field automatically

[edit] note on cost associated with SKU line item

Cost should be distributed across line items proportionally based on Gross Weight

[edit] drilldown v3 xls examples

!!! This mock up is out of date !!!

X - GRP fields

Y - GRP KPI

Z - CT fields

W - CT KPI

V - SQU fields

Q - Dates KPI

[edit] How to output KPIs

• always output "current KPI" (if it is enabled in Admin)

User selections:

VOLUMES:

• sh count // remove from the list
• ct count // remove
• total spend // calculate and output per shipment
• gross kg // remove
• cwt kg // calculate and output per shipment
• containers/TEU // calculate and output per shipment
• CBM // calculate and output per shipment

CPU:

• cost per lb // calculate and output per shipment
• cost per kg // calculate and output per shipment
• cost per item // calculate and output per shipment
• cost per package // calculate and output per shipment
• cost per pallet // calculate and output per shipment

LEAD TIMES:

• 6 date KPIs // calculate and output per shipment

LO:

• shipments per lane // calculate and output per shipment (show number of shpments on lane it belongs to, see example below)

Example (LO as an output parameter)
-----------------------------------
if for given "reporting segment" on which user clicks:
lane X: sh# 1,3,7, 9 (display 4 for every shpmnt on this list)
lane Y: sh# 2,5 (display 2 for every shpmnt on this list)
by the way LO for this "reporting segment" is (4+2)/2=3 shpmnt per lane on average

EU:

• kg per shmt // remove
• lb per shmt // remove
• plts per shmt // remove
• cwt kg per shmt // remove

[edit] xls datatype

• currency should be of type currency
• numerical should be of type numerical with no decimals (digits after dot)

[edit] field selector v3

UI idea is to use trees: one on the left (select from) and one on the right (select to)

[edit] unit for weight control

• options: Kg, Lb
• this selector will identify unit of measurement for weight. This will be used for all weight related fields. For example if Kg is set then "CT Kg" field option will not show up.

[edit] KPI value label

Now it generically says "KPI value".

We should name it after actual KPI.

[edit] SOW 71 Change requests

• re-design pop up layout (details will be provided by Kostya to Misha and functional prototype will be demonstrated)

[edit] Cost Per Unit KPI Class

This is aka CPU.

[edit] CPU General Info and List

a. Cost per Lb

b. Cost per Kg

c. Cost per Item

d. Cost per Pallet

e. Cost per Carton

f. Cost per chargeable Weight (air)

g. Cost per container (FCL)

h. Cost per CBM (LCL)

[edit] CPU group A

[edit] Cost per Lb

This is average cost per pound.

Formula: #Cost / #Gross Weight.

[edit] Cost per Kg

This is same as #Cost per Lb but using different unit of measurement.

[edit] CPU group B

[edit] Cost per chargeable weight

• This is for MOT Air only
• Formula: #Cost / (sum of all chargeable weights across all CTs participating)

[edit] Cost per CBM

• Formula: #Cost / (sum of all CBMs across all CTs participating)
• MOT = LCL only

[edit] CPU group C

[edit] Cost per TEU

TBD

• MOT = FCL only

[edit] Cost per Container

'!!! This is on hold

Spec to be rewritten

• OLD Formula: #Cost / (sum of all container counts across all CTs participating)
  • not important what cont type
• MOT= FCL only

[edit] Cost per item

Formula: #Cost / (sum of Qty values from CT commodity table across all CTs participating)

[edit] Cost per Pallet

Formula: #Cost / (sum of all pallet counts across all CTs participating)

[edit] Cost per Carton

Formula: #Cost / (sum of all carton counts across all CTs participating)

[edit] CPU Layout

See #Standard Layout.

Also see #KPI Framework.

[edit] DR3 Phase 1A: EA DOM Portal only

Commented out this spec as outdated.

[edit] Dates KPI Class

[edit] Dates KPI General Info

• Name "Lead times" (aka Dates)
• Measuring average per CT distance in days between known CT dates.

[edit] List of Dates KPIs

See below links to group A, B, C, .... .

[edit] Dates KPI group A

• created to delivered *
• approved to delivered *
• created to approved *
• approved to pick up *
• pick up to departure (air and ocean only)
• departure to arrival (air and ocean only) - moved to group B !!!
• arrival to delivery (air and ocean only)
• pick up to delivery

[edit] Dates KPI group B

see SOW 44

[edit] Dates KPI group C

[edit] Created on Date to Cargo Due Date

• Meaning: Number of days since the date of creation of the shipment to the date of cargo due date
• Show label as: Created to Cargo Due
• Only for ASN users and TMS Client user roles
• formerly SOW 29

[edit] Basic to Super approved

• Add additional lead time date range to measure time for shipment to get approve by double approval process.
• Meaning: This is measuring between a date that basic planner approves shipment and a date that super planner approves shipment
• Show label as: Basic to Super approved
• If there is some shipment that only got approved by super planner, DO NOT consider this shipment. Only consider shipments that have approval activity from both basic planner and super planner
• Only for ASN users and TMS Client user roles
• formerly SOW 31

[edit] Average Transit Times KPI

• Transit in days:
  • Truck: door to door in days
  • Air: airport to airport
  • Ocean: port to port
• Label: Transit Times

• formerly 3966

[edit] Time To Update

• Meaning: number of days between date that is set and date on which this date is set by operator. For these date field:
  • Actual Pick Up
  • Actual Delivery
• If date was updated in advance (before that date) then show KPI value as negative, if on same day then show as zero, if updated after then show as positive
• Show label as:
  • Time to update <date field>
• formerly SOW 30, mant 3752

[edit] Dates KPI group D

[edit] File Closing Metrics

• type: Internal KPI
• name: Arrived to Closed
• meaning: number of days from actual arrival date (delivery for Truck modes) to date of closing (of that CT)
• Add filters to main menu
  • By office (associate CT with office through operator that closed that CT last time)

[edit] Dates KPI Title

Lead Times by Shipper Consignee Carrier Lane.

[edit] Dates KPI Main Menu

• MOT
• specific dates KPI

[edit] Dates View KPI panel

• gives average in days for a YTD for selected dates KPI See SOW 1
• gives change in % (this year over previous) See SOW 1
• arrow indicator (up or down from previous year) See SOW 1
• gage indicator for YTD See SOW 1
• weekly/monthly switch - this actually currently controls type of axis X on Top Right panel

[edit] Dates KPI Left panel

See #KPI Framework

[edit] Dates KPI Top Right panel

See #KPI Framework

[edit] Dates View Bottom Right panel

See #KPI Framework

[edit] Precision for Dates class

Maximum one digit after dot.

[edit] Counters KPI Class

Another name for this KPI class is "Volumes".

IMPORTANT: see also #Association Inclusion and Group logic

[edit] Counters General Info

This is a class of KPIs. Individual KPIs that belong to this class - see below.

Group One (free client KPIs):

• #Shipments Count
• #Total Spend
• #CT Count

Group Two (others):

• Gross KG shipped
• Cartons count
• Pallets Count
• CBMs count
• Containers Count
• Chargeable Weight Count

[edit] Counters group A

[edit] Shipments Count

In case of Trucking MOT with TMS:

• it is a number of loads.

For all other cases/MOTs:

• it is all GRPs plus individual CTs not included in GRPs that were created during given timeframe for given MOT. Use "Created On Date".

See also #Shipment defined.

Note: Created On Date for a group is Created On from CT with lowest CT number in the group.

[edit] CT Count

• it is 'a sum of all CTs that were created during given timeframe for given MOT. Use "Created On Date".

[edit] Total Spend

Trucking for TMS/EADOM case:

• Total charges for all loads for given timeframe (from LoadPlan XML).

All other cases/MOTs:

• take sum of all sales invoices

Additional condition:

• consider only shipment that has Actual Delivery date is set (not empty).

see also Exclude Duty and VAT section

[edit] Main Menu

• Client company
• MOT - Single select including ALL MOTs
  • Default: All MOTs
  • If mode is selected then applies to left panel, top right panel and bottom right panel for a particular mode value
• Specific couters KPI - Shipment count/Gross KG/Cartons count/Pallets count...dropdown menu
  • This specific counter KPI - single select, if it is universal, shows all modes for default value, if it is specific, shows mode specific.

[edit] View Type

• • Main View
  • Shippers View : TBD
  • Consignees View : TBD
  • Carriers View : TBD
  • Lanes View : TBD
• Additional selector
  • Not used for Main View
  • [TBD]

[edit] Layout

Use #Standard Layout similar to Cost per unit KPI

[edit] Counter View Title

Activity KPI by Shipper Consignee Carrier Lane

[edit] KPI Panel

• Label: ie. Total shipments (KPI type of counters- See List of total counters KPI above)
• Number: Actual value of KPI (NOT average value of the last full week/quarter/month)
• Arrow change indicator: Same indicator as other KPIs
• Percentage: comparatively to previous period time frame same as other KPI
• Timeframe: Options: Weekly, Quarterly, Monthly
• Traffic light: Gage. Indicates tolerance level same as other KPIs
• Weekly/Quarter/Monthly switch: Control type of axis X on Top Right panel

[edit] Left Panel

Similar to Cost per Unit KPI but this is reported on selected type of counter KPIs

Include Drill down concept and ability to download to excel

• Types of counters are universal for all MOTs or specific for specific MOT.

• Universal KPI: default "ALL MOTs" and value for all modes (count all modes)
• Specific MOT: default to specific "MOT" (count on a specific mode such as CBMs count is specific for LCL only)

For specific MOTs show specific MOTs value

[edit] Top Right Panel

Similar to Cost per Unit KPI but this is reported on selected type of counter KPIs

All MOTs as selected choice by default

• show graphs for all modes for current year and last year comparison

Specific mode is selected

• show graphs for a specific mode

[edit] Bottom Right Panel

Similar to Cost per Unit KPI but this is reported on selected type of counter KPIs

Include drill down concept with HTML report and ability to download to excel

• This chart is to show for all modes if all modes is selected. Show only a specific mode if specific mode is selected. Current YTD value.

[edit] Apply to panels

Apply to all panels (top right, bottom right, left panel) with KPI and MOT values combination.

[edit] LO and EU KPI classes

[edit] LO class

• name: "Lane Optimization"
• has one KPI #Avg Shipments per Lane - see below

[edit] Avg Shipments per Lane

• label: "Weekly/ Monthly Avg Shipments per Lane"

• definition: This shows how many shipments there are on average per lane per week or month

Weekly: Weekly Average   
Monthly: Month Average   
Quarter: Total Quarter Count - divided by 3 month (average per month)   
Annual: Total annual count - divided by 12 month (average per month)

• hint label:

If "period" is 3 weeks or less, show the "average shipment for this week"   
If "period" is 1 month or more: Show  'average shpt count per month for this period'  

[edit] EU class

• name: "Equipment Utilization"
• This is a KPI measuring quantity, weight in Kg and Lb, pallets, CBM, etc per shipment/ per container for a specific time frame

• group A:
  • #KG per shipment
  • #LB per shipment
  • #Pallets per shipment
  • #CBM per shipment
  • #Chargeable Weight per shipment - added

• group B:
  • #Pallets per container - on hold as well
  • #CBM per container - on hold

• Layout: #Standard Layout

[edit] KG per shipment

average KG per shipment(load for Truck Dom TMS, Shipment for Non-TMS Trucking) for specific time frame. Sum up KGs for all shipments in a specific time frame and divide by number of shipments

[edit] LB per shipment

average LB per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame. Sum up Lbs for all shipments in a specific time frame and divide by number of shipments

[edit] Pallets per shipment

average Pallets per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame.

Sum up number of pallets for all shipments in a specific time frame and divide by number of shipments

[edit] CBM per shipment

average CBM per shipment(load for Truck Dom TMS, master for Non-TMS Trucking) for specific time frame.

Sum up CBM value for all shipments in a specific time frame and divide by number of shipments

[edit] Chargeable Weight per shipment

• Average for Chargeable Weight across participating CTs
• MOT Air only

[edit] Pallets per container

average plts per container for specific time frame. Sum up number of pallets for all number of containers in a specific time frame and divide by number of containers

[edit] CBM per container

!!! Per Marc Keep ON HOLD for International ASN Clients

average CBM per container for specific time frame. Sum up CBM value for all number of containers in a specific time frame and divide by number of containers

[edit] Misc

N/A

[edit] SOWs Part A

[edit] Sequence

• first 3567, 3575 in parallel
• next 0003583
• next 0003595
• all remaining are sequential in order of priorities

[edit] 3567 DR1 Simple Shipment count DR

• Sasha: Java/SQL

Spec: #DR1 Simple Shipment count DR

[edit] 3575 Evaluate LogiXML

• Kostya, Vlad

[edit] 0003583 DR2 Complex Shipment Data per Client per Year DR, PART A

• kostya: SQL, Java b-end
• sasha: b-end, f-end

spec: #DR2 Complex Shipment Data per Client per Year DR, PART A

[edit] 0003595: [DR/KPI] DR3 Phase 1A: EA DOM Portal only

spec: #DR3 Phase 1A: EA DOM Portal only

[edit] SOWs Part B

[edit] SOW 0 DR4 Dates KPI

mantis: 3662

Spec: #DR4 Phase 1A2 Dates View

• Created to Delivered
• Approved to Delivered
• Created to Approved
• Approved to Pick-up
• Pick up to Delivered

[edit] SOW 1 Spec gaps and some changes

mantis: 3668

R1,2 - moved to another SOW

• R3: monthly / weekly switch should also apply to Left Panel (group by weeks / month). If monthly is selected then left panel should show breakdown by month

• R4: for Left panel:
  • arrow indicator should show change from previous week SAME year not previous year

• R5: for KPI panel:
  • arrow and percentage: implement as in original spec (last week/month comparatively to previous week/month)

• R6 (added): for KPI panel:
  • Number should show average cost per unit for last week/month (not YTD)

Description for R4, R5, and R6.

• 1) Left Panel: Show data until latest week/month which includes current week/month.

ie. if today is Thursday July 19 (29 Week). Top left should show 29 weeks for weekly left panel.. See SUMMARY section below.

• 2) KPI Value: <s>Per Example above, current week (29)/month (July) is not a full week/month,

KPI value should show the the last week/month value and percentage of last week/month value compared to previous week/month.

ie. If monthly is selected, shows July data on the left panel as the latest month (current month) and KPI Value shows "Jun KPI value " and percentage "Jun KPI" compared to "May KPI".

ie. If weekly is selected, shows 29 week on the left panel as the latest week (current week) and shows KPI value for last week value "28 week KPI value" and percentage "28 week KPI" compared to "27 week KPI".

Additional changes from above requirements:

Left Panel:

• 1) Show the latest of full week/month on the list (do not show current not full week/month value)

• a. For example, today is Tuesday July 24 (week 30), show last full week/month as the first one on the list (week 29, Week 28, …/Jun, May). Not necessary to show week 30/July as they are NOT a full week/month.

• 2) For Weekly/monthly that has “0” value (no data), do not show arrow change indicator for the week/month that has no data(empty)
• 3) If there is some week period in the middle that has no data (between 2 period of weeks that have data), arrow indicator to show for the week/month that has data compared to the previous last full week that has data

KPI Panel:

• 1) Show the last full week KPI value:

• a. If the last full week value has no data (empty), show “0” as KPI value as well as percentage for “0%” and no need to show arrow change indicator
• b. If the last full week value has data (not empty) and the previous week has no data (empty) then KPI value shows for the last full week value, and % for comparison as well as arrow change indicator to compare between the last full week (not empty) and previous last full week that has data (not empty)

Note: APPLY THESE TO ALL KPIs

Signed off by Marc for these additional changes with more note below:

This is correct. Also on the “timeline linechart” simply skip the weeks where there is no data so the lines will not be continuous from left to right but will only connect weeks that have data.

• Empty week = empty
• Full week = dot
• Empty week = empty

In the above example 3 weeks with 2 weeks (1 and 3) do not have data = only 1 dot on the chart.

• Full Week = dot
• Full Week = dot

The above 2 dot are connected by a line.

Summary of this SOW1 can be found on mantis attachment.

SUMMARY

• 1) Monthly / weekly switch should also apply to Left Panel (group by weeks / month). If monthly is selected then left panel should show breakdown by month.

Left Panel:

• Show the latest of full week/month on the list (do not show current not full week/month value)
• Arrow indicator should show change from previous week SAME year not previous year
• For Weekly/monthly that has “0” value (no data), do not show arrow change indicator for the week/month that has no data(empty)
• If there is some week period in the middle that has no data (between 2 period of weeks that have data), arrow indicator to show for the week/month that has data compared to the previous last full week that has data

• 2) KPI Value:

• Arrow indicator should show change from previous week SAME year not previous year
• Arrow and percentage: implement as in original spec (last week/month comparatively to previous week/month)
• Number should show average cost per unit for last full week/month (not YTD)
• Show the last full week KPI value:
  • If the last full week value has no data (empty), show “0” as KPI value as well as percentage for “0%” and no need to show arrow change indicator
  • If the last full week value has data (not empty) and the previous week has no data (empty) then KPI value shows for the last full week value, and % for comparison as well as arrow change indicator to compare between the last full week (not empty) and previous last full week that has data (not empty)

• 3) Top Right Panel

• Timeline line chart
  • If there is no data, no dot on graph (skip the weeks where there is no data)
  • If there is data, dot on graph and connect it to the last dot on graph

[edit] SOW 2 Look and feel changes

mantis: 3690

• Weekly/monthly button: not clear what indicates "pressed" state

[edit] SOW 3 KPI Admin on Internal and Client

mantis: 3691

spec: #KPI Admin

[edit] SOW 4 (DR3 Cost Per Unit KPI, EA DOM only) Add all other Views

manis: 3692

These views:

• Shippers View
• Consignees View
• Carriers View
• Lanes View

Spec: #DR3 Cost per unit KPI by Shipper Consignee Carrier Lane

[edit] SOW 5 Add Quarters

mantis: 3693

Add option Quarterly to Weekly / Monthly

• Quarterly button is added between Weekly and Monthly
• Left Panel:

• Once Quarterly is selected then quarterly data columns are applied
• Show Quarterly from latest full quarter back to previous quarters (Q2 Apr-Jun 2012, Q1 Jan-Mar 2012, Q4 Oct-Dec 2011, ....)
• Show KPI value per quarter
• Drill down value: see #SOW_10_Left_panel_drill_down

• KPI Panel:

• KPI value: last full quarter value and percentage compared between last full quarter value and previous last full quarter value (not empty: Must have data)

• Top right Panel:

• Axis X: Quarters (Q1, Q2, Q3, and Q4)
• Axis Y:
  • Plot A: KPI for this year
  • Plot B: KPI for last year
  • Plot C: YTD average

• Drill down Left Panel:

• If Q2 Apr-Jun 2012 is selected (one click), then applies chart to the specific quarter value on the bottom right chart.
  • Drill down for a particular bottom right chart, show HTML report information for that particular bar/pie chart with ability for download to excel select output option
• If Q2 Apr-Jun 2012 is selected (double clicks), then download to excel option is available for all data for that particular quarter 2.

[edit] SOW 6 Cost per pound is incorrect for some records

mantis: 3688

Current issue:

• The current KPI module calculates cost KPIs based solely from the TMS load table (tblLoad). This poses an issue for us as the TotalWeight in that table is pulled directly from TMS and for the most part the TotalWeight in that table is incorrect. Some records contain only 1 lb on weight when they first created CT record for Truck itself. Issue is due to a specific business process that the EA Dom team uses for all east coast northbound/southbound full trailer consolidation moves. Weight and piece counts are not known in advance and are usually known the morning after, when trucker, Lawrence Transportation, provides EA Dom with a trailer manifest. This manifest confirms the actual shipment #'s that were loaded into the trailer and its corresponding TMS Load number#.

• These type of records cause report on Cost per lb/kg incorrect as the info we report is not a correct report on weight. The weights get updated later on CyberTrax but it does not send back to TMS. The up to date weights can be seen on CyberTrax.

Strategic Solution:

• Redesign KPI module to calculate cost KPIs by using the TotalCharge in load table and combining/aggregating TotalWeight from internal CT2 tables. TotalCharge is to be used for load or add up allocated charge in the loadplan to be used for load charges.. Allocated charge in the loadplan is to be used for CT record.
• Drill down: Export to excel - Ensure that there should be ability to report cost per lb/kg per load as well as per CT record if the user would like to.

See Allocated charges for each CT on LoadPlan: Allocated Charges for each CT.

[edit] SOW 7 Remove pick up and/delivery trucker switch and display for only Pick up data

mantis: 3719

Business process currently, there are 3 different use cases of CT records in CT2 system with Pick up and Delivery truckers assigned below:

• 1. CT1 - Shipment that has Trucker 1 as a pick up trucker and Trucker 1 as a delivery trucker and charges applies to Trucker 1
• 2. CT2 - Shipment that has Trucker 2 as a pick up trucker and Trucker 2 as a delivery trucker and charges applies to Trucker 2
• 3. CT3 - Shipment that has Trucker 1 as a pick up trucker and Trucker 2 as a delivery trucker and charges applies to Trucker 1

For case 3, even though a shipment has 2 truckers to handle the shipment but the charge is always applied to the pick up trucker only. For the delivery trucker charges, operator has to create a new CT internally in this case could be CT4- shipment to handle the charges for Trucker 2 for CT3. Operator creates CT4 - shipment that has trucker 2 as a pick up trucker as well as deliver trucker and charges applies to Trucker 2

• 4. CT4 - Shipment that has Trucker 2 as a pick up trucker and Trucker 2 as a delivery trucker and charges applies to Trucker 2 (handle charges for CT3 delivery trucker)

Notes: This is only applied for Truck- DOM for TMS only

[edit] SOW 8 For Bottom Right panel add drill down to CT and load level

mantis: 3707

spec: #MV Bottom right panel drill down

[edit] SOW 9 Show all data from KPI Earliest Date

mantis:3695

Everywhere (all panels, all KPIs) use data from CT records created not earlier than #KPI Earliest Date. Based on "Created On Date".

Managed through Admin.

Currently defined as Jan 1, 2011 and hardcoded.

[edit] SOW 10 Left panel drill down

mantis: 3703

Left Panel Drill down ability should be applied and available for all KPIs

spec: #MV Left panel drilldown

[edit] SOW 11 DR4 Dates KPI (On time performance: Truck dom (TMS) only )

Mantis: 3712

• Add additional lead time date range to measure On time performance
• Meaning: How on time between Actual date of delivery and Cargo Due date.
• Goal: To measure how on time performance will be and percentage on time, late, and early. The goal for user to achieve is to get number that is close to or equal to "zero" or "negative" value; means "On time" or "Early" in business performance.

• 1) Average days for on time performance
  • Logic 1: Day(s) = Actual Delivery Date - Due Date
    • Day(s): Negative = Early, Positive = Late, Zero "0" = On time
  • Logic 2: Average of performance for shipping = SUM (Day(s) in a week/quarter/month) / CT Count.
    • Result of KPI value for average days comes from sum up number of days from logic 1 for all CTs within a week/quarter/month then divided by number of CT records within a week/quarter/month.
    • If the day value is positive; means average of performance for shipping is considered late
    • If the day value is negative; means average of performance for shipping is considered early
    • If the day value is equal zero; means average of performance for shipping is considered On time
• 2) Percentage on time/late/early in particular week/quarter/month/YTD current year
  • Logic 3: Percentage of delivery shipment early in particular time frame = Number of CT record(s) that is early / Total number of CT records in particular time frame
  • Logic 4: Percentage of delivery shipment late in particular time frame = Number of CT record(s) that is late / Total number of CT records in particular time frame
  • Logic 5: Percentage of delivery shipment on time in particular time frame = Number of CT record(s) that is on time / Total number of CT records in particular time frame

• MOT: Truck-Dom (TMS)

On average these shipments are delivered before Cargo Due date is considered Early , on the Cargo Due date is considered On time and for shipments that are delivered late or after Cargo Due date is considered Late

[edit] Additional info from Leadtime KPI

• Option: Additional date pairing to measure on time performance

• 2 additional options from drop down list
  • OTIP (Day)
  • OTIP (%) - make it default option for Lead Time KPI.

• Additional tab for CT count to bottom right chart

[edit] Layout

Use #Standard Layout similar to other date KPIs

[edit] KPI Panel

• Label:Average (days)
• Number: Average value of the last full week/quarter/month
• Arrow change indicator: Same indicator as other KPIs
• Percentage: Comparatively to previous period time frame same as other KPIs
• Timeframe: Weekly, Quarterly, Monthly
• Traffic light: Gage. Indicates tolerance level same as other KPIs.
• Weekly/Quarterly/Monthly switch: Control type of axis X on top Right panel

[edit] Left Panel

Similar to other date KPIs. The difference is the value can contain negative value if shipment is delivered early, zero value if shipment is delivered on time, and positive value if shipment is delivered late.

Include Drill down concept and ability to download to excel. Similar to other dates drill down functionality.

[edit] Top Right Panel

Similar to other date KPIs. The differences are:

• OTIP (Day )

• Show average (days) on axis Y, and timeframe on axis X depend upon time frame switch

• OTIP (%)

• Show average % on axis Y, and timeframe on axis X upon time frame switch

[edit] Bottom Right Panel

Similar to other date KPIs with additional tab for CT count as to show percentage of shipments that are delivered early, on time and late. Show YTD current year average value.

Include drill down concept with HTML report and ability to download to excel

[edit] CT count Tab

Axis Y is CT count, Axis X show max 25 value from negative to positive value. Depend on value in the system. Show 25 value on axis X then the first position shows (Less than negative value), the last position shows (More than positive value), the middle position shows (0) value for on time.

Show % on the charts

[edit] Other tabs

Shippers, Consignees, Carriers, Lanes similar to other date KPIs.

[edit] OTIP (Day)

[edit] OTIP (%)

[edit] SOW 12 Elevate 0 value graph in order to see a bar chart for zero value

mantis: 3705

• Zero value chart is not visible (blank) and value cannot be shown while mouse over value

• Elevate 0 value graph in order to see actual bar charts for zero value. Apply for all tabs for zero value
• See figures below:

Solution: elevate zero value to show actual zero value chart below

[edit] SOW 13 DR3 Cost per Unit (cost per LB and cost per KG for all modes)

Mantis: 3795

• These cost per LB and per KG apply to all modes. This is universal for all modes just a conversion

[edit] SOW 14 Add Yearly as well as make button narrower and rename buttons

Mantis: 3737

Add option Yearly to Weekly / Quarterly / Monthly

• Yearly button is added after Monthly
• Rename buttons: Weekly/Quarterly/Monthly/Yearly to Wk/Qtr/Mo/Yr

• Left Panel:
  • Once Yr is selected then yearly data columns are applied
  • Show Yearly from latest full year back to previous years (2011, 2010)
  • Show KPI value per year
  • Drill down value: see #SOW_10_Left_panel_drill_down
• KPI Panel:
  • KPI value: last full year value and percentage compared between last full year value and previous last full year value (not empty: Must have data)
• Top right Panel:
  • Axis X: Years (2011,2010) If current year is 2013 but not a full year then show 2012, 2011, 2010
  • Axis Y:
    • Plot A: KPI for this year
    • Plot B: KPI for last year
    • Plot C: YTD current year average
    • Plot D: YTD last year average
• Drill down Left Panel:
  • If year 2012 is selected (one click), then applies chart to the specific year value on the bottom right chart.
    • Drill down for a particular bottom right chart, show HTML report information for that particular bar/pie chart with ability for download to excel select output option
  • If year 2012 is selected (double clicks), then download to excel option is available for all data for that particular year.

[edit] SOW 15 Change status bar value display

Mantis: 3735

• DisplayActual KPI Value on status bar so that user can see all the value all at once as user can view it quickly compared to use mouse over value one by one to compare one by one.

• No need to show the min, med, max value on the status bar (hide)

• Mouse over value functionality should stay the same as common.

[edit] SOW 16 Connect dot timeline chart

Mantis: 3736

• Design Chart 2
• This is a requirement from SOW 1, but it was not enough time to implement. Need more time to investigate at that time, so this SOW 16 is created for this task separately.

[edit] SOW 19 DR7 Overlap

canceled

[edit] SOW 20 DR3 Cost Per Unit KPI: Extend this KPI to other Client Companies

mantis: 3798

Detailed spec TBD!

[edit] SOW 21 Top Right Panel: Show all years graphs since May 2010 with all YTD average

Mantis: 3708

• Data is displayed from May 2010 for all KPIs
• Graphs: 2012, 2011, 2010, YTD average (2012), YTD average (2011), YTD average (2010)

• Provide ability to easily select years for comparison (2 years at a time)

• Default: Show the current year and last year graphs (2012, YTD 2012 average, 2011, YTD 2011)
• Same year same color of graph but different type of graph to show the different. Current one is good for dot graph with line for a year and dash-line graph for a YTD average.

[edit] SOW 22 Bottom Right Panel: Spell out what value based on

Mantis: 3709

Bottom Right Panel displays bar or pie segment chart for top/bottom display value but there is nothing to describe what those value on the graph based on such as 2012 YTD value or weekly for current year value, or value of all data in the system.

• Display a Frame Label for what value based on to help user understand what data he/she see and compare to.
• A Frame label must be displayed for all KPIs and all views for the bottom right panel

• Main view: such as 28 Week (08-14 Jul 2012, 2th Quarter (Apr-Jun 2012), 2012 YTD Average
• Other views: such as Weekly - 2012, 2012 YTD Average
• A frame label displays depending upon general information or Left Panel drill down information

[edit] SOW 23 (DR3 Cost Per Unit KPI) Extend this KPI to other MOTs (all units)

Mantis: 3799

Extend cost per unit KPI from existing one to other mode of transportation as well as other cost per unit (all units)

• These list of DR KPI is on cost per unit tab additional to cost per Lb.
• Dropdown list for list of cost per unit KPI

• Universal for all MOTs
  • Cost per Lb
  • Cost per Kg
  • Cost per Item
  • Cost per pallet
  • Cost per carton

• Specific MOT(s)
  • Cost per Chargeable Weight - specific for Air only
  • Cost per container - specific for Ocean FCL only
  • Cost per CBM - specific for Ocean LCL and Vendor Console

• All units are added to this KPI and are available for ones that applies to mode of transportation.

• Meaning: Universal list of cost per unit MUST be available for all MOTs selected and some specific one is available for specific mode of transportation.

• It is available when cost per unit is set to be available to specific user and allow user accessibly to my profile (Client Application) for tolerance Gage setting for specific cost per unit.

Truck

• Once this MOT is selected then list of cost per unit that applies to Trucking will be available: Universal list
• Truck Air, Truck Ocean, Truck Dom (TMS), Truck DOM (NON-TMS)
• For the TMS shipments, consider Load shipments - CT level
• For the NON TMS shipments, consider Master shipments - CT level

Air

• Once this MOT is selected then list of cost per unit that applies to Air will be available. In this case universal list will be available as well as cost per chargeable weight

Ocean FCL

• Once this MOT is selected then list of cost per unit that applies to Ocean FCL will be available. In this case universal list will be available as well as cost per container

Ocean LCL

• Once this MOT is selected then list of cost per unit that applies to Ocean LCL will be available. In this case universal list will be available as well as cost per CBM
• This one consider both ocean LCL and Vendor console

Question to Marc: Whether we will look at mode of transportation option to Air Ocean and Truck? or Air Ocean FCL Ocean LCL, Truck (include for both truck dom (TMS) and Truck ocean and Truck air (non-TMS)

Question to Marc: Whether we look at ct level for all TMS and NON-TMS ???

• Main View and other views.

[edit] Layout and Cost per unit view title

Use #Standard Layout same as Cost per unit KPI with drop-down list of cost per unit

[edit] KPI Panel

• Label: Cost per Lb, Cost per Kg,... etc depend on what cost per unit is selected
• Number: Average value of cost per unit of the last full week/quarter/month and depend on what cost per unit is selected.
• Arrow change indicator: Same indicator as other KPIs
• Percentage: comparatively to previous period time frame same as other KPIs
• Timeframe: Options: Weekly, Quarterly, Monthly
• Traffic light: Gage. Indicates tolerance level same as other KPIs

[edit] Logic

Cost per Lb:

Cost per Kg:

Cost per Item:

Cost per Pallets:

Cost per Carton:

Cost per Chargeable Weight:

Cost per CBM:

Cost per container:

[edit] Left Panel

Similar to cost per Lb but this is reported on what unit is selected to show average result

Include drill down concept and ability to download to excel

[edit] Top Right and Bottom right panel

Similar to cost per Lb but this is reported on selected type of cost per unit to show average result

Include drill down concept with HTML report and ability to download to excel

[edit] Apply to panel

Similar to cost per Lb

[edit] Main View

See an example mock up for cost per Kg below. Other unit has similar mock up only report on different of unit for cost

[edit] SOW 24 (DR4 Lead Time KPI) Extend this KPI to other MOTs (all units)

mantis: 3800

• Air
• Ocean

• LCL/FCL/Vendor Console

• Truck

• Truck-Air
• Truck-Ocean

Detailed spec TBD!

[edit] SOW 25 (DR5 Total counter KPI) Extend this KPI to other MOTs (all units)

mantis: 3801

• Air
• Ocean

• LCL/FCL/Vendor Console

• Truck

• Truck-Air
• Truck-Ocean

Detailed spec TBD!

[edit] SOW 26 DR5 Total counter KPI : Extend this KPI to other views

Mantis: 3802

• Shippers View
• Consignee View
• Carriers View
• Lanes View

[edit] SOW 27 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other MOTs (all units)

mantis: 3728

• Air
• Ocean

• LCL/FCL/Vendor Console

• Truck

• Truck-Air
• Truck-Ocean

Detailed spec TBD!

[edit] SOW 28 (DR6 Lane Optimization Equipment Utilization) Extend this KPI to other views

Mantis: 3803

[edit] SOW 29 (DR4 Lead Time KPI) Add additional KPIs to measure cargo due date

Mantis: 3741

outdated mantis, moved to another SOW

[edit] SOW 30 (DR4 Lead Time KPI) who and when - Updates and Time to update Actual Departure date and Actual delivery date KPIs

Mantis: 3752

Provide ability to track and report on specific CT2 updates and events. Driving factor is that clients want the ability to track the efficiency of their operations team and how up-to-date their system records really are.

Example: Operators enter in an Actual Delivery date of 8/10/2012 for a shipment. However, they waited 2 weeks to make that update on 8/24/2012. So between 8/10 and 8/24, the system data was stale and invalid.

This specific request is to be able to track and report on when and who updates actual departure dates and actual delivery dates

There are 2 KPIs to be added to the LeadTime class for Trucking only.

1) Updates

• Number of shipments that are updated by:

• TMS (trucker)
• Jaguar operator

• Categorize by shippers, consignees, carriers, and lanes
• Option:

• Report on "Actual Pick up date"
• Report on "Actual Delivery date"

[edit] KPI Panel

• Label: CT records
• Number: average value of KPI of CT records (that have Actual picked up date or actual delivery date is set) the last full week/quarter/month)
• Arrow change indicator: Same indicator as other KPIs
• Percentage: comparatively to previous period time frame same as other KPI
• Timeframe: Options: Weekly, Quarterly, Monthly
• Traffic light: Gage. Indicates tolerance level same as other KPIs
• Weekly/Quarter/Monthly switch: Control type of axis X on Top Right panel

[edit] Logic

• Count (CTs in the system for each specific time frame that have at least actual picked up or actual delivery date not empty)
• count(CTs that have Actual Picked up date field that was updated by Jaguar)
• count(CTs that have Actual Picked up date field that was updated by TMS)
• count(CTs that have Actual Delivery date field that was updated by Jaguar)
• count(CTs that have Actual Delivery date field that was updated by TMS)

[edit] Left Panel

Similar to other date KPI but this is reported on average CT records in the system that have:

• At least Actual Picked Up or Actual delivery date is set.
• Weekly, Quarterly, Yearly

Include Drill down concept (one click) and ability to download to excel (double click)

[edit] Top Right Panel

Report on CT records count for Actual Picked up Date or Actual Delivery date (option radio button: report for specific date) that were updated by Jaguar and TMS for weekly/quarterly/monthly of current year only (2012)

• Default radio button: Actual Delivery Date

• Graph should show line chart comparison Actual delivery date value that were updated between TMS and Jaguar operator
• If Actual Picked up date radio button is selected then show graph comparison for actual picked up date value that were updated between TMS and Jaguar operator

[edit] Bottom Right Panel

Report on CT records Count for YTD current year for CT records in the system that have at least actual picked up date or actual delivery date field set and display stack-chart comparison between records that were update by TMS and Jaguar operator

Include drill down concept with HTML report and ability to download to excel

NOTE: If the left panel is selected for a specific week/month/year then apply the bottom right panel to a specific value per shipper, consignees, carriers, lanes for a specific week/month/year. Ability to report and download to excel for a specific shipper/consignee/carrier/lane for that specific week is available as well. Common to all KPIs for drill down and download to excel functionality.

[edit] Apply to panels

Option for date selected for actual pick up and actual delivery dates - apply graph on the Top right panel

Drill down - apply to bottom right panels

[edit] Updates

2) Time to update

• Number of days for the actual date to take for the field to be updated (actual date to date to update)

• a) Measure the actual picked up date and the actual date that this field is updated for current year, last year, YTD current year average, YTD previous year average
• b) Measure the actual delivery date and the actual date that this field is updated for current year, last year, YTD current year average, YTD previous year average
• c) Measure both dates average for the actual picked up and delivery dates that that 2 fields are updated for current year, last year, YTD current year average, YTD previous year average (All)

• Categorize by shippers, consignees, carriers, and lanes
• Option:

• Report on "Actual Pick up date"
• Report on "Actual Delivery date"

[edit] KPI Panel

• Label: Average (days)
• Number: average days of KPI of combination of time to update for picked up date and actual delivery date OR average days of KPI of time to update Actual picked up date OR average days of KPI of time to update Actual delivery date for the last full week/quarter/month. Depend upon radio button on the right panel.

• Default value: average value of KPI of days for ALL (both actual pick up and delivery dates) in the system

• Arrow change indicator: Same indicator as other KPIs
• Percentage: comparatively to previous period time frame same as other KPI
• Timeframe: Options: Weekly, Quarterly, Monthly
• Traffic light: Gage. Indicates tolerance level same as other KPIs
• Weekly/Quarter/Monthly switch: Control type of axis X on Top Right panel

[edit] Logic

• Time to update (days) = Actual Picked up date is set on date by Jaguar - Actual Picked up date value
• Time to update (days) = Actual Picked up date is set on date by TMS - Actual Picked up date value
• Time to update (days) = Actual Delivery date is set on date by Jaguar - Actual Delivery date value
• Time to update (days) = Actual Delivery date is set on date by TMS - Actual Delivery date value

[edit] Left Panel

Similar to other date KPI but this is reported on average days of date pairing select based on. If Actual Delivery date is selected, so Left panel show value for average days of how long it takes for actually delivery date to be set compared to the actual delivery date value for particular week/quarter/month. Left panel data depends upon the radio button on the right panel.

Option: All (default), Actual Picked up date, and Actual Delivery date

Each specific time frame considers the records that have at least Actual Picked Up or Actual delivery date is set.

• Weekly, Quarterly, Yearly

Include Drill down concept (one click) and ability to download to excel (double click)

[edit] Top Right Panel

Report on average days of Actual Picked up Date, Actual Delivery date OR both the combination of Actual Picked up and Actual Delivery dates for weekly/quarterly/monthly of current year only , previous year, YTD current year, and YTD previous year across the system depend upon option date selected. (option radio button: report for a specific date or ALL)

• Default radio button: All

• Graph should show line chart comparison for current year, previous year, YTD current year average, and YTD previous year average KPI value for specific timeframe.
• If Actual Picked up date radio button is selected then show graph comparison average time to update Actual Picked up date value for current year, previous year, YTD current year average, and YTD previous year average

[edit] Bottom Right Panel

Report on average days of YTD current year for actual picked up date or/and actual delivery date across the system depend upon radio button selected from the top right panel

This chart was driven by the radio button on the right panel for Actual Picked up date, Actual Delivery date or ALL. If Actual picked up date was selected then the chart shows YTD current year average days data for Actual Picked up date with stack-chart to separate days for records that were updated by TMS and Jaguar operator.

Include drill down concept with HTML report and ability to download to excel

NOTE: If the left panel is selected for a specific week/month/year then apply the bottom right panel to a specific value per shipper, consignees, carriers, lanes for a specific week/month/year. Ability to report and download to excel for a specific shipper/consignee/carrier/lane for that specific week is available as well. Common to all KPIs for drill down and download to excel functionality.

[edit] Apply to panels

Radio button option for date(s) and ALL selected - apply to all :graphs on the Top right panel, left panel data, and bottom right data

Drill down from left panel - apply to bottom right panels

[edit] Time to update

[edit] SOW 31 (DR4 Lead Time KPI) - how long it takes between Basic planner and Super planner to approve

Mantis: 3765

• Add additional lead time date range to measure time for shipment to get approve by double approval process.
• Meaning: This is measuring between a date that basic planner approves shipment and a date that super planner approves shipment
• Label: Basic to Super approved
• MOT: Truck-Dom shipments that were approved via double approval process only and only consider for the shipments that have both basic planner and super planner approval dates.

Logic:

• Number of days since basic planner approved to super planner approved shipment.

• For example, basic planner approved CT 11111 on Sept 10 and super planner approved this same CT on Sept 12, that means it took 2 days for super planner to approve shipment once basic planner approved.
• Average number of days to take for shipment to get final approve for a specific time frame
  • If there is some shipment that only got approved by super planner, DO NOT consider this shipment. Only consider shipments that have approval activity from both basic planner and super planner.

• KPI value, functionality, time frame, drill down, Left panel, Top and Bottom right panels are similar to other date pairing KPIs.

[edit] SOW 33 Add Year Selector Panel to the top right panel

Mantis: 3785

Spec: #Year Selector Panel

[edit] SOW 34 RFC: change the chart color for bar chart to be one color instead of multicolor

Mantis: 3814

• Blue color would be good. One color is best for bar chart as multicolor do not have meaning associated with it.
• For pie chart, multicolor makes sense. So leave multi-color for pie chart only
• Apply this requirement for all KPIs that have similar display

[edit] SOW 35 Lead Time: The vertical lable of top right panel shows "Cost per LB, USD" --> change to "Average Lead Times, in Days"

Mantis: 3840

• “Lead times tab” the Vertical Label of the top/main line graph chart reads “Cost per LB, USD” this should be changed to “Average Lead Times, in Days”

[edit] SOW 36 RFC: Cost and Lead time KPI - decimal points

Mantis: 3841

• Change Cost KPI to show 2 decimal point on left panel.
• Change Lead time KPI to show 1 decimal point on left panel.

[edit] SOW 37 RFC: Bar's width of top/bottom 5

Mantis: 3842

• Change the width of the columns for "top/Bottom 5" to be a bit bigger that the size as "top/Bottom 10" but smaller than the current one and space them out. Can we make it in between the sizes? 3/4 of the original size?
• Currently it is too wide, so it would look nicer to keep it thinner and space them out.

[edit] SOWs part C

By Alex after he took over from Tira

[edit] SOW 38 Define and implement user data visibility for KPIs

mantis: 3971

spec: #Visibility

[edit] SOW 17 Counters:group A

Mantis: 3964, 3978, 3979

Spec: #Counters group A

Kostya: SQL

Misha: Drilldowns and Admin

Sasha: Front End

[edit] SOW 39 Dates KPI:group A

Mantis: 3980, 3981, 3982

Review existing KPIs in this group and make them work as re-defined in #KPI Framework - main changes are visibility and use with all MOTs.

Spec: #Dates KPI group A

Kostya: SQL

Misha: Drilldowns and Admin

Sasha: Front End

[edit] SOW 40 CPU KPI:group A

Mantis: 3983, 3984, 3985

Review existing KPIs in this group and make them work as re-defined in #KPI Framework - main changes are visibility and use with all MOTs.

Spec: #CPU group A

Kostya: SQL and Admin

Misha: Drilldowns

Sasha: Front End

[edit] SOW 41 Pull weight when calculating KPIs always from CT2 DB

Mantis: 0003987

Spec:

Currently we pull weight in some cases from TMS we need to change that to CT2 DB as this info is more correct (operators will update some records that have incorrect weight coming from TMS).

[edit] SOW 32 Add PO Issue By filter to main menu

Mantis: 3767

• Add PO Issue By filter to main menu
• need config option on User Admin

[edit] SOW 43 Counters KPI:group B

mant: 3988

spec:

List:

• Gross KG // all
• Containers count // FCL
• CBM count // LCL
• Chargeable Weight count // Air

[edit] Gross KG

• Grand total of Gross KGs for all shipments for specific time frame.
  • Gross KG data mapping: CT>TableA.GrandTotal.Ttl GW Commodity#Grand_Total:_Gross_Kg

[edit] Package count

(per Marc this is on hold!)

• This counts number of cartons; consider sum(AMS) cartons for all CTs for each specific time frame.

• Mapping: ContTab.Table A. Grand Total AMS

AMS for shipment: (Loose pkgs + Ttl pkgs on plts)

[edit] Pallets Count

(per Marc this is on hold!)

• Count number of actual # of pallets for a specific time frame. SUM(Ttl #Plts).

• Mapping:ContTab.TableA.Grandtotal.Ttl # of plts Commodity#Grand_Total:_Plts

[edit] Containers Count

• total TEU value for shipment
  • include only assigned containers
• label: TEU
• MOTs: FCL only
• in drilldown:
  • show column with total TEU value
  • show additional columns - one for each type / size combinations with respective counts.
  • see mock up below

[edit] CBMs count

• MOT: LCL only

• Mapping: CT> ContTab.TableB.Total(in cbm) Commodity#Grand_Total:_cbm for LCL only. SUM(CBM)

[edit] Chargeable Weights count

• This counter is for Air specific. This is available only when Air is selected as MOT

• Mapping: CT> ExpTab.17b: Chargeable weight CT#Chargeable_Weight SUM(Chargeable Weight)

[edit] Drilldowns and XLS for Counters KPI group B

• Apply this: #Drilldowns v3

How to display KPI value:

• Out of counters above the following KPIs are "non-standard":
  • CBM count, Containers count, CWT
    • only per CT value is available in DB, not per commodity line so display total on first commodity line, the rest are zeros

• for all others display per commodity item
  •  ! make sure total per column is equal total on html for segment that user clicked on

[edit] SOW 44 Dates KPI:group B

Mantis:

Spec:

[edit] On time performance

1/ Definition: Actual Delivery date - Cargo Due date; Difference in days

2/ label: On Time Performance

3/ MOT: all

4/ those KPI should be visible for current Client Company in filters if this Client Company is used in ASN Portal

[edit] Negatives on Left Panel

Marc suggested to put "0" in the middle of scale in this case.

Plot in a standard way as in Cartesian system of coordinates.

[edit] Departure to Arrival

1/ Definition: Difference in days between Actual Departure and Actual Arrival Date

2/ label: Departure to Arrival

3/ MOT: air and ocean only

[edit] Approved on Date to Cargo Due Date

• Meaning: Number of days since the date of the shipment gets approval to the date of cargo due date
• Show label as: Approved to Cargo Due
• those KPI should be visible for current Client Company in filters if this Client Company is used in ASN Portal
• formerly SOW 29

[edit] SOW 44 Changes

• Change the LABEL to "On Time Performance" instead of OTIP
• formula is clarified - see spec above (what dat is subtract from what indicating when output negative vs positive values)

[edit] SOW 45 CPU KPI:group B

Mantis:

Spec: #CPU group B

Kostya: SQL and Admin

Misha: Drilldowns

Sasha: Front End

[edit] SOW 42 Implement Overlap KPIs

Mantis:

Spec: TBD

Kostya: SQL and Admin

Misha: Drilldowns

Sasha: Front End

[edit] SOW 18 LO and EU classes

Mantis:

Spec: #LO and EU KPI classes

Kostya: SQL and Admin

Misha: Drilldowns

Sasha: Front End

[edit] SOW 46 Dates KPI group C

combined with SOW 44

[edit] SOW 47 CPU group C

combined with SOW 45

[edit] SOW 48 File Closing Metrics

Mantis: 4043

Add 2 KPIS:

• #File Closing Metrics
• #Time_To_Update

[edit] SOW 49 Notifications for Traffic light

mant: 4012

spec: see new section that has been added - #Notifications for Traffic light

[edit] SOW 50 Enable KPIs for internal users

mantis: 4009

spec: below

• KPIs for Internal users are almost the same as in Client UI with some differences:
  • The Internal KPIs located in separate section of Internal App (click "Flashlight" link on the top of CT2 Homepage)
  • "Total Spend" for the Internal KPIs is based on Jaguar Purchases (use PI amounts) and not filtered by "Bill To" or "Charge Codes"
• Settings:
  • Internal KPIs are available for all Internal user roles (basic ops, super ops, basic accounting, super accounting, management & super users)
  • User can select needed KPIs for viewing on "Dashboard Configuration" tab in User Profile section of CT2 Admin
  • New KPIs (only for internal):
    • Total Sales (Client Costs). It is based on information from Sales Invoices (or Est tab if CT has no invoices).
    • Margin. Sales (Client Costs) minus Purchases (Jaguar Costs).
• Look and Feel:
  • Transport Mode filter remains on top level (as on Client's Flashlight)
  • "Client Company (E0)" moved to Additional Filters and provides multi-select like other filters:
    • Client Company
    • Shipper
    • Consignee
    • From/To Region
    • From/To Country
    • SKU
  • Differences from Client KPIs (Right-Bottom part)
    • Added distribution of KPI per Client Companies in "Client Companies" tab
    • Added ability to display Sales/Purchase/Margin distribution (available in "Needle Movers" mode)
      • Every column has its own floating summary (Sales/Purchase/Margin) pop-up window
    • Single click on a Bottom Right Panel updates Top Right Panel
      • Selected column becomes grey-coloured
    • Drilldown from right-bottom part is called via right-click mouse button
    • Added Zoom feature (with mouse-drag selection)

[edit] SOW 51 KPI Admin v2

[edit] KPI Admin

mant: 4051

spec: see #KPI Admin v2

[edit] SOW 53 Association and Exclusion logic v2

mant: 4072

spec: below

This is a version 2 of a logic that we did not get completely right first time due to the bugs, spec bugs, change requests.

Please apply to all existing and all future KPIs.

[edit] Association between week and shipment

For every KPI we need to decide what criteria to use to associate shipment with a particular week/month/Quarter/Year.

Same association criteria for all classes of KPIs now:

Actual arrival date (air/ocean)  
or 
Actual Delivery Date (all truck modes)

See also #SOW 57 Association logic for TMS shipments

[edit] Association in case of groups

If Shipment is a group or load - see below.

All dates below for CTs in the group/load should be defined

Actual arrival date (air/ocean)  
or 
Actual Delivery Date (all truck modes)

Specific week/month/quarter/year will be associated with earliest date above among CTs in the group.

[edit] Exclusion logic

[edit] Exclusion for Arrived and Delivered

see association logic

[edit] Exclusion if shipment is deleted or alike

Exclude CTs that are:

• canceled or
• deleted or
• rejected (from ASN)

NOTE: do not exclude transmited to 3PL.

[edit] Exclusion if parameter in KPI formula is undefined

Examples:

• If KPI is a time frame then exclude shipments that have only one out of two dates/times that define the interval present in the system.

• In CPUs if cost is undefined or unit is undefined

[edit] Exclusion if need to divide by zero

[edit] SOW 55

[edit] Bugs and Changes from UAT of March 7 and 8

NOTE: Marc/Alex tested with E0=Elizabeth Arden on dev4.

Format: Issue# Summary // dev bug OR spec bug OR change // <status>.

• 1 For some reason xls says "TimeFrame: 2013 YTD Average" (instead of weekly timeframe) for Left panel drilldowns for one week // dev bug // dev, qa - done

• 2 Add "Cost per Lb" as an output column option (for download to xls) // change // dev, qa - done

• 3 Left panel weekly number is different from same number on drill down xls // dev bug // dev, qa - done
  • (example: Pick Up to Delivered)
  • example: total spend ocean

• 4 Shipment Group column contain strange info: decimals instead of integer // dev bug // dev - done, qa - pending

• 5 Each drill down xls should contain KPI itself as an output (as a column and as a total) // change request // dev - done, qa - pending
  • example: KPI name: Created to Delivered

• 6 Cost per Kg KPI calculating incorrectly on xls (per CT) // dev bug // dev, qa - done

• 7 Remove KPI title from HTML // change request // dev, qa - done

• 8 Mapping of Ship to Location needs to be changed for AIR / OCEAN (air / ocean – Consignee SHOULD BE CONSIGNEE and/or Import Tab “delivery location” … not the “export pick up deliver to address”…
  • see also "0004014: [ASN 20] change mapping for "Ship To Location" in Air/Ocean records" // change // postponed till next Sprint

• 9 do not show CTs that have only one date out of two in the interval // spec bug // dev- done, in qa

• 10 = 5 (duplicate) always show KPI itself as a column on download to xls

• 11 Default value for “downloads” should always be: // change // dev,qa - done
  • CT #
  • Shipper
  • PO
  • SKU
  • QTY 

• 12 Include DUTY/VAT or Exclude DUTY/VAT option is not visible on screen (SPEC IS MISSING? LOW PRIORITY?) // postponed till next release

• 13 The labels for the “bottom right” pannel should be vertical // change // dev, qa - done

• 14 cost per pallet // dev in progress
  • calculations are incorrect on xls // dev bug
  • exclude CTs with plt=0 values // spec bug? or change request?

• 15 output columns selection to be replaced with drag and drop version // change // planned for next sprint

• 16 we found reports that include canceled or deleted CTs // spec bug // not done

[edit] SOW 56 Carrier defined

For Bottom Right Panel please use the following logic.

• For Air associate with Airline
• For Ocean associate with Steamshipline
• For all All Truck modes associate with only Pick Up trucker only for now

[edit] SOW 57 Association logic for TMS shipments

In case of TMS associate CTs and timeframes (Weeks/month/quaters/years) based on this date:

• Actual Delivery Date

This will ensure that all CTs associated with a load will be part of same timeframe. (per Marc/Stacy)

[edit] SOW 59 Round total spend and CPU values to 2 values after dot

mantis: 4061

See #Rounding.

[edit] SOW 60 Split MOT Ocean options into FCL and LCL

Mantis: 0004066

spec:

In KPI main menu we have one option for all Ocean shipments.

Marc changed his mind and would like to have two options instead now:

• Ocean FCL (map fromFCL(AW) and FCL(MLB) internal options)
• Ocean LCL (map from LCL and Client Consol internal options)

So we need to associate shipments with those two separately.

[edit] SOW 61 Changes to main menu values on one tab/KPI class should propagate to other tabs

mantis: 0004060

spec: below

Changes to some Main Menu values on one tab/KPI class should propagate to other tabs. Relevant to these two parameters:

• Client Company
• MOT

For example if you are on "Counters" tab and change MOT to Air then if you click on Lead Times tab you should see MOT changed to Air.

Comment from developer: not as easy as we might think on a development side. But not too bad either.

[edit] SOW 62 TMS charges should populate Est tab

mant: 4062

moved to Estimation#SOW_2_TMS_charges_should_populate_Est_tab_automatically

[edit] SOW 63 Inclusion logic in case of transmit to 3PLs

mant: 4089

spec: draft below

Core requirement:

• we want these CTs to be avail in KPIs only for some group of users

Solution:

• always use separate E0 for Transmit to option
• associate user with client group
• add this E0 to the group to make Transmit to CTs available in KPI reports under this E0

Questions:

• I assume no programming required for this

[edit] SOW 65 Validation for Actual Delivery Date

mant: 4118

spec:

Create additional validation for Actual Delivery date:

System can not set / save Actual Delivery Date 
without Estimated Delivery Date being defined

Mapping:

• CT#Actual Delivery Date
• CT#Estimated Delivery Date

For all MOTs.

Special Case:

If this is empty when Actual Delivery Date is updated from external source such as TMS etc then update both with Actual.

Added:

Automatically fill est date when enter actual date:

IF estimated date is EMPTY then copy actual date into estimated field

[edit] SOW 66 Change logic for KPI Earliest Date

mant: 4102

spec:

Base KPI Earliest Date on the same date logic that we use for association btw CT and week:

Actual arrival date (air/ocean)  
or 
Actual Delivery Date (all truck modes)

NOTE: Old logic is here #KPI Earliest Date

[edit] SOW 67 Replace Created On with Notification Date for all Dates KPI

mant: 4096

spec:

1/ Business reason:

In some cases operators create CT after it was departed or delivered. This creates problem on reporting side with such dates KPIs as:

• created to approved
• created to delivered

2/ If we use notification date instead of created on date we can fix this by editing the date on internal.

3/ Apply this change to 2 existing dates KPI:

• created to approved
• created to delivered

END OF WIKI

[edit] Cost related SOWs

[edit] SOW 52 Cost v2

mant: 4071

spec: below

• New formula is the following:

If CT is closed

AND there is at least one inv linked to E0 [this line was added to old logic and to address case with TMS when most of the CTs will not have invoices OR any CT that are closed without having any invoices]

then show Actual Cost

else show Estimated Cost

NOTE:

• Actual Cost = Sum of All Sales invoices minus APPROVED Credit Notes
• Estimated Cost = Total of Sell Charges table in Estimation module.

• (new!) Show only charges "linked to E0" through Client Company to Bill To link. See SOW 68 below.

[edit] SOW 68 Client Company to Bill To link

mant: 4078

moved to Client_Company#SOW_3_Client_Company_to_Bill_To_link

[edit] SOW 58 Add Duty and VAT switch

mantis: 4074

spec:

1/ We need to provide option to exclude all VAT/duty related charges that we apply:

• through charge codes
  • VAT = charge code 1204
  • Customs Duties = charge code 1201

2/ This should be applied to all cost related KPIs. Currently:

• Total Spend KPI from counters KPI Class
• CPU

3/ UI:

• drop down with options: "Exclude VAT/Duty; Include VAT/Duty"
• make it a part of KPIs Main Menu
• default is "Exclude..."

4/ user selection should be stored for next time when KPI will be opened

5/ (added) In case of TMS must work the same way

[edit] SOW 64 Inclusion logic based on CC number

mant: 4090

spec:

0/ Business reason: CTs should not be included into KPI if it does not contain certain "core" charges. In some cases Jaguar plays "minor" role.

1/ If at least one cc from the cc list defined for particular MOT is present in TOC as defined in Inclusion CC Table (see example below) then include this CT from KPI report.

2/ Inclusion CC Table example:

MOT               cc#
----------------------------
AIR               1101 
OCEAN FCL         1301 
OCEAN LCL         1401
TRUCK             1501, 1503, 1505, 1598

3/ This is for CPU class only

[edit] SOW 69 Validation for Origin and Destination country

mant: 4120

spec:

At the moment Actual Departure is set and ready to be saved system should require that Origin and Destination country fields are set.

MOTs/mapping:

• ocean LCL/Client Consol: 11c. ATD:
• air: 1c. ATD - Actual Time of Dept:
• ocean FCL: 11c. ATD:

Added:

[edit] In case of grouped CTs system should validate for all CTs

Ask user to ENTER origin/dest countries and process GROUP SAVE (copy this value to all records).

[edit] Closed group option

added later!

If act dep date is set for at least one shipment in the group then any operations for a group is prohibited:

block edit/del group

[edit] Case when creating a group from some CT with Dep date and other without

In this case warning:

Please clear Dep date on all CTs or set all Orig/Dest countries.

[edit] if we set act dep date in one groupped CT then we have to force to set Orig and Dest country for all CTs in group

This is in only for CTs that have empty Origin / Dest country.

[edit] SOW 70 KPI Menu v2

mant: 4126

spec:

Tweak layout:

• narrow the left panel by about 25%

Remove:

• remove "CT Home, Watch List, ..." menu
• remove Main View dropdown

Introduce this hierarchy:

• Top level controls (selection applies to all KPIs)
  • Client Company
  • MOT
  • Sh#Geography Lane
  • Time period (weekly, monthly, ...)

• KPI class level controls
  • select KPI class

• KPI specific level controls
  • KPI name
  • KPI specific parameters (Ex: Duty/VAT control)
  • Settings button
  • "Number/Trend/%/Gage"

[edit] SOW 71 Drilldowns v3

mant: 4128

spec: see #Drilldowns v3

[edit] SOW 72 Import old Load records

mant: 4125

spec:

We need these data for period since we have started get loads from TMS till July of 2011. This is due to the fact that originally we did not have a code in place to copy all info into CT2 DB.

Descartes is to provide this data as an xls.

[edit] SOW 73 Validation to ensure that containers are assigned

mant: 4143

spec:

Actual Departure Date can not be assigned if there are unassigned containers.

MOTs:

• Ocean, all submodes
• Truck Ocean

Mapping: Sh#Actual Departure Date.

[edit] SOW 74 CPU group C

mant:

spec:

See #CPU group C

[edit] SOW 75 Needle Movers feature

mant: 4152

spec:

Changes are needed for bottom right panel:

• add "Needle movers option"
• if selected show another plot over existing panel that displays Total spend in USD
• sort values left to right from highest Total Spend to lowest

As implemented - see below:

[edit] sow 75 change requests

round 1:

• add "show 25" option
• add average as dotted line

round2:

• remove Avg Total Spend line
• make dotted line lighter

[edit] SOW 76 Change colors for Gages

mant: 0004155

spec:

Low Med High now is expressed as Green - Yellow - Red.

Change to: Light Yellow - Green - Light Red. (to be confirmed)

[edit] SOW 77 Layout and Graphics changes

mant: 4157

spec:

• make buttons more rounded
• make buttons colored (blue as in "weekly")
• move Settings to Main Menu bar

[edit] SOW 78 Change layout for No data to display case

mant: 4158

spec:

Change layout for No data to display case: replace all panels by a blank page and print "No data to display" in the center.

[edit] SOW 79

[edit] Change Rounding logic

mant: 4138

spec: #Rounding section is completely re-defined.

[edit] SOW 80

[edit] Redefine Top Right panel in case WMQY control is Yearly

mantis: 0004192

related section: #WMQY selector

spec:

For WMQY control option = "Yearly" change this on Top Right Panel:

• remove horizontal dotted average lines for years
• remove "YTD Average" label on the right
• in dot associated pop-ups remove "YTD Average words"

[edit] SOW 81

[edit] Enable single click on a Bottom Right Panel to update Top Right Panel

mantis: 0004193

related section: #Top Right Panel

spec:

Changes:

• make drilldowns available on dbl click instead of single (if user clicks on a Bar)
• on single click instead re-draw Top Right Panel - it will show KPI plot only of that value (say Shipper X)

[edit] SOW 82

[edit] Logic and labels for Bottom panel depending on values of WMQY selector

mantis: 4197

related section: #WQMY selector

spec:

Changes for Bottom Panel Label:

- if no specific time period on the Top Panel is selected then:

   for options: W, M, Q: display YTD <current year> 
   for options: Y: display Year <last full year> 

- else:

   display extended description of selected Time Period

[edit] SOW 83

[edit] Average line for bottom chart in non Volumes KPI redefined

mantis: 0004198

related section:

spec:

Average line for bottom chart in Line Optimization KPI: it should be equal to value of selected time period at the top chart.

Should be in the all KPIs except Volumes KPI.

[edit] SOW 84

[edit] Add Avg to some KPI prefixes

mantis: 4201

related section:

spec:

• Add Avg to all KPI prefixes but Volumes KPIs
• do it everywhere: UI, xls, etc

[edit] SOW 85

[edit] Add more global filters: SKU, Shipper, Consignee, PO Issued By

mantis: 4223

related section:

spec:

• Add to Main Menu new filters:

SKU, 
Shipper,  
Consignee, 
PO Issued By

(will be applied to all KPIs)

Shipper, Consignee:

• combobox
• select from lists in Address Book

SKU:

• text field
• if SKU# is present in shipment then include that shipment

PO Issued By:

• combobox
• show options associated with planner role
• if not planner do not show this filter

[edit] SOW 86

[edit] Add additional field options to drilldowns

mantis: 4222

related section:

spec:

Add additional field options to drill downs:

-	Pallets
-	Loose
-	CBM (for LCL)
-	CWT (for AIR)

[edit] SOW 87 Cost per CBM

mantis: 4288

related section:

spec: #Cost per CBM

[edit] SOW 88 Cost per Chargeable weight

mantis: 4289

related section:

spec: #Cost per chargeable weight

[edit] SOW 89 Cost per TEU

mantis: 4290

related section:

spec: #Cost per TEU

[edit] SOW 90 (DR4 Lead Time KPI) - Departure to Delivery

Mantis: 4522

• Add additional lead time date range to measure time for shipment to get number of days since shipment departed to shipment delivered.
• Meaning: This is measuring between actual date of departure of shipment and actual date of shipment's delivery.
• Label: Departed to Delivered
• MOT: For any Truck shipments use Actual Pick up Date as date of departure. For all other MOTs use Actual Departure Date.
• KPI value, functionality, time frame, drill down, Left panel, Top and Bottom right panels are similar to other date pairing KPIs.