topic Re: RANK & AGGR in QlikView

RANK & AGGR

ilanbaruch — Tue, 05 Dec 2017 10:03:46 GMT

hi all,

i want to use rank employees in a ctegory and a sub categry i.e

rank an employee in all the region

and rank same empoyee in his department

of course two different expressions

any ideas?

advansed thanks

Re: RANK & AGGR

shraddha_g — Tue, 05 Dec 2017 10:10:39 GMT

use rank().

If possible provide sample data and expected output to help you better.

Re: RANK & AGGR

sunny_talwar — Tue, 05 Dec 2017 12:35:43 GMT

Do you have a sample or sample data you can share. Also, it would be helpful if you can share your expected output from the data.

Re: RANK & AGGR

ilanbaruch — Tue, 05 Dec 2017 13:28:58 GMT

hi ,

please see attached table below,

i want to Rank() every agent in company level

and in his region level

advanced thanks

COMPANY	REGION	AGENT	AMOUNT
COMPNY_A	REGION_A	AGENT 1	500
COMPNY_A	REGION_A	AGENT 2	320
COMPNY_A	REGION_A	AGENT 3	140
COMPNY_A	REGION_A	AGENT 4	700
COMPNY_A	REGION_A	AGENT 5	1100
COMPNY_A	REGION_B	AGENT 6	230
COMPNY_A	REGION_B	AGENT 7	470
COMPNY_A	REGION_B	AGENT 8	2300
COMPNY_A	REGION_B	AGENT 9	70
COMPNY_A	REGION_B	AGENT 10	620

Re: RANK & AGGR

ilanbaruch — Tue, 05 Dec 2017 13:30:44 GMT

hi ,

please see attached table below,

i want to Rank() every agent by amount in company level

and in his region level

advanced thanks

COMPANY	REGION	AGENT	AMOUNT
COMPNY_A	REGION_A	AGENT 1	500
COMPNY_A	REGION_A	AGENT 2	320
COMPNY_A	REGION_A	AGENT 3	140
COMPNY_A	REGION_A	AGENT 4	700
COMPNY_A	REGION_A	AGENT 5	1100
COMPNY_A	REGION_B	AGENT 6	230
COMPNY_A	REGION_B	AGENT 7	470
COMPNY_A	REGION_B	AGENT 8	2300
COMPNY_A	REGION_B	AGENT 9	70
COMPNY_A	REGION_B	AGENT 10	620

Re: RANK & AGGR

sunny_talwar — Tue, 05 Dec 2017 13:32:00 GMT

Have you tried this

Rank(Sum(AMOUNT))

Re: RANK & AGGR

ilanbaruch — Tue, 05 Dec 2017 13:50:31 GMT

yes.

also with this: Aggr(Rank(Sum(amount),1,1),agent)

but i need this to run in the script, and in UI to be presented as a dimension,

and not to be influenced from user selections

or maybe it can be solved with set analysis in UI ?

apriciate your swift reply

Re: RANK & AGGR

sunny_talwar — Tue, 05 Dec 2017 13:54:08 GMT

Try this

Table:

LOAD * INLINE [

COMPANY, REGION, AGENT, AMOUNT

COMPNY_A, REGION_A, AGENT 1, 500

COMPNY_A, REGION_A, AGENT 2, 320

COMPNY_A, REGION_A, AGENT 3, 140

COMPNY_A, REGION_A, AGENT 4, 700

COMPNY_A, REGION_A, AGENT 5, 1100

COMPNY_A, REGION_B, AGENT 6, 230

COMPNY_A, REGION_B, AGENT 7, 470

COMPNY_A, REGION_B, AGENT 8, 2300

COMPNY_A, REGION_B, AGENT 9, 70

COMPNY_A, REGION_B, AGENT 10, 620

];

FinalTable:

LOAD *,

If(COMPANY = Previous(COMPANY) and REGION = Previous(REGION), RangeSum(Peek('RANK'), 1), 1) as RANK

Resident Table

Order By COMPANY, REGION, AMOUNT desc;

drop Table Table;

Re: RANK & AGGR

ilanbaruch — Wed, 06 Dec 2017 12:50:28 GMT

hi ,

i have this expression that is working well

Aggr(Rank(Count(distinct [OrderID]),1,1),[Employee])

i need it to ignore user selection, is it possible?

Re: RANK & AGGR

sunny_talwar — Wed, 06 Dec 2017 12:54:27 GMT

May be this

Only({1} Aggr(Rank(Count({1}distinct [OrderID]),1,1),[Employee]))

or this

Aggr(Rank(Count({1}distinct [OrderID]),1,1),[Employee])