Jump to content

Customer Overall TY: Difference between revisions

From PBI Reporting Wiki
← Older editNewer edit →
VisualWikitext
Line 189: Line 189:
** Fall within the '''current year-to-date'''.
** Fall within the '''current year-to-date'''.
|-
|-
| Won Quote% ||<pre>Current YTD Won Quote % =  
| Revenue ||<pre>Confirmed_revenue YTD =  
COALESCE(
CALCULATE(
DIVIDE(
     SUMX(
     CALCULATE(
         'Dimreporting_db quotation_totals',
         DISTINCTCOUNT(Quotation[id]),
         'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
         FILTER(
             / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate]
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
             NOT ISBLANK(Quotation[confirmed_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
     ),
     ),
     CALCULATE(
     FILTER(
         DISTINCTCOUNT(Quotation[id]),
         'Dimreporting_db quotation_totals',
         FILTER(
         NOT ISBLANK( 'Dimreporting_db quotation_totals'[confirmed_at] )
            Quotation,
            && 'Dimreporting_db quotation_totals'[confirmed_at] >= DATE( YEAR( TODAY() ), 1, 1 )
            NOT ISBLANK(Quotation[quoted_at]) &&
            && 'Dimreporting_db quotation_totals'[confirmed_at] <= TODAY()
            Quotation[type] = 8
     )
        ),
)</pre>||
        DATESYTD(Quotation[quoted_at])
** The '''Confirmed Revenue Year-to-Date (YTD)''' represents the total revenue from confirmed quotations (type = 8) '''from the start of the current year up to today''', converted into Euros. '''Calculation Logic:'''
     ),
*** '''Revenue Conversion:''' Each quotation’s <code>customer_invoice_currency_sell_total</code> is divided by the <code>confirmed_customer_invoice_currency_to_euro_exchange_rate</code> to standardize revenue into '''Euros'''.
    0
*** '''Filter Applied:''' Only quotations that meet the following conditions are included:
),0)</pre>|| The '''Current Year-to-Date (YTD) Won Quote %''' measures the proportion of quotes that have been '''both issued and confirmed''' (type = 8) relative to all quotes issued (type = 8) '''so far in the current year'''.
**** <code>confirmed_at</code> is '''not blank''' (i.e., the quotation has been confirmed).
'''Calculation Logic:'''
**** <code>confirmed_at</code> falls between '''January 1st of the current year''' and '''today'''.
* '''Numerator:''' Counts distinct quotations that:
*** '''Formula:''' Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Total​for all confirmed quotations YTD
** Have a non-blank <code>quoted_at</code> date.
** Have a non-blank <code>confirmed_at</code> date.
** Are of type 8 (confirmed jobs).
** Fall within the '''current year-to-date''' (<code>DATESYTD(Quotation[quoted_at])</code>).
* '''Denominator:''' Counts distinct quotations that:
** Have a non-blank <code>quoted_at</code> date.
** Are of type 8.
** Fall within the '''current year-to-date'''.
|-
|-
| Won Quote% ||<pre>Current YTD Won Quote % =  
| Won Quote% ||<pre>Current YTD Won Quote % =  
Line 247: Line 233:
     ),
     ),
     0
     0
),0)</pre>|| The '''Current Year-to-Date (YTD) Won Quote %''' measures the proportion of quotes that have been '''both issued and confirmed''' (type = 8) relative to all quotes issued (type = 8) '''so far in the current year'''.
),0)</pre>||
'''Calculation Logic:'''
** The '''Confirmed Revenue Year-to-Date (YTD)''' represents the total revenue from confirmed quotations (type = 8) '''from the start of the current year up to today''', converted into Euros. '''Calculation Logic:'''
* '''Numerator:''' Counts distinct quotations that:
*** '''Revenue Conversion:''' Each quotation’s <code>customer_invoice_currency_sell_total</code> is divided by the <code>confirmed_customer_invoice_currency_to_euro_exchange_rate</code> to standardize revenue into '''Euros'''.
** Have a non-blank <code>quoted_at</code> date.
*** '''Filter Applied:''' Only quotations that meet the following conditions are included:
** Have a non-blank <code>confirmed_at</code> date.
**** <code>confirmed_at</code> is '''not blank''' (i.e., the quotation has been confirmed).
** Are of type 8 (confirmed jobs).
**** <code>confirmed_at</code> falls between '''January 1st of the current year''' and '''today'''.
** Fall within the '''current year-to-date''' (<code>DATESYTD(Quotation[quoted_at])</code>).
*** '''Formula:''' Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Total​for all confirmed quotations YTD
* '''Denominator:''' Counts distinct quotations that:
** Have a non-blank <code>quoted_at</code> date.
** Are of type 8.
** Fall within the '''current year-to-date'''.
|}
|}

