5 Replies Latest reply on Sep 8, 2017 4:15 AM by Stephen Gibbons

    Current/Prior Ranking Question

    Stephen Gibbons

      I have a report that shows 2 columns for rank - rank _current and rank_prior alongside 2 more columns for client and income. The problem I am facing is that rank_current and rank_prior appear to be inextricably linked in that the rank_prior column will only hold the same values as the rank_current column, albeit possibly in a different order. For example...

       

      If we have a table of top 10 clients, then if a row for one of the clients has a rank_prior that is outside the top 10 then it won't show. I need the rank_prior object to be independent of the rank_current column and show rankings that may not fall within the range (in this case 1-10) of the rank_current column.

       

      I am using the RANK function and have tried (1) calculation across the table and (2) calculation along the Client dimension.

       

      I hope this makes sense. Apologies for the absence of any attachment - company policy prohibits this.

       

      Thanks.