topic Re: Getting Max Year/Month within aggr in QlikView

Getting Max Year/Month within aggr

zagzebski — Wed, 12 Feb 2014 18:36:54 GMT

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

Re: Getting Max Year/Month within aggr

Wed, 12 Feb 2014 18:42:56 GMT

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

Re: Getting Max Year/Month within aggr

zagzebski — Wed, 12 Feb 2014 18:48:40 GMT

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

Steve

Re: Getting Max Year/Month within aggr

MK_QSL — Wed, 12 Feb 2014 19:34:04 GMT

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]))

Re: Getting Max Year/Month within aggr

zagzebski — Wed, 12 Feb 2014 19:41:16 GMT

Awesome thanks!

Steve