Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
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
Thanks Ajay. A good suggestion long term. Any ideas how to make this work in the short term with in the expression?
Steve
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]))
Awesome thanks!
Steve