Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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 :

AgencyMarketAGENTNo. of BookingsRank (incorrect)Rank (correct)
Agency XAAA-BBB11155912
Agency XABB-CDD11116024
Agency XCCC-EEE11115431

Base Data :

AgencyMarketAGENTNo. of BookingsMarket Rank
Agency XAAA-BBB1115592
Agency XAAA-BBB2226001
Agency XAAA-BBB3334003
Agency XABB-CDD1111604
Agency XABB-CDD2225003
Agency XABB-CDD3337002
Agency XABB-CDD4448001
Agency XCCC-EEE1111541
Agency XCCC-EEE333202

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!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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

View solution in original post

4 Replies
Highlighted
MVP
MVP

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

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

=num(Rank(

Aggr(

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

,

[Agency],

[Market],

[AGENT]

)

,4))

Highlighted
Not applicable

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

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 !

Highlighted
MVP
MVP

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

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

View solution in original post

Highlighted
Not applicable

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

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?