Overall YTD

Summary

  Purpose

This page provides a comprehensive Year-To-Date (YTD) and historical analysis of Booked and confirmed bookings (type = 8), excluding lost and deleted jobs. It focuses on key business KPIs such as jobs, customers, chargeable weight, revenue, gross profit, and margin

Filters

Filter	Description
Carriers	Filter by carrier name based on main carrier id
Year	Filter by year booked or confirmed

Data Transformation Applied

[To be added]

Query	Calculations	Description
Quotation table	SELECT    q.id,    q.booked_at,    q.confirmed_at,    q.quoted_at,    q.pre_quoted_at,    q.created_at,    q.user_id,    q.booking_user_id,    q.lost_reason,    q.customer_id,    q.customer_code,    q.shipper_status,    q.main_carrier_id,    q.deleted_at,    q.lost_at,    q.delete_reasons,    q.chargeable_weight,    q.gross_weight,    q.spotrate,    q.type,    q.schedule_auto_update,    q.automation_created_count,    q.schedule_auto_amount,    q.status,    q.booking_sync_state,    q.cargo_type,    q.house_airwaybill,    q.product,    q.origin,    q.destination,    q.master_airwaybill,    q.hazardous_goods,    q.commodity_description,    q.weight_unit,    q.cargo_only,    CASE        WHEN q.weight_unit = 'lb' THEN ROUND(q.chargeable_weight * 0.453592, 2)        ELSE q.charge_weight    END AS chargeable_weight_kg FROM quotations AS q WHERE q.created_at >= '2024-01-01'   AND q.type = 8   AND q.lost_at IS NULL   AND q.deleted_at IS NULL;	This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.
Sites	SELECT id, name FROM sites	This selects site id and name from the site table.
Customer	SELECT id, name FROM customers	This query selects customer id and name from customer table

Calculations

Reference	Calculation	Description
TBC	TBC	TBC