7 Replies Latest reply on Aug 25, 2013 10:26 PM by ruth.wyer

# Rank only if criteria is met

Hi,

On the attached workbook I have a rank according to results.  My user wishes to see this raw ranking alongside an adjusted rank that only ranks agents where Criteria = 1.  i.e. on the attached, Agent 8 would rank 1, agent 7 would rank 7 etc.  Can anyone steer me in the right direction for how to do this?

Thanks

Ruth

EDIT:  Just realised I can't use INDEX() as the ranking will change if someone filters the view.  I need the Ranks to remain the same regardless of what the user filters.  so If someone filtered only on Agent 10, they would see RANK = 9 and Adjusted Rank=6.  is this possible?

• ###### 1. Re: Rank only if criteria is met

Ruth,

See if the attached workbook helps.  It uses a couple of table calcs to demonstrate how it might be done.  You can tweak the calculations if needed to fit your exact requirements.

I'd be happy to answer any questions you might have!

Regards,

Joshua

• ###### 2. Re: Rank only if criteria is met

Thanks for your reply Joshua.  The 'Adjusted Rank' seems to return the same value against mulitple people so that would not be suitable.  I'm sorry for the confusion but i added an EDIT to my original post when I realised that the RANK was changing when I filter the agents.  I need a calculation that compares to the total count of agents where Criteria=1, regardless of whether they appear in the view.  i.e. Adjusted rank would not change when agent filtering is used.  The reason for this is that the agents will only ever see themselves when they use this report and need to see their raw rank and adjusted rank against other agents that are not in their view.  Does that make sense?

• ###### 3. Re: Re: Rank only if criteria is met

Ruth,

See the attached workbook here.  I've clarified the final table calc which should remove any duplicate rankings.  Also, I've added some table calc filters which will allow you to filter the view without impacting the rank function.  When table calculations are used as filters, they are processed after other table calculations.  That way those table calculations aren't impacted by the filter.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 4. Re: Re: Rank only if criteria is met

That's perfect, Joshua.  Thank you.  I can't get it to work on my workbook, though.  The ranks are fine when all agents are selected, however, everyone drops down a level (i.e. agent ranked #1 changes to #2 etc) whenever i filter on agent or any other criteria.  I have done my calculations exactly the same as yours so there is obviously other things in my document (which has a lot more dimensions and measures) that is affecting it. My agents are also filtered by team (according to hierarchy dimension), rather than individual agents. I have a lot of AGG pills on the columns shelf.  I've tried removing them but still have the same issue.   Unfortunately, I can't upload the workbook as it contains highly sensitive information.  Do you have any idea what might cause everything to start from #2 when any filtering is applied?

Thanks

Ruth

• ###### 5. Re: Re: Rank only if criteria is met

Hmmm.... not right off.  But would it be possible to at least post a screenshot including Rows, Columns, Filters, and Marks so I can see how everything is arranged.   You can blur out any sensitive data as needed.

Regards,

Joshua

• ###### 6. Re: Rank only if criteria is met

Ruth,

When you look at the fields on the Filters shelf, do they all have the little triangle icon representing a table calculation? (the exception to this might be Measure Names)

For example, The Cirteria filter is not a direct filter on the Criteria dimension, but a calculated filter that looks like:

LOOKUP(ATTR(Criteria), 0)

It is essential to use table calculations as filters so the Rank function is performed prior to the filtering.

As to why everyone drops a rank when filtering -- I'm still not sure after looking at the screenshots.  I can duplicate it in my workbook if I filter out Agent 8 (Rank 1), then of course the ranking starts at 2 for Agent 6.

But Agent 8 is always Rank 1, and Agent 6 is always 2 no mater what filters I select / deselect.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 7. Re: Rank only if criteria is met

Ah!  No they don't.  I will change that and see how I go.  Thanks Joshua.