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
Did u Tried like this ?
Sum (Amount)/(aggr(sum(Weight), Shipment))
check that is it giving correct result .let me know if I am wrong
Hi Manoj,
thanks for quick reply. but it is not working. It doesn't return any value.
Thanks
ASHOK
Please provide sample data with expected output
If its possible, try to map/join Weight, Pallets, Miles into Your transactiontable where you have Amount.
I.ll think shipment will be the Keyfield to use if you for ex use mapping load and applymap.
If they are in the same table there is no need to use aggr.
Hi kushal,
Expected output is like this.
For shipment 7457778
Cost per miles --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 2681 = 1.1595225
Cost per Weight --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 11623 = 0.26745934
Cost per Pallet --> (359.11 + 50 + 85.01+ 1875.34 + 739.22) / 26 = 119.564615
Thanks,
ASHOK
Hi ASHOK,
May be as in attached file?
Regards,
Andrey
try if this could be an alternative:
Sum ({<Mode = {'TruckLoad'}> }Amount)/ only({<Mode = {'TruckLoad'}> }Miles)
Hi Andrey,
Thanks for your reply.
As per your attachment, we are able show the shipment wise cost per mile. but suppose if we select a particular customer, then sum up the all individual shipment's miles. i.e here we have 2 shipments. so miles should be 2681+2681 = 5362. if we take distinct miles it goes wrong.
Thanks,
ASHOK
If to analyze the content of your data, maybe the value "Transportation" in field "ChargeGroup" is a sign of miles? Then Sum(DISTINCT Miles) is replaced by expression Sum({<ChargeGroup={'Transportation'}>} Miles)?
New version in the attached file.