topic Re: aggr top 100 in QlikView

aggr top 100

sr_qlikview — Mon, 13 May 2013 22:40:16 GMT

i have a expression like

AVG( aggr(sum(sales),Client))

i want to get only top 100 clients based on sum(sales)

how to control data with in aggr set ?

Re: aggr top 100

MayilVahanan — Mon, 13 May 2013 22:51:35 GMT

Use dimension limit or rank() function.

Hope it helps

Re: aggr top 100

Sokkorn — Tue, 14 May 2013 02:16:10 GMT

Hi sr_qlikview,

It can be done via:

1. Use Dimension Limit

2. Sum({$<Client = {"=Rank(Aggr(Sum(Sales),Client))<= 100"}>}Sales)

Regards,

Sokkorn

Re: aggr top 100

Tue, 14 May 2013 04:54:30 GMT

try this

take dimension--- client

sales

Expression-- aggr(if(rank(sum(sales))<=100,client),client)

Then output comes fine.

Re: aggr top 100

Tue, 14 May 2013 04:58:22 GMT

example

LOAD * INLINE [

client, sales

a, 10

b, 30

c, 20

d, 5

e, 40

];

Dimension-- client

sales

expression-- aggr(if(rank(sum(sales))<3,client),client)

Then output comes like this

client	sales	Top two client
b	30	b
e	40	e

Re: aggr top 100

narender123 — Tue, 14 May 2013 06:50:38 GMT

Hi,

1)In dimention limit select show only box select * largest

* 100

2)In expression tab select Sum of rows

Thanks.

Narender