aggr function slows down my app

[Anonymous]

Hi team,

can any one help me regarding aggr function. Basically after writing the function drastically my application performance slows down. 

my statements are Amount/(aggr(mile, shipment)), Amount/(aggr(Weight, shipment)), Amount/(aggr(Pieces, shipment))

and records size is 5.5M.

My requirement is basically , mile particular to single shipment. From below table i want to sum up all amounts and miles regarding to particluar unique shipment

so i wrote Sum(Amount)/aggr(Miles, Shipment).

Miles	Cost Type	Amount	Shipments
500	Fuel	100	S1
500	Transportation	1000	S1
600	Transportation	2000	S2
600	Fuel	200	S2
500	Fuel	50	S1
700	Transportation	1000	S3

Can any one suggest another expression so that may it increase my application perfromance

bmw‌rwunderlichadamdavi3s‌swuehlstevedarkloveisfail

[swuehl]

What's the context (dimensions) of your expressions?

If it's [Shipments], then you could replace the aggr() with only() function:

=Sum(Amount) / Only(Miles)

edit:

Or remodel your data, so that Miles fact is not getting duplicated (for example, create a Shipment / Miles table linked to the Shipment / Cost table)

[Anonymous]

hi stefan,

thanks for immediate response. small modification in expression

num(Sum ({<CAMode = {'TruckLoad'}> }CAAmount)/ sum({<CAMode = {'TruckLoad'}> }aggr(CAMiles, CAShipment)),'$ #,##0.00')

my expression is as above one.

[Anonymous]

how can only() function works . i want sum of miles for distinct shipments

[sunny_talwar]

Are you having Shipment as your dimension in the chart? If you are, then you can try this

Num(Sum({<CAMode = {'TruckLoad'}> }CAAmount)/Only({<CAMode = {'TruckLoad'}>}CAMiles),'$ #,##0.00')

[swuehl]

As a side note (I would still recommend a different data model), you could add the set expression also to the inner aggregation, which should avoid unneccessary calculations for different CAModes:

sum({<CAMode = {'TruckLoad'}> } aggr( Only({<CAMode = {'TruckLoad'} >} CAMiles),  CAShipment))