Ranking - ignoring a dimension in the rank but not the analysis

[#1]

I am trying to determine the Rank (highest number of bookings to lowest) of a MARKET by an AGENT.

I only want to see the AGENT I have selected (filtered the selection by) and it's ranking compared with it's peer AGENTS.

Here is the XL View :

Agency	Market	AGENT	No. of Bookings	Rank (incorrect)	Rank (correct)
Agency X	AAA-BBB	111	559	1	2
Agency X	ABB-CDD	111	160	2	4
Agency X	CCC-EEE	111	154	3	1

Base Data :

Agency	Market	AGENT	No. of Bookings	Market Rank
Agency X	AAA-BBB	111	559	2
Agency X	AAA-BBB	222	600	1
Agency X	AAA-BBB	333	400	3
Agency X	ABB-CDD	111	160	4
Agency X	ABB-CDD	222	500	3
Agency X	ABB-CDD	333	700	2
Agency X	ABB-CDD	444	800	1
Agency X	CCC-EEE	111	154	1
Agency X	CCC-EEE	333	20	2

I'm using the following Rank function but it doesn't work (Rank (incorrect) column)

=num(Rank(

Aggr(

sum({$<[AGENT]= >} [Booking ID])

,

[Agency],

[Market]

)

,4))

eg. Ignore the AGENT in the selection for this analysis and Rank by the number of bookings over the Agency and Market.

If I bring in the AGENT as a dimension it works but I don't want to see the AGENT in the dimension, as it is restricted by the selection filter and I only therefore want to show the rankings for that AGENT against it's Markets.

Any ideas out there!

[swuehl]

Have you tried adding your Agent to the aggr() dimensions?

=num(Rank(

Aggr(

sum({$<[AGENT]= >} [Booking ID])

,

[Agency],

[Market],

[AGENT]

)

,4))

[#3]

Yes - and that would work if I didn't filter the analysis Chart by the AGENT. When this filter is applied then the RANK is 1 for every Market as I've restricted the Chart to this single Agent.

It's not straightforward I know !

[swuehl · Accepted solution]

Hm, isn't that what you are looking for?

[#5]

THANKYOU!

With the re-ordering of the placement of the Rank and Aggr, this works. What does the 'only' command do - is that a simple de-duplication?