Help Optimizing an expression with AGGR

[akash_durai1507]

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,

[rwunderlich]

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

-Rob

[akash_durai1507]

Hi Rob!

Thanks for the reply.

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

Here is the snapshop of the code,

[Chanty4u]

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;

[marcus_sommer]

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.