Kpi alex

From UG

Jump to: navigation, search


Contents

SOW 1

Info

  • mantis parent: 0003545: [DR/KPI DR KPI Framework Phase I (SOW 1) ..... <parent mantis for this phase>]
  • mantis category: DR/KPI:ph1


Business Requirements

  • Optimize KPIs performance
  • Deliver first KPI panels, overlap multiple KPIs chart, interaction result based on filter(s), and download to excel ability
  • For overlap multiple KPIs in one chart if it is possible then implement it. If not, we can show KPIs result in separated charts to show side by side in order to help user for analysis.
  • Build KPI Dashboard first phase to be considered to future phases for flexibility
  • Using tool from vendor evaluation to produce KPI results: Jreport, LogiXML, or Prognos.
    • Decision will be made for which vendor we will select upon feedback from everyone in the team. Jreport is number one at this point in term of pricing. So please evaluate this asap.
  • Data set (fields) is provided for generating KPIs
    • Please see spreadsheet for all the fields that will be needed to generate KPIs results at 0003545
  • List of KPIs for phase I
    • Cost Based KPI
      • Cost per KG
    • Date Based KPI
      • Created to Approved
      • Approved to Delivery
  • Apply client visibility rules

Date Based KPI

Mantis: 3564 - Created to Approved AND Approved to Delivery


Validation

Approval on Date or Authorization on Date: MUST NOT be empty

  • IF Mode of transportation is assigned AND Authorized on Date is EMPTY THEN system automatically updates Authorization on Date to Today date.


Created to Approved

  • Number of days since the date of creation the shipment until the date of its approved
  • Logic per shipment: Created To Approved = Approved on Date - Created on Date
  • Logic KPI for Average number of created to approved = ((Sum of Created to Approved number of days)/(number of shipments))
  • For the specific shipments that have created on date AFTER authorization date. Logic will show a negative value for the pairing of created on date to approved on date. Please see solution logic below:
  • IF (Created on date > Authorization on Date), THEN shows result for KPI report as 0 ZERO value to avoid negative value.
  • For the specific shipments that have authorization on date EMPTY, take the value of created on date value


Mode of Transportation

  • AIR Mapping:
  • Ocean FCL Mapping:
  • Ocean LCL Mapping:
  • Truck

Approved to Delivery

  • Number of days since the date of approved the shipment until the date of its delivery
  • Logic per shipment: Approved To Delivery on Date = Delivery on Date - Approved on Date
  • Logic KPI for Average number of approved to delivery = ((Sum of Approved to Delivery number of days) / (number of shipments))
  • For the specific shipments that have created on date AFTER authorization date. Logic will show a negative value for the pairing of created on date to approved on date. Please see solution logic below:
  • IF (Created on date > Authorization on Date), THEN shows result for KPI report as 0 ZERO value to avoid negative value.
  • For the specific shipments that have authorization on date EMPTY, take the value of created on date value

Mode of Transportation

  • AIR Mapping:
  • Ocean FCL Mapping:
  • Ocean LCL Mapping:
  • Truck

Cost Based KPI

Mantis: 0003565: [DR/KPI Cost Based KPI: Cost per KG]

  • Consider Cost for close shipment ONLY, apply the same logic as GM split
  • Take the re-open and close shipment into the consideration as well to re-calculate
  • TblGeneric Shipment: A lastCloseDate, OCStatus
  • Admin to manage E0. client company and E0. client company group with bill to party (ies): Consider Cost for Sale invoices that are linked to Bill to Party(ies) with E0. Client Company or/and E0. Client Company Group as this is associated to only what cost to pay for the specific client
  • This table is needed to be created on Admin for admin to set up the link to Bill to party(ies) with E0.
  • Example: 
CT/Group
Sale invoices: 
Invoice 1 Bill to ABC amount $20
Invoice 2 Bill to ABC amount $20
Invoice 3 Bill to XYZ amount $50
Invoice 4 Bill to XYZ amount $10

Sample 1:
E0. Client company links to bill to party ABC
Take invoice 1 and 2 for the cost consideration

