topic Re: Help Optimizing an expression with AGGR in App Development

Help Optimizing an expression with AGGR

akash_durai1507 — Mon, 15 Dec 2025 20:03:33 GMT

Sum(Aggr(max( {<GLaccountCode={'121001','117000'}>}If((YearEndDate>=NetDueDate and [Clearing Date]>YearEndDate and Arrears>0) or AROverdue='Overdue',ARAmountLC)
),YearFilter,ARKey))

Above is my expression i use on charts and its very slow when there are no filters.

I'm using an As-of table and when i apply filters inside the if-clause, the amount is duplicated(guessing its due to many-to-many joins) and I'm forced to use Max() inside the AGGR() function.

My data model looks like below,

TIA,

Re: Help Optimizing an expression with AGGR

rwunderlich — Tue, 16 Dec 2025 00:15:45 GMT

When you built the link table, did you apply the DISTINCT operator?

-Rob

Re: Help Optimizing an expression with AGGR

akash_durai1507 — Tue, 16 Dec 2025 10:13:05 GMT

Hi Rob!

Thanks for the reply.

Yes, i did apply distinct operator while loading the link table.

Here is the snapshop of the code,

Re: Help Optimizing an expression with AGGR

Chanty4u — Tue, 16 Dec 2025 16:32:05 GMT

Try this in your load script

AR_Snapshot:

LOAD

YearEndDate,

ARKey,

Max(

If(

(YearEndDate>=NetDueDate

and [Clearing Date]>YearEndDate

and Arrears>0)

or AROverdue='Overdue',

ARAmountLC

)

) as ARAmount_AsOf

Resident AsOfTable

Group By YearEndDate, ARKey;

Re: Help Optimizing an expression with AGGR

marcus_sommer — Thu, 18 Dec 2025 14:04:03 GMT

I suggest to make the date-checks already within the data-model an refer then within the expressions to these flags, for example with an approach like:

sign(date1 - date2) as flagX

This may need some measurements to bring the various dates together and should be used to consider to rebuild the data-model because link-table approaches may end in a working data-model but from a performance point of view are they usually not the best choice.