Rank() from two dimension Aggr()

[#1]

Dear All,

Please let me know how can get the Rank() from two Dimension Table ( via Aggr() function)

i want to get the Member rank of a specific city like 'MUMBAI'

RANK(aggr(Sum(TRADE_VALUE),CITY,MEMBERID))

Kindly help

[sunny_talwar]

Do you have a sample that you can share?

[swuehl]

In which context are you using this expression?

In a chart using which dimensions?

In a text box?

Rank() should be used within a table, not as an aggregation outside.

= Max( aggr( Rank(Sum(TRADE_VALUE), CITY, MEMBERID))

Max() is just a guess, no idea what you are trying to achieve.

[#4]

i am using in CITY dimensions

[#5]

there is my requirement.

i want to select a Member and want to show CITYWISE, TRADE_VALUE of a Member. But along with the TRADE_VALUE, I also want to know is Rank() CITYWISE

[swuehl]

Ok, within a chart with dimension CITY.

And you select a Member, but want to return the rank compared to all (unselected) members?

Try

= Min( aggr( Rank(Sum({1} TRADE_VALUE), CITY, MEMBERID))

to ignore the Member selection for the rank calculation.

[#7]

becasue each city there are multiple member so according to members turnover on city what is his rank()

[#8]

below is my requirement with sample data

MEMBER	CITY	TOTAL
A	MUMBAI	100
A	PUNE	50
A	DELHI	200
B	DELHI	100
B	PUNE	250
B	MUMBAI	200
B	KOLKATA	100
C	MUMBAI	50
C	DELHI	500

Now when i select Member 'A' i want to get CITY wise TOTAL along with the rank compare to other MEMBERS like

CITY	A-TOTAL	RANK
MUMBAI	100	2
PUNE	50	2
DELHI	200	2
KOLKATA	0	0

Kindly note this is just for a sample purpose. the actual records have more dimensions (YEAR,MONTH,DATE,etc)

[swuehl]

= Min( aggr( Rank(Sum({1} [TOTAL])), CITY, MEMBER))

seems to work for me.

[#10]

Hi,

i think you did not get my requirement, below is sample

MEMBER	CITY	TOTAL
A	MUMBAI	100
A	PUNE	50
A	DELHI	200
B	DELHI	100
B	PUNE	250
B	MUMBAI	200
B	KOLKATA	100
C	MUMBAI	50
C	DELHI	500

In the above case if we see closely then we found in Mumbai,  'A'  MEMBER have '2' Rank , and in PUNE, 'A' have '2' RANK.

So when i want to show CITYWISE TOTAL of 'A' Member, on the same time i want to show his TOTAL RANK with compare to other Member. like 'B' TOTAL is more and 'C' TOTAL is less so at MUMBAI he RANK '2' as on

CITY	A-TOTAL	RANK
MUMBAI	100	2
PUNE	50	2
DELHI	200	2
KOLKATA	0	0

here RANK() should me caluclated on the basis on MEMBERS respective CITY .