Complex AGGR

[dambeldor]

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.

[rohit214]

hi

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

Thanks

Rohit

[deepakqlikview_123]

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

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

thanks,

Deepak

[rohit214]

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

[deepakqlikview_123]

Thanks Rohit

[its_anandrjs]

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

[dambeldor]

Hello-

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

[MK_QSL]

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)