topic aggr in set analysis in QlikView

aggr in set analysis

Thu, 20 Jan 2011 12:56:54 GMT

H i all

I want to display the rows that has max value grouped by id

Data

Id , dep , val

1 a 1

1 b 2

1 c 3 expected Result

2 d 4 1 c 3

2 e 5 2 f 6

2 f 6 3 I 9

3 g 7

3 h 8

3 I 9

Aggr(max(dal),id) this returns 3,6,9

max( {< val = { 3,6,9 } >} val) This gives the correct result in straight table

but if replace the 3,6,9 with Aggr(max(val),id) then the result is not what is expected

max( {< val = { '=aggr(max(val),id)'} >} val)

1 a 1

2 d 4

3 g 7

Can we do something to the expression to make this work?

aggr in set analysis

fernandotoledo — Thu, 20 Jan 2011 13:50:49 GMT

See the example below, you will see that your idea is correct, but there is something missing in your set analisys expression:

http://community.qlik.com/media/p/82604.aspx

Best regards

Fernando

aggr in set analysis

Fri, 21 Jan 2011 05:12:33 GMT

Normal 0 false false false EN-US X-NONE X-NONE

Thanks for the reply Fernando D. T

Could you please tell me what's wrong with the following expression

Normal 0 false false false EN-US X-NONE X-NONE

Dimensions are id and dep

expression is = max( {1< val = { '=aggr(max(val),id)' } >} val )

Thanks

Thiru

aggr in set analysis

spsrk_84 — Fri, 21 Jan 2011 08:35:21 GMT

Hi,

In a straight table add ID dimension and also the below calculated dimension

aggr((if(rank(sum(val))='1',dep)),Id,dep)

In the expression just give Sum(Val)...

Just try this i hope it will give required result

Regards,

Ajay

aggr in set analysis

Fri, 21 Jan 2011 09:44:44 GMT

Normal 0 false false false EN-US X-NONE X-NONE

Thanks ajay

It can be done by restricting the dimensions or by using if in expression

= if( val = aggr( nodistinct max(val),id) , val )

But Question is

If I type 3, 6, 9 in set expression it works fine

Aggr(max(val),id) returns 3,6,9 then

Why this expression doesn't Work?

= max( {1< val = { '=aggr(max(val),id)' } >} val )

Thanks

Thiru

aggr in set analysis

mongolu — Fri, 21 Jan 2011 10:56:26 GMT

try this

MAX({< val = {"=val=Aggr(nodistinct max(val),Id)"} >} val)

aggr in set analysis

Sat, 22 Jan 2011 04:51:29 GMT

Thanks Mihai Petcu

It does not work

aggr in set analysis

mongolu — Sat, 22 Jan 2011 05:14:04 GMT

oh. but it does.

QlikView 9 SR 6.

you?

aggr in set analysis

Sat, 22 Jan 2011 06:20:27 GMT

Sorry Mihai Petcu

Thank you so much it works in both Qview 9 and 10

Thiru