topic Re: Complex AGGR in QlikView

Complex AGGR

dambeldor — Wed, 16 Jul 2014 10:09:06 GMT

Hello -

I have this table:

place, salesperson-id, sale

A,111,40

A,222,30

A,333,90

B,444,56
B,555,124

B,666,50

B,777,300

B,888,300

C,999,400

C,101,300

C,202,420

C,303,390

D,404,14

D,505,30

D,606,12

D,707,20

I would like to create a chart with dimension- place,

and the expression will be the sum of the 2 highest values from sale ,

in this case it should look like this:

dimenssion, expression

A, 130

B, 600

C,820

D,50

I tried a lot with the functions AGGR and RANK

but I could not get the result .

Please if someone can help me I'd appreciate it very much thanks.

Re: Complex AGGR

rohit214 — Wed, 16 Jul 2014 10:49:16 GMT

Use Sum({<Place= {"=rank(sum( Sales), 4)<= 2"}>} Sales)

Thanks

Rohit

Re: Complex AGGR

deepakqlikview_123 — Wed, 16 Jul 2014 11:17:22 GMT

Sum({<Place= {"=rank(sum( Sales), 4)<= 2"}>} Sales)

Hi rohit can you please tell me what is the use of 4 here.

thanks,

Deepak

Re: Complex AGGR

rohit214 — Wed, 16 Jul 2014 11:25:19 GMT

Hi,

Using 4

Lowest rank on first row, then incremented by one for each row.

for more info please check in help file.

Thanks

Rohit

Re: Complex AGGR

deepakqlikview_123 — Wed, 16 Jul 2014 11:30:27 GMT

Thanks Rohit

Re: Complex AGGR

its_anandrjs — Wed, 16 Jul 2014 11:41:33 GMT

Try like this

Dim1:- place

Expr:- Sum(Aggr(if( Rank(sum(sale)) <= 2 ,sum(sale)),place, [salesperson-id]))

And on this post

Complex aggr | Qlik Community

Re: Complex AGGR

dambeldor — Thu, 17 Jul 2014 06:09:26 GMT

Hello-

I'm using the fields with the name is not in English so it's not work.
Thanks anyway

Re: Complex AGGR

MK_QSL — Thu, 17 Jul 2014 20:35:56 GMT

Create a straight table

Dimension

Calculated Dimension

=IF(Aggr(Rank(SUM(sale),4),place,[salesperson-id])<=2, place)

Tick Suppress When Value is NULL

Expression

SUM(sale)