Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ilanbaruch
Specialist
Specialist

RANK & AGGR

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

9 Replies
shraddha_g
Partner - Master III
Partner - Master III

use rank().

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

sunny_talwar

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.

ilanbaruch
Specialist
Specialist
Author

hi ,

please see attached table below,

i want to Rank() every agent in company level

and in his region level

advanced thanks

    

COMPANYREGIONAGENTAMOUNT
COMPNY_AREGION_AAGENT 1500
COMPNY_AREGION_AAGENT 2320
COMPNY_AREGION_AAGENT 3140
COMPNY_AREGION_AAGENT 4700
COMPNY_AREGION_AAGENT 51100
COMPNY_AREGION_BAGENT 6230
COMPNY_AREGION_BAGENT 7470
COMPNY_AREGION_BAGENT 82300
COMPNY_AREGION_BAGENT 970
COMPNY_AREGION_BAGENT 10620
ilanbaruch
Specialist
Specialist
Author

hi ,

please see attached table below,

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

and in his region level

advanced thanks

    

COMPANYREGIONAGENTAMOUNT
COMPNY_AREGION_AAGENT 1500
COMPNY_AREGION_AAGENT 2320
COMPNY_AREGION_AAGENT 3140
COMPNY_AREGION_AAGENT 4700
COMPNY_AREGION_AAGENT 51100
COMPNY_AREGION_BAGENT 6230
COMPNY_AREGION_BAGENT 7470
COMPNY_AREGION_BAGENT 82300
COMPNY_AREGION_BAGENT 970
COMPNY_AREGION_BAGENT 10620
sunny_talwar

Have you tried this

Rank(Sum(AMOUNT))

ilanbaruch
Specialist
Specialist
Author

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

sunny_talwar

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;

ilanbaruch
Specialist
Specialist
Author

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?

sunny_talwar

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])