Sample 2:
E0. Client company links to bill to party ABC and XYZ
Take invoice 1, 2, 3, and 4 for the cost consideration
  • Currency: Apply new currency rule from GM Split for conversion. Default: USD
  • IF Actual Arrival Date is EMPTY, THEN uses currency from Created On Date
  • IF Actual Arrival Date is SET, THEN uses currency from Actual Arrival Date
  • Group shipment --> Consider currency from date logic above to the lowest number of CT in the group
  • Include/Exclude VAT and Duty report ability. There are basically 3 separate components to take into consideration
  • Import VAT/TAX charge code (Any charges for which CC:1204 was used)
  • VAT on Services Charges: VAT added to invoice based on user selection (per office: for the one that is checked to apply VAT)
  • Duty charge code (Any charges for which CC:1201 was used)
  • This should be defined on Admin (KPI) for the charge codes for VAT and DUTY
  • Label would always show what is included or exclude in a particular KPI report
  • Client Visibility: This is a subset of current Client Visibility logic
  • Consider E0. Client Company shipment ONLY. Do not include the shipment with E1, E2, E3 visibility to the client


Cost per KG

Cost Calculation

The total cost is the sum of all sales invoices issued against E0. Client Company or E0. Client company group. CT record or the group shipment 

Example:

Case 1
CT 123456 has three sales invoices issued against it.
Invoice 1 in the amount of $5.00
Invoice 2 in the amount of $10.00
Invoice 3 in the amount of $5.00
Total cost for the shipment would be $20.00

Case 2
If CT 123456 is a part of a master 123, Master 123 has CT 123456 and CT 123789 and both CTs have three same sales invoices issued against it.


This sum should be of sales invoices billed to a pre-determined bill to party linked to the client company that is viewing the KPI for External users


Take this total cost and divide it by the desired KG to determine the cost per KG.

  • In case of group shipments, we need to make sure the KG is the total sum of all of the CT's in the group 


Example:
This shipment is 10 KG
Invoice 1 Bill to: ABC amount $25.00
Invoice 2 Bill to: ABC amount $15.00
Invoice 3 Bill to: XYC amount $5.00
Invoice 4 Bill to: XYZ amount $50.00


When XYZ looks at the KPI for this shipment, average cost per KG: $5.5

(Sum of the invoices billed to XYZ for this shipment = $55.00 and this shipment weights 10 KG): 55/10 = $5.5 per KG


Universal Logic for cost per KG for all modes(AIR, FCL, Client Console, LCL, Truck-Dom, Truck-Air, Truck-Ocean):

  • Cost per KG = (Sell Rate/Gross KG)
  • Exception Truck-Dom
    • Logic:
      • TMS:
        • Cost per KG = (Sell Rate/Gross KG of record(s))
        • Consider cost to shipment level to find value within a load to line item (commodity) level
      • Non-TMS:
        • Consider cost to shipment level to find value within a group to line item (commodity) level


Overlapping KPIs

  • Optimize performance of KPIs
  • Dashboard display
  • KPIs will be created/displayed on Dashboard Tab layer
    • Start with one dashboard tab only
  • See example dashboard 1 and 2 for group by TIME based and NON-Time based at #User_Interface_Requirements_2
  • Any/all KPI dashboards should be able to be filtered based on "Created On Date" after May 1, 2010
  • DR KPI Level 1
  • Panels, Charts, and Filters display on the same level on the dashboard
    • Panel columns consist of:
      • Heading of the top of panel contains category KPI report name such as Cost Based KPI, Date Based KPI
      • KPI consists of a list of Name of object KPI report sush as Cost per item, Cost per pound under Cost based KPI panel, Created to Approved, Approved to Delivery under Date based KPI panel
      • Time frame for ATD is the name of selected time frame for ATD displays in column heading (dynamic per specified time frame selected) such as previous week, previous month, previous quarter, previous year and display value.
        • Value: average value for specific time frame
      • Changes percentage value contains percentage change of value per specified time frame compared to the same previous period
        • Label: Change
        • Formula: ((data)/(previous data) - 1) * 100%
          • data: data from time period defined in profile
          • previous data: data from previous same period (default is defined in user profile)
      • Year to Date value contains average year to date value from year to date (previous week)
        • Label: Year to Date
      • Calculated value for specified time frame
        • User can select time frame from provided list of values
    • Panel row consists of:
      • Object report name , value 1, value 2, value 3 per row
      • Total (x) is sum of Total cost Only (This is for cost based only)
        • This shows total X per selected time frame and percentage change of total X compared to the same previous period
        • Value: sum of all cost for specific time frame
        • Change formula: ((sum of values from period time frame)/(sum of values from previous period time frame) - 1) * 100%
      • See mock up below:


File:Panel1.JPG

