RANK & AGGR

[ilanbaruch]

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

[shraddha_g]

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]

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

[ilanbaruch]

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

[sunny_talwar]

Have you tried this

Rank(Sum(AMOUNT))

[ilanbaruch]

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]

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