Customer Overall TY - Revision history

Hendry.mwamburi at 09:09, 2 December 2025

2025-12-02T09:09:24Z

← Older revision		Revision as of 11:09, 2 December 2025
Line 3:		Line 3:
	Year-to-Date Confirmed Jobs (Type = 8) Overview		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~~.		This page provides a historical view of year-to-date confirmed jobs (type = 8), excluding lost and deleted jobs.

	Key business KPIs tracked include:		Key business KPIs tracked include:

Hendry.mwamburi: /* Calculations */

2025-12-02T08:55:25Z

Calculations

← Older revision		Revision as of 10:55, 2 December 2025
Line 228:		Line 228:
	**** '''Aggregation:''' The <code>chargeable_weight_kg</code> values of the filtered quotations are '''summed'''.		**** '''Aggregation:''' The <code>chargeable_weight_kg</code> values of the filtered quotations are '''summed'''.
	**** '''Handling Missing Data:''' The <code>COALESCE</code> function ensures that if no confirmed quotations exist, the metric returns '''0''' instead of blank. '''Formula:''' Current YTD Chargeable Weight Confirmed (kg)=∑Chargeable Weight (kg) for all confirmed quotations YTD '''Purpose:'''		**** '''Handling Missing Data:''' The <code>COALESCE</code> function ensures that if no confirmed quotations exist, the metric returns '''0''' instead of blank. '''Formula:''' Current YTD Chargeable Weight Confirmed (kg)=∑Chargeable Weight (kg) for all confirmed quotations YTD '''Purpose:'''
			\|-
			\| Origin destination
			pair
			\|<pre> each [origin] & " - " & [destination]</pre>\|\| The formula concatenate origin column and destination column
	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-12-02T08:45:02Z

Calculations

← Older revision		Revision as of 10:45, 2 December 2025
Line 210:		Line 210:
	*** '''Formula:''' Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Totalfor all confirmed quotations YTD		*** '''Formula:''' Confirmed Revenue YTD=∑Exchange Rate to EuroQuotation Totalfor all confirmed quotations YTD
	\|-		\|-
	\| ~~Won Quote%~~ \|\|<pre>Current YTD ~~Won Quote %~~ =		\| Chargeable weight \|\|<pre>Current YTD Chargeable Weight Confirmed (kg) =
	COALESCE(		COALESCE(
	~~DIVIDE(~~
	CALCULATE(		CALCULATE(
	~~DISTINCTCOUNT~~(Quotation[id]),		SUM(Quotation[chargeable_weight_kg]),
	FILTER(		FILTER(
	Quotation,		Quotation,
	~~NOT ISBLANK(Quotation[quoted_at]) &&~~		NOT ISBLANK(Quotation[confirmed_at])
	NOT ISBLANK(Quotation[confirmed_at]) &&
	~~Quotation[type] = 8~~
	),		),
	DATESYTD(Quotation[~~quoted_at])~~		DATESYTD(Quotation[confirmed_at])
	),
	~~CALCULATE(~~
	~~DISTINCTCOUNT(Quotation[id]),~~
	~~FILTER(~~
	~~Quotation,~~
	~~NOT ISBLANK(Quotation[quoted_at]) &&~~
	~~Quotation[type] = 8~~
	),
	~~DATESYTD(Quotation[quoted_at~~])
	),		),
	0		0
	)~~,0)~~</pre>\|\|		)
	** 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:'''		</pre>\|\|
	*** '''~~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~~'''.		*** The '''Current Year-to-Date (YTD) Chargeable Weight Confirmed''' measures the total '''chargeable weight in kilograms''' from all quotations that have been '''confirmed''' so far in the current year. '''Calculation Logic:'''
	*** '''~~Filter Applied~~:''' Only quotations ~~that meet~~ the ~~following conditions~~ are included:		**** '''Included Quotations:''' Only quotations where <code>confirmed_at</code> is '''not blank''' (i.e., confirmed jobs).
	**** <code>~~confirmed_at~~</code> is '''~~not blank~~''' ~~(i.e., the quotation has been confirmed)~~.		**** '''Time Frame:''' Only quotations within the '''current year-to-date''' (<code>DATESYTD(Quotation[confirmed_at])</code>) are included.
	**** <code>~~confirmed_at~~</code> ~~falls between '''January 1st of~~ the ~~current year~~''' ~~and '''today~~'''.		**** '''Aggregation:''' The <code>chargeable_weight_kg</code> values of the filtered quotations are '''summed'''.
	~~***~~ '''Formula:''' Confirmed ~~Revenue YTD~~=~~∑Exchange Rate to EuroQuotation Totalfor~~ all confirmed quotations YTD		**** '''Handling Missing Data:''' The <code>COALESCE</code> function ensures that if no confirmed quotations exist, the metric returns '''0''' instead of blank. '''Formula:''' Current YTD Chargeable Weight Confirmed (kg)=∑Chargeable Weight (kg) for all confirmed quotations YTD '''Purpose:'''
	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-12-02T08:42:18Z

Calculations

← Older revision		Revision as of 10:42, 2 December 2025
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 Totalfor 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 Totalfor 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~~'''.
	\|}		\|}

Hendry.mwamburi: /* Calculations */

2025-12-02T08:23:14Z

Calculations

@@ Line 153: / Line 153: @@
 |-
 ! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
 |-
 | Won Quote% ||<pre>Current YTD Won Quote % =

Hendry.mwamburi: /* Calculations */

2025-12-02T08:21:56Z

Calculations

@@ Line 154: / Line 154: @@
 ! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
 |-
-| TBC ||<pre>TBC</pre>|| TBC
+| Won Quote% ||<pre>Current YTD Won Quote % =
 |}

Hendry.mwamburi: /* Data Transformation Applied */

2025-12-02T08:13:55Z

Data Transformation Applied

← Older revision		Revision as of 10:13, 2 December 2025
Line 43:		Line 43:
	!Description		!Description
	\|-		\|-
	\|~~Quote revenue~~ from reporting database		\|Quotation totals from reporting database
	\|SELECT * FROM ~~reporting_db_quote_revenue~~		\|SELECT * FROM reporting_db_quotation_totals
	\|The query retrieves data from the ~~quote revenue~~ table in the reporting database.		\|The query retrieves data from the quotation totals table in the reporting database.
	\|-		\|-
	\|Quotation table		\|Quotation table

Hendry.mwamburi: /* Data Transformation Applied */

2025-12-02T08:10:23Z

Data Transformation Applied

@@ Line 43: / Line 43: @@
 !Description
 |-
+|Quote revenue from reporting database
+|SELECT * FROM reporting_db_quote_revenue
+|The query retrieves data from the quote revenue table in the reporting database.
 |}

Hendry.mwamburi: /* Filters */

2025-12-02T08:05:06Z

Filters

← Older revision		Revision as of 10:05, 2 December 2025
Line 32:		Line 32:
	! style="width: 25%;" \| Filter !! style="width: 75%;" \| Description		! style="width: 25%;" \| Filter !! style="width: 75%;" \| Description
	\|-		\|-
	\| ~~TBC~~\|\| ~~TBC~~		\| Carriers\|\| Filter data by carrier name
	\|}		\|}

Hendry.mwamburi: /* Summary */

2025-12-02T08:04:17Z

Summary

← Older revision		Revision as of 10:04, 2 December 2025
Line 1:		Line 1:
	==Summary==		==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==		==Filters==