NOTE: Each panel can consist of many KPIs. It is upon KPI(s) that users wish to see from their own visibility info.


    • Chart display allows overlap multiple KPIs charts into one chart display for data comparison with different criteria
      • If Time based group by is selected, then displays overlap multiple KPIs in a chart
      • If Non-time based group by is selected, then displays separated charts of each class of KPIs and allows overlapping multiple KPIs inside of the KPI class/catagory in a chart
      • Axis Y of chart (vertical) is always the KPI value based such as Cost per pound, Cost per Item, etc
        • This could be dual Axis chart
      • Axis X of chart (horizontal) can reflect time frame for ATD for group by data
      • Define Scale Axis
      • Label for parameter from filters selected
      • Define type of chart: Bar, Line, Scatter plot, etc
        • Phase I: As we do not give the flexibility for what type of charts to user, default for type of charts should be set such as Cost per Item will use Bar chart as default setting. Created to Approved and Approved to Delivery could be bar chart next to each other as default for the group by of Non-time based. If it is time based group by, it could be "line chart".
      • Download to Excel button
        • Displays output list select option

TIME BASED Group By

This is just a concept for overlap multiple KPIs in one chart with different KPI classes if it is possible to implement by using Jreport, LogiXML, ...etc. If it is not possible, show result separated charts side by side for what make sense such as one chart for cost based and the other for date based.

NON-TIME BASED Group By

old: File:ChartSample2.jpg

Filters

    • Filters consists of two sections:
      • Top section: select parameters to display value
        • Group by:
          • Carriers - if this is selected, shows result for previous average time frame
          • Shippers - if this is selected, shows result for previous average time frame
          • Consignees - if this is selected, shows result for previous average time frame
          • Geography - if this is selected, shows result for pairing of from to regions for previous average time frame such as North America to Asia, Western Europe to Asia, ...etc
          • Time - if time is selected, time frame for ATD criteria below will be applied
        • Mode of Transportation: Single select
          • All - Default
          • Air
          • Ocean FCL
          • Ocean LCL
          • Ocean Client Console
          • Truck Ocean
          • Truck Air
          • Truck Domestic
        • KPI: Single select or multiple select
          • If possible for overlap multiple KPIs in one chart, then allows multiple selects KPIs
          • If not possible for overlap multiple KPIs in one chart, but possible to set this filter and apply for separated charts then allow multiple selects KPIs
          • If filter is set per chart, then each KPI has its own filter with single select KPI
          • Phase I: List of universal KPI cost based:
            • Cost per KG
          • Phase I: List of universal KPI date based:
            • Created to Approved
            • Approved to Delivery
        • Show Result:
          • Currency // this is currency for cost based only (show when cost based KPI is selected) - this should be able to set from Editor as well(TBD)
        • Time Frame for ATD:
          • Weekly // if non-time filter selected, shows point of average data for previous week. If time filter is selected, shows point of value for weekly result for 12 weeks previously
            • Data point of value starts every Monday (if current week is Week 15, then shows: Week 3, Week 4 Week 5, Week 6, Week 7, Week 8, Week 9, Week 10, Week 11, Week 12, Week 13, Week 14)
          • Monthly // if non-time filter selected, shows point of average data for previous month. If time filter is selected, shows point of value for monthly result for 12 months far back for current year and previous year comparison
            • Blue line - if this month is April, shows Mar 2012, Feb 2012, Jan 2012, Dec 2011, Nov 2011, Oct 2011, Sep 2011, Aug 2011, July 2011, Jun 2011, May 2011, April 2011
            • Red line - if this month is April, shows Mar 2011, Feb 2011, Jan 2011, Dec 2010, Nov 2010, Oct 2010, Sep 2010, Aug 2010, July 2010, Jun 2010, May 2010, April 2010
          • Quarterly // if non-time filter selected, shows point of average data for previous quarter. If time filter is selected, shows point of value for quarterly result for 8 quarters data points
            • Data point of value starts from the previous quarter, if today is in April month of 2012, show 1st QT 2012, 4th QT 2011, 3rd QT 2011, 2nd QT 2011, 1st QT 2011, 4th QT 2010, 3rd QR 2010, 2nd QT 2010
          • Yearly // if non-time filter selected, shows point of average data for previous year. If time filter is selected, shows point of value for yearly average result back to the first year that have the records for comparison.
            • Data point of value starts from the previous year to years previously such as year 2012, 2011, 2010, 2009 etc.
      • Bottom section: filters to narrow result
        • PO
        • Item Code
        • Origin Countries
        • Destination Countries
        • Carriers
        • Shippers
        • Consignees

  • DR KPI Level 2
  • Download to Excel (Common option for all KPIs)
    • Output fields: Define Field Mappings (CT to XLS)
      • E0. Client Company
      • Master number
      • Group number
      • CT# (This should be always on the XLS report)
      • Load Number (EA-Truck Dom/ TMS tag)
      • Group Number
      • Shipper Name
      • Shipper City
      • Shipper State
      • Shipper Country
      • Shipper Region
      • Consignee Name
      • Consignee City
      • Consignee State
      • Consignee Country
      • Consignee Region
      • Carrier Name
      • Mode of Transportation
      • Commodity Item P.O
      • Commodity Item SKU
      • Commodity Item Description
      • Commodity Item Quantity
      • Commodity Gross KG (Truck)
      • Commodity Gross LB (Truck-Domestic)
      • Total Pkg on Pallet
      • Total Loose package
      • Chargeable Weight (Air)
      • Cubic Meters (LCL)
      • Container # (FCL and Vendor Console)
      • Container size/type (FCL and Vendor Console)
      • TEU (FCL)
      • Total "E0 Sales" in USD
      • Bill to party
      • Total VAT
      • Total Duty
      • Total charges excluding VAT/DUTY
      • Cost per Item (KPI)
      • Created on Date
      • Approved on Date
      • Actual Delivery Date
      • Created To Approved (KPI)
      • Approved To Delivery (KPI)
    • NOTE 1: Once list of object reports of KPIs is added, this should be added into output list as well.
    • NOTE 2: For the field(s) that is not applicable to the record, show n/a result on the spreadsheet if user selects to see this output

  • Validation
  • Limitation for number of KPIs per chart

