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‌

[Kushal_Chawda]

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 )

[ashokpaladugula]

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.

[Kushal_Chawda]

Please share one example of the same.

[ashokpaladugula]

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

[swuehl]

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.