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;