Comments from Alex to SOW 1 section

Tira, please fix the following ASAP:

  • Business Requirements still contains questions// completed
  • Gauges should be removed// completed (Jreport, LogiXML will provide user flexibility to select what type of chart they would like to see...so I take this part out completely
  • KPI: Single select section contains reference to other units - I thought we agreed to limit phase 1 to kg // Completed. I moved all the list of KPIs Universal and unit measurement for mode specific to the top section. It is not under SOW 1 anymore.


Some Clarifications from Alex

Key Time Intervals

Two Key CT intervals (in days) are between the following 3 dates:

  • date when shipment was created
  • date when shipment was approved for pick up
  • date when shipment was delivered

See mappings and more info here: #Created to Approved, #Approved to Delivery

Descartes TMS based Data vs CT2 based

For some E0 that are using Descartes TMS some data is stored in a different way. For example cost of CT.

Also for TMS there is a concept of load that is not available for Truck mode for E0 that do not use TMS.

Work breakdown structure

3567 Proof Of Concept for integration between CT2 Client and DR produced by JReports

Create a simple dashboard report using JReports that emulates our approach to DR/KPI reports.

Requirements:

  • pull data from CT2 database (could be simpler than existing KPI spec and not related to it). Ideally produce this example:
    • DR panel has two filters: (Client E0) - single select; Created on date (from date to date).
    • Below is radio ("Shippers"-default vs "Consignees" )
    • Below is radio (bar chart-default vs pie)
    • Below is graphics
    • axis X: 6 bars for top 5 (as far as number of CTs) Shippers (or Consignees) with labels and 6th represents all remaining Shippers/Consignees
    • axis Y: Number of CTs (for selected E0, for specific Shipper/Consignee)
    • User can change filters/controls and system will re-draw plot on the fly
  • must be integrated seamlessly from:
    • look and feel experience (should blend in CT2 Client front end)
    • security (display only dashboard allowed for particular user)
      • QA test: assign one KPI to one user, one to another and verify what they see
    • parameters for report should be controlled through user profile
      • number 5 should be a parameter in user profile

All should pass QA and task deployed to UAT to demo Marc latest Thursday, May 17.

0003564: [DR/KPI] Date Based KPI: Created to Approved AND Approved to Delivery

0003565: [DR/KPI] Cost Based KPI: Cost per KG

0003568 [DR/KPI] Integrate Cost based and Time based KPIs into one DR panel that will appear on CT2 Client dashboard

Dependencies

All tasks except 3568 can be started right away. Once they all complete 3568 can be started.

Retrieved from "https://wiki.jaguarfreight.com/Kpi_alex"
Personal tools