Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
how can only() function works . i want sum of miles for distinct shipments
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')
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))