Revision as of 09:42, 2 December 2025

Summary

Year-to-Date Confirmed Jobs (Type = 8) Overview

This page provides a historical view of year-to-date confirmed jobs (type = 8), excluding lost and deleted jobs, for both the current and previous year.

Key business KPIs tracked include:

Customers

Won quotes percentage

Direct bookings

Chargeable weight

Revenue

Gross profit

Routes

Carriers

Users

This page serves as a quick reference for monitoring performance trends and overall business growth.

Filters

Filter Description
Carriers Filter data by carrier name

Data Transformation Applied

[To be added]

Query Calculations Description
Quotation totals from reporting database SELECT * FROM reporting_db_quotation_totals The query retrieves data from the quotation totals table in the reporting database.
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.
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
Won Quote% 
Current YTD Won Quote % = 
COALESCE(
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            NOT ISBLANK(Quotation[confirmed_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    0
),0)
 The Current Year-to-Date (YTD) Won Quote % measures the proportion of quotes that have been both issued and confirmed (type = 8) relative to all quotes issued (type = 8) so far in the current year.

Calculation Logic:

  • Numerator: Counts distinct quotations that:
    • Have a non-blank quoted_at date.
    • Have a non-blank confirmed_at date.
    • Are of type 8 (confirmed jobs).
    • Fall within the current year-to-date (DATESYTD(Quotation[quoted_at])).
  • Denominator: Counts distinct quotations that:
    • Have a non-blank quoted_at date.
    • Are of type 8.
    • Fall within the current year-to-date.
Revenue 
Confirmed_revenue YTD = 
CALCULATE(
    SUMX(
        'Dimreporting_db quotation_totals',
        'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
            / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate]
    ),
    FILTER(
        'Dimreporting_db quotation_totals',
        NOT ISBLANK( 'Dimreporting_db quotation_totals'[confirmed_at] )
            && 'Dimreporting_db quotation_totals'[confirmed_at] >= DATE( YEAR( TODAY() ), 1, 1 )
            && 'Dimreporting_db quotation_totals'[confirmed_at] <= TODAY()
    )
)
    • The Confirmed Revenue Year-to-Date (YTD) represents the total revenue from confirmed quotations (type = 8) from the start of the current year up to today, converted into Euros. Calculation Logic:
      • Revenue Conversion: Each quotation’s customer_invoice_currency_sell_total is divided by the confirmed_customer_invoice_currency_to_euro_exchange_rate to standardize revenue into Euros.
      • Filter Applied: Only quotations that meet the following conditions are included:
        • confirmed_at is not blank (i.e., the quotation has been confirmed).
        • confirmed_at falls between January 1st of the current year and today.
      • Formula: Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Total​for all confirmed quotations YTD
Won Quote% 
Current YTD Won Quote % = 
COALESCE(
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            NOT ISBLANK(Quotation[confirmed_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    0
),0)
    • The Confirmed Revenue Year-to-Date (YTD) represents the total revenue from confirmed quotations (type = 8) from the start of the current year up to today, converted into Euros. Calculation Logic:
      • Revenue Conversion: Each quotation’s customer_invoice_currency_sell_total is divided by the confirmed_customer_invoice_currency_to_euro_exchange_rate to standardize revenue into Euros.
      • Filter Applied: Only quotations that meet the following conditions are included:
        • confirmed_at is not blank (i.e., the quotation has been confirmed).
        • confirmed_at falls between January 1st of the current year and today.
      • Formula: Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Total​for all confirmed quotations YTD
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Customer_Overall_TY&oldid=427"