topic aggr function slows down my app in QlikView

aggr function slows down my app

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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‌rwunderlich adamdavi3s‌swuehl stevedark loveisfail

Re: aggr function slows down my app

swuehl — Tue, 11 Apr 2017 12:29:15 GMT

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)

Re: aggr function slows down my app

Anonymous — Tue, 11 Apr 2017 12:40:40 GMT

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.

Re: aggr function slows down my app

Anonymous — Tue, 11 Apr 2017 12:41:32 GMT

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

Re: aggr function slows down my app

sunny_talwar — Tue, 11 Apr 2017 13:04:31 GMT

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')

Re: aggr function slows down my app

swuehl — Tue, 11 Apr 2017 13:19:45 GMT

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))