Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I want calculate the cost per mile and cost per weight and cost per pallet. I achieved the my result but dashboard slowdown.
This is the scenario.
Shipment | Miles | Amount | Pallets | Weight | Service | CustomerName | CarrierName | Mode | ChargeGroup |
---|---|---|---|---|---|---|---|---|---|
7457778 | 2681 | 359.11 | 26 | 11623 | LH Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7457778 | 2681 | 50 | 26 | 11623 | Stop Off | Rockwell Automation | Rockwell International | Truckload | Accessorial |
7457778 | 2681 | 85.01 | 26 | 11623 | LTL Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7457778 | 2681 | 1875.34 | 26 | 11623 | Consol Linehaul | Rockwell Automation | Rockwell International | Truckload | Transportation |
7457778 | 2681 | 739.22 | 26 | 11623 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7593211 | 2681 | 38.06 | 11 | 4778 | LTL Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7593211 | 2681 | 192.17 | 11 | 4778 | LH Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7593211 | 2681 | 346 | 11 | 4778 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7593211 | 2681 | 50 | 11 | 4778 | Stop Off | Rockwell Automation | Rockwell International | Truckload | Accessorial |
7593211 | 2681 | 1061.77 | 11 | 4778 | Consol Linehaul | Rockwell Automation | Rockwell International | Truckload | Transportation |
Here i want same shipment has different rates with different services but same mile, weight and Pallets.
So i need sun up the Amount of distinct shipment, miles, Weight and Pallets.
My Expressions like this:
Cost per miles --> Sum ({<Mode = {'TruckLoad'}> }Amount)/ sum({<Mode = {'TruckLoad'}> }aggr(Miles, Shipment))
Cost per Weight --> Sum (Amount)/Sum(aggr(Weight, Shipment))
Cost per Pallet --> sum(Amount)/sum(aggr(Pallets, Shipment))
Here i have different mode are there. so applied mode condition.
Please anybody suggest
Thanks,
ASHOK
Cost per miles --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 2681 = 1.1595225
Sum ({<Mode = {'TruckLoad'}> }Amount)/Sum ( TOTAL <Shipment>{<Mode = {'TruckLoad'}> } distinct Miles)
Cost per Weight --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 11623 = 0.26745934
Sum (Amount)/Sum ( TOTAL <Shipment> distinct Weight )
Cost per Pallet --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 26 = 119.564615
Sum (Amount)/Sum ( TOTAL <Shipment> distinct Pallet )
We cannot apply distinct on miles, weight or pallets. Because different may have same number of miles then it cannot come sum up the value. so result become wrong.
Please share one example of the same.
Shipment | Miles | Amount | Pallets | Weight | Service | CustomerName | CarrierName | Mode | ChargeGroup |
---|---|---|---|---|---|---|---|---|---|
7457778 | 2681 | 359.11 | 26 | 11623 | LH Fuel Surcharge | Rockwell Automation | ProTrans International | Truckload | Fuel |
7457778 | 2681 | 50 | 26 | 11623 | Stop Off | Rockwell Automation | ProTrans International | Truckload | Accessorial |
7457778 | 2681 | 85.01 | 26 | 11623 | LTL Fuel Surcharge | Rockwell Automation | ProTrans International | Truckload | Fuel |
7457778 | 2681 | 1875.34 | 26 | 11623 | Consol Linehaul | Rockwell Automation | ProTrans International | Truckload | Transportation |
7457778 | 2681 | 739.22 | 26 | 11623 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7501508 | 4030 | 18.01 | 12 | 2641 | LTL Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7501508 | 4030 | 527.58 | 12 | 2641 | Consol Linehaul | Rockwell Automation | Rockwell International | Truckload | Transportation |
7501508 | 4030 | 101.03 | 12 | 2641 | LH Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7501508 | 4030 | 50 | 12 | 2641 | Stop Off | Rockwell Automation | Rockwell International | Truckload | Accessorial |
7501508 | 4030 | 163.74 | 12 | 2641 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7531529 | 2998 | 16.11 | 12 | 1567 | LTL Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7531529 | 2998 | 140.05 | 12 | 1567 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7531529 | 2998 | 107.08 | 12 | 1567 | LH Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7531529 | 2998 | 50 | 12 | 1567 | Stop Off | Rockwell Automation | Rockwell International | Truckload | Accessorial |
7531529 | 2998 | 559.2 | 12 | 1567 | Consol Linehaul | Rockwell Automation | Rockwell International | Truckload | Transportation |
7593211 | 2681 | 38.06 | 11 | 4778 | LTL Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7593211 | 2681 | 192.17 | 11 | 4778 | LH Fuel Surcharge | Rockwell Automation | Rockwell International | Truckload | Fuel |
7593211 | 2681 | 346 | 11 | 4778 | Consol Pickup | Rockwell Automation | Rockwell International | Truckload | Transportation |
7593211 | 2681 | 50 | 11 | 4778 | Stop Off | Rockwell Automation | Rockwell International | Truckload | Accessorial |
7593211 | 2681 | 1061.77 | 11 | 4778 | Consol Linehaul | Rockwell Automation | Rockwell International | Truckload | Transportation |
Here suppose if we take distinct of miles or weight or pallets you will get following
miles - 2681, 4030 and 2998
Pallets - 26,12 and 11
Weight - 11623,2641 and 4778, right.
Then
if we calculate the Cost per mile for all shipments as follows
CPM = Sum(Amount)/ Sum(Miles) (i.e sum up amount of all rows / sum up individual shipments miles )
= (359.11+50+85.01+1875.34+739.22+18.01+527.58+101.03+50+163.74+16.11+140.05+107.08+50+559.2+38.06+192.17+346+50+1061.77)/(2681+4030 +2998 + 2681) = 0.526995
= 6529.48/12390 = 0.526995
but as per you query we get following result
CPM = Sum(Amount)/ Sum(Miles)
=
(359.11+50+85.01+1875.34+739.22+18.01+527.58+101.03+50+163.74+16.11+140.05+107.08+50+559.2+38.06+192.17+346+50+1061.77)/(2681+4030 +2998) => 0.526995
= 6529.48/9709 => 0.672518 but this wrong right?
hope you will understand what i'm expecting.
anyway thanks once again.
Thanks
ASHOK
Have you considered creating a dimensional table for Shipments with its attributes like Miles, Pallets, Weight, one record per shipment, and linking this table to your cost fact table by Shipment ID?
This should return correct aggregates without the need for advanced aggregation.