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 quoted kg | Previous Year Chargeable Weight Quoted (kg) =
COALESCE(
CALCULATE(
SUM(Quotation[reporting_db quote_revenue.chargeable_weight]),
FILTER(
Quotation,
NOT ISBLANK(Quotation[reporting_db quote_revenue.quoted_at]) &&
YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
)
),
0
) |
Previous Year Chargeable Weight Quoted (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.
|
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |
| TBC | TBC |
TBC |