Weight v Revenue Week-On-Week: Difference between revisions
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== Summary == | == Summary == | ||
The Weight vs Revenue Week-on-Week page is part of the Yield, Weight, and Revenue GSA report. It displays weekly revenue by entities | The Weight vs Revenue Week-on-Week page is part of the Yield, Weight, and Revenue GSA report. It displays weekly revenue by entities. | ||
== Filters == | == Filters == | ||
| Line 9: | Line 9: | ||
!Description | !Description | ||
|- | |- | ||
| | |'''Database''' | ||
| | |Select the reporting database. | ||
|- | |||
|'''Site''' | |||
|Choose the location or branch for the report. | |||
|- | |||
|'''Carrier''' | |||
|Filter by transport provider. | |||
|- | |||
|'''Service''' | |||
|Narrow results by type of service offered. | |||
|- | |||
|'''Customer''' | |||
|View data for a specific customer. | |||
|- | |||
|'''Commodity Type''' | |||
|Filter by category of goods. | |||
|- | |||
|'''Product Type''' | |||
|Select specific product groups. | |||
|- | |||
|'''Date''' | |||
|Define the reporting period (weekly, monthly, or custom range). | |||
|} | |} | ||
| Line 19: | Line 40: | ||
!Transformations | !Transformations | ||
|- | |- | ||
|let | |||
Source = MySQL.Database( | |||
"qms-reporting.cluster-ro-cbuxcavwaolv.eu-west-2.rds.amazonaws.com", | |||
"db", | |||
[ | |||
ReturnSingleDatabase = true, | |||
CommandTimeout = #duration(0, 10, 0, 0), | |||
| | | | ||
* '''Formatting''' – Data is arranged into a consistent structure used across all report pages. | |||
* '''Merging''' – Related tables (e.g., customers, carriers, services) are combined so the report shows complete information in one place. | |||
* '''Combining''' – Combined four tables (Groupair, AirCross, HAE, And ACS) | |||
* '''Cleaning''' – Any extra technical fields are removed, and values are standardized for easy reading. | |||
|} | |} | ||
| Line 30: | Line 67: | ||
!Description | !Description | ||
|- | |- | ||
|1.'''Revenue and CHW(KG) by Week Number''' | |||
|Total Revenue In euro = <pre>SUM('Combined Quatations Tables2'[Revenue In EURO])</pre> | |||
CHW (KG) = | |||
<pre>SUMX ( | |||
'Combined Quatations Tables2', | |||
IF ( | |||
'Combined Quatations Tables2'[weight_unit] = "lb", | |||
'Combined Quatations Tables2'[chargeable_weight] * 0.45359237, | |||
'Combined Quatations Tables2'[chargeable_weight] -- already in KG | |||
) | |||
)</pre> | |||
| | | | ||
| | * '''Total Revenue in Euro''' – Calculates the total revenue by summing all values in Euro from the combined quotations table. | ||
| | |||
* '''CHW (KG)''' – Calculates the total chargeable weight in kilograms, converting from Lb when needed. | |||
|- | |||
|2'''.Total Revenue by Week Numbers and Entity''' | |||
|Total Revenue In euro =<pre>SUM('Combined Quatations Tables2'[Revenue In EURO])</pre> | |||
|This is a table that displays weekly Revenue in EUR and CHW(KG) across different entities. | |||
|} | |} | ||
Latest revision as of 14:30, 1 October 2025
Summary
The Weight vs Revenue Week-on-Week page is part of the Yield, Weight, and Revenue GSA report. It displays weekly revenue by entities.
Filters
| Filter | Description |
|---|---|
| Database | Select the reporting database. |
| Site | Choose the location or branch for the report. |
| Carrier | Filter by transport provider. |
| Service | Narrow results by type of service offered. |
| Customer | View data for a specific customer. |
| Commodity Type | Filter by category of goods. |
| Product Type | Select specific product groups. |
| Date | Define the reporting period (weekly, monthly, or custom range). |
Data Transformation Applied
| Query | Transformations |
|---|---|
| let
Source = MySQL.Database( "qms-reporting.cluster-ro-cbuxcavwaolv.eu-west-2.rds.amazonaws.com", "db", [ ReturnSingleDatabase = true, CommandTimeout = #duration(0, 10, 0, 0), |
|
Calculations
| Columns | Calculation | Description |
|---|---|---|
| 1.Revenue and CHW(KG) by Week Number | Total Revenue In euro = SUM('Combined Quatations Tables2'[Revenue In EURO])
CHW (KG) = SUMX ( 'Combined Quatations Tables2', IF ( 'Combined Quatations Tables2'[weight_unit] = "lb", 'Combined Quatations Tables2'[chargeable_weight] * 0.45359237, 'Combined Quatations Tables2'[chargeable_weight] -- already in KG ) ) |
|
| 2.Total Revenue by Week Numbers and Entity | Total Revenue In euro =SUM('Combined Quatations Tables2'[Revenue In EURO])
|
This is a table that displays weekly Revenue in EUR and CHW(KG) across different entities. |