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 |
|---|---|---|
| Total Quotes previous Year | 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) |
Previous Year Total Quotes B measures the total number of distinct quotes (type = 8) issued during the previous calendar year, excluding any records without a valid quotation date. |
| Quoted Customer previous year | Previous Year Quoted CustomersB =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[customer_id]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[quoted_at]) &&
NOT ISBLANK(Quotation[id]) &&
Quotation[type] = 8 &&
YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
)
),
0) |
Previous Year Quoted Customers B counts the number of unique customers who received a quote (type = 8) during the previous calendar year, excluding any records with missing quotation dates. |
| Previous Year Total Booking | Previous Year Total BookingB =
VAR StartPrevYear =
DATE( YEAR(TODAY()) - 1, 1, 1 )
VAR StartCurrentYear =
DATE( YEAR(TODAY()), 1, 1 )
RETURN
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[id]),
NOT ISBLANK(Quotation[booked_at]),
Quotation[deleted_at] = BLANK(),
Quotation[lost_at] = BLANK(),
Quotation[type] = 8,
Quotation[created_at] >= StartPrevYear,
Quotation[created_at] < StartCurrentYear
),
0) |
Previous Year Total Booking B measures the number of distinct bookings (type = 8) created during the previous calendar year, excluding any bookings that were deleted or marked as lost. |
| Previous Year Chargeable Weight booked kg | PreviousYear_Booked_ChargeableWeight_kg =
COALESCE(
CALCULATE(
SUM(Quotation[reporting_db quote_revenue.chargeable_weight]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[booked_at]) &&
Quotation[type] = 8 &&
YEAR(Quotation[booked_at]) = YEAR(TODAY()) - 1
)
),
0
) |
Previous Year Chargeable Weight Booked (kg) represents the total chargeable weight from all quotations issued in the previous calendar year, based on the value recorded in reporting_db quote_revenue.chargeable_weight.
|
| Booked Customers previous year | Previous Year Booked CustomersB =
VAR StartPrevYear =
DATE(YEAR(TODAY()) - 1, 1, 1)
VAR StartCurrentYear =
DATE(YEAR(TODAY()), 1, 1)
RETURN
COALESCE(
CALCULATE(
DISTINCTCOUNT('Quotation'[customer_id]),
'Quotation'[type] = 8,
NOT ISBLANK('Quotation'[customer_id]),
NOT ISBLANK('Quotation'[booked_at]) || NOT ISBLANK('Quotation'[confirmed_at]),
ISBLANK('Quotation'[lost_at]),
ISBLANK('Quotation'[deleted_at]),
'Quotation'[created_at] >= StartPrevYear,
'Quotation'[created_at] < StartCurrentYear
),
0) |
Previous Year Booked Customers B reports the number of unique customers who had a booked or confirmed job (type = 8) during the previous calendar year, excluding any jobs marked as lost or deleted. |
| Booked Routes (O + D) | PreviousYear_Origin_dest_Count_Booked =
COALESCE(
CALCULATE(
DISTINCTCOUNT(Quotation[Origin _ Destination]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[booked_at]) &&
Quotation[type] = 8 &&
YEAR(Quotation[booked_at]) = YEAR(TODAY()) - 1
)
),
0) |
Previous Year Origin–Destination Count (Booked) measures the number of unique origin–destination pairs associated with bookings made during the previous calendar year for jobs of type 8.
Calculation Logic:
|
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |