Jump to content

Shipment Detail Report

From PBI Reporting Wiki

Summary

TBC

Filters

Filter Description
TBC TBC

Calculations

Reference Calculation Description
1 - Won / Lost = If the quote was won or lost Formula:

Won/Lost =

SWITCH ( TRUE(), 'quotations'[status] IN {8, 61, 62}, "Lost", 'quotations'[status] IN {0, 1, 2, 20, 6, 60}, "Pending", 'quotations'[status] IN {3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, 71}, "Won", "Unknown")

This formula determines the status of a quotation based on its assigned numerical code. The possible statuses are:


  • Lost: If the quotation status is 8, 61, or 62, it is categorized as "Lost".
  • Pending: If the status is 0, 1, 2, 20, 6, or 60, the quotation is still "Pending".
  • Won: If the status is 3, 4, 5, 7, 30, 31, 32, 33, 34, 35, 70, or 71, the quotation is considered "Won".
  • Unknown: If the status does not match any of the predefined categories, it defaults to "Unknown" .
2 - Deleted = If the shipment is deleted The formula:

Deleted = IF (ISBLANK (quotations[deleted_at]), "X", UNICHAR (10003)

)

This formula checks whether a quotation has been deleted and assigns a corresponding

symbol:

  • If the deleted_at field is blank (i.e., the quotation has not been deleted), it displays "X".
  • If the deleted_at field contains a value (i.e., the quotation has been deleted), it shows a ✓ (checkmark) using the UNICHAR (10003) function.
3 - Status = Current status of the shipment in QMS The formula:

State = 

   SWITCH ( 

   TRUE(), 

   VALUE('quotations'[status]) = 0, "Search - routes", 

   VALUE('quotations'[status]) = 1, "Search - confirm", 

   VALUE('quotations'[status]) = 2, "Quote", 

   VALUE('quotations'[status]) = 20, "Quote - requote request", 

   VALUE('quotations'[status]) = 6, "Quote - spot rate", 

   VALUE('quotations'[status]) = 60, "Quote - spot rate review required", 

   VALUE('quotations'[status]) = 5, "Booking - cancellation request", 

   VALUE('quotations'[status]) = 31, "Booking - cancelled with charges", 

   VALUE('quotations'[status]) = 30, "Booking - acceptance review required", 

   VALUE('quotations'[status]) = 33, "Booking - confirmed review required", 

   VALUE('quotations'[status]) = 34, "Booking - sync review required", 

   VALUE('quotations'[status]) = 35, "Booking - schedule confirmed review required", 

   VALUE('quotations'[status]) = 4, "Booking - pending credit", 

   VALUE('quotations'[status]) = 3, "Booking - carrier confirmation required", 

   VALUE('quotations'[status]) = 32, "Booking - pending", 

   VALUE('quotations'[status]) = 70, "Booking - rebooking request", 

   VALUE('quotations'[status]) = 7, "Booking - confirmed", 

   VALUE('quotations'[status]) = 71, "Booking - airline confirmed", 

   VALUE('quotations'[status]) = 8, "Lost", 

   VALUE('quotations'[status]) = 61, "Lost - carrier declined", 

   VALUE('quotations'[status]) = 62, "Lost - customer declined", 



   "UnKnown"

)

This formula categorizes the status of a quotation based on its numerical value and assigns a corresponding description. The possible states are:


Search & Quotation States:

  • Search - Routes (0): The user is searching for available routes.
  • Search - Confirm (1): The user is confirming route details.
  • Quote (2): A quotation has been generated.
  • Quote - Requote Request (20): A request for a revised quotation has been made.
  • Quote - Spot Rate (6): A special pricing (spot rate) has been requested.
  • Quote - Spot Rate Review Required (60): The spot rate needs review.

Booking States:

  • Booking - Carrier Confirmation Required (3): Waiting for carrier confirmation.
  • Booking - Pending Credit (4): Booking is pending due to credit approval.
  • Booking - Cancellation Request (5): A request to cancel the booking has been made.
  • Booking - Acceptance Review Required (30): The booking needs review before acceptance.
  • Booking - Cancelled with Charges (31): The booking was canceled with applicable charges.
  • Booking - Pending (32): The booking is pending approval or confirmation.
  • Booking - Confirmed Review Required (33): The confirmed booking needs further review.
  • Booking - Sync Review Required (34): A synchronization issue requires review.
  • Booking - Schedule Confirmed Review Required (35): The schedule confirmation needs review.
  • Booking - Rebooking Request (70): A request to rebook the shipment has been made.
  • Booking - Confirmed (7): The booking has been successfully confirmed.
  • Booking - Airline Confirmed (71): The airline has confirmed the booking.

Lost States:

  • Lost (8): The quotation was lost.
  • Lost - Carrier Declined (61): The carrier rejected the booking request.
  • Lost - Customer Declined (62): The customer rejected the quotation.

Default State:Unknown: If the quotation status does not match any of the predefined values, it is categorized as "Unknown".

4 - Loss Reasons = The lost reason that was set
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.
16 The clustered bar chart breakdown total bookings by Teams.

}

Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Shipment_Detail_Report&oldid=41"