Aldo Report
Summary
Historical View of Booked and Confirmed Jobs (Type = 8)
This page provides a historical overview of booked and confirmed jobs (type = 8), excluding lost and deleted jobs, for both the current and previous year.
Filters
| Filter | Description |
|---|---|
| Carriers | Filters by carrier name |
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. |
| reporting db quotation totals | SELECT * FROM reporting_db_quotation_totals | This selects data from reporting_db_quotation_totals |
| 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 |
| Users | SELECT id, name FROM users | This query selects User id and name from user table |
Calculations
| Reference | Calculation | Description |
|---|---|---|
| Quotes | Previous Year Total QuotesB =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[quoted_at]) &&
Quotation[type] = 8 &&
YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
)
),
0) |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |