Dashboard slowdown when i using the Aggr function with sum function

[ashokpaladugula]

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

jagan‌adamdavi3s‌awhitfield‌hic‌vinieme12‌

[#2]

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

[ashokpaladugula]

Hi Manoj,

thanks for quick reply. but it is not working. It doesn't return any value.

Thanks

ASHOK

[Kushal_Chawda]

Please provide sample data with expected output

[stabben23]

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.

[ashokpaladugula]

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

[ahaahaaha]

Hi ASHOK,

May be as in attached file?

Regards,

Andrey

[stabben23]

try if this could be an alternative:

Sum ({<Mode = {'TruckLoad'}> }Amount)/ only({<Mode = {'TruckLoad'}> }Miles)

[ashokpaladugula]

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

[ahaahaaha]

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.