Overall YTD: Difference between revisions
| Line 132: | Line 132: | ||
|- | |- | ||
! style="width: 20%;" | Reference !! style="width: 40%;" | Calculation !! style="width: 40%;" | Description | ! style="width: 20%;" | Reference !! style="width: 40%;" | Calculation !! style="width: 40%;" | Description | ||
|- | |||
| Totals Jobs ||<pre>Total Jobs= | |||
CALCULATE( | |||
DISTINCTCOUNT('Quotation'[id]), | |||
FILTER( | |||
'Quotation', | |||
NOT ISBLANK('Quotation'[id]) | |||
) | |||
)</pre>|| The formula calculates the total number of unique jobs by quotation ID | |||
|- | |||
| Totals Jobs ||<pre>Total Jobs= | |||
CALCULATE( | |||
DISTINCTCOUNT('Quotation'[id]), | |||
FILTER( | |||
'Quotation', | |||
NOT ISBLANK('Quotation'[id]) | |||
) | |||
)</pre>|| The formula calculates the total number of unique jobs by quotation ID | |||
|- | |- | ||
| Totals Jobs ||<pre>Total Jobs= | | Totals Jobs ||<pre>Total Jobs= | ||
Revision as of 09:38, 25 November 2025
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 |
| Users | SELECT id, name FROM users | This query selects User id and name from user table |
Calculations
| Reference | Calculation | Description |
|---|---|---|
| Totals Jobs | Total Jobs=
CALCULATE(
DISTINCTCOUNT('Quotation'[id]),
FILTER(
'Quotation',
NOT ISBLANK('Quotation'[id])
)
) |
The formula calculates the total number of unique jobs by quotation ID |
| Totals Jobs | Total Jobs=
CALCULATE(
DISTINCTCOUNT('Quotation'[id]),
FILTER(
'Quotation',
NOT ISBLANK('Quotation'[id])
)
) |
The formula calculates the total number of unique jobs by quotation ID |
| Totals Jobs | Total Jobs=
CALCULATE(
DISTINCTCOUNT('Quotation'[id]),
FILTER(
'Quotation',
NOT ISBLANK('Quotation'[id])
)
) |
The formula calculates the total number of unique jobs by quotation ID |