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

    Rank only if criteria is met

    ruth.wyer

      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
          Joshua Milligan

          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
            ruth.wyer

            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
              Joshua Milligan

              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
                ruth.wyer

                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
                  Joshua Milligan

                  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
                    Joshua Milligan

                    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)

                     

                    Filters.png

                    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
                      ruth.wyer

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