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.
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