topic Help with aggr function in QlikView

Help with aggr function

Tue, 08 Jul 2014 14:32:48 GMT

=aggr(only({<MasterDate = {'$(=date(max(MasterDate)))'},Value = {'$(=max(Value))'}, Name -= {'Team*'}>}Name),Name)

In my app, there are two types of names, team names (begin with 'Team') and individual names (normal names). I'm trying to retrieve the Team with the top 'value' from the most recent date and the Individual with the top 'value' from the most recent date.

This function works fine without the:

Name -= {'Team*'}

But I need to find a way to separate individuals from teams.

Re: Help with aggr function

sasikanth — Tue, 08 Jul 2014 14:35:55 GMT

Try some thing like below

=aggr(only({<MasterDate = {'$(=date(max(MasterDate)))'},Value = {'$(=max(Value))'}, Name ={'*'}- {'Team*'}>}Name),Name)

Re: Help with aggr function

Tue, 08 Jul 2014 14:37:38 GMT

No dice, thanks for the suggestion though.

Re: Help with aggr function

Tue, 08 Jul 2014 14:45:37 GMT

Nevermind. I figured it out.

Re: Help with aggr function

jerem1234 — Tue, 08 Jul 2014 14:45:41 GMT

I think you'll need to add the set analysis to your max value as well. Since you are reducing the Names you want the max value for. For example, the max value (80) is for Team B. But when you want the individuals, you exclude Team B with the set analysis. But the max(Value) will still return the max value (80), but this doesn't match any individual. So try:

=aggr(only({<MasterDate = {'$(=date(max(MasterDate)))'},Value = {'$(=max({<Name -= {'Team*'}>}Value))'}, Name -= {'Team*'}>}Name),Name)

You could also try using firstsortedvalue or rank functions

Hope this helps!