Getting Max Year/Month within aggr

[zagzebski]

I have the following expression that works fine:

sum(aggr(
if(sum([Member Count])<1 and sum([Claim Amt])>0,
      sum([Claim Amt]),0),
           [Year Month],[Member ID]))

However I want the aggregation to only be performed on the Member ID and the MAX of the Year Month each Member ID, not for every Year Month.

Any help is appreciated.

Steve

[#2]

My 2cents

You can create a flag in the backend to find out whats the max Year-month for each Memeber ID and on the front end you can just use that flag in your if statement.

The reason i suggested this is calculations precalculated on the backend will use less memory, instead of calculating it on the front end.

Just a suggestion

Thanks

AJ

[zagzebski]

Thanks Ajay. A good suggestion long term. Any ideas how to make this work in the short term with in the expression?

Steve

[MK_QSL · Accepted solution]

May be something like below...

sum(aggr(

if(sum([Member Count])<1 and sum([Claim Amt])>0,

      sum({<[Year Month = {'$(=Max([Year Month]))'}]>}[Claim Amt]),0),

           [Year Month],[Member ID]))

[zagzebski]

Awesome thanks!

Steve