Dynamic Ranking Logic

1

Since I wrote this article, Centigon Solutions has launche a new component called Dynamic Sort, which allows you to rank data dynamically without a single line of logic. You can get this component for 2008 at http://centigonsolutions.com

I often get questions about dynamic raking within Crystal Xcelsius models so I have put together some lightweight logic that will enable you to rank up to several hundred rows of data during runtime. First we will look at the basic functions needed; then we will combine them into a simple model. Finally, I have provided a more complex model to show you how to generate some more compelling real time analysis using these functions.

Here is a list of functions that I used for my logic within the model. Click on any of the links to see detailed descriptions:
LARGE and SMALL
MATCH
VLOOKUP

With an understanding of how these functions work, let’s combine them to create our dynamic ranking logic.

Source data
1. Index cell: This index cell will be used eventually by a vlookup function once the rank is identified
2. Source Data: This is the original source data that we will rank
3. Unique Identifier. The unique identifier addresses the issue of duplicate values. By carrying the value to the 100 thousandth place, this identifier will ensure that all values in your rank order are unique without affecting the values themselves. This is important because the logic will break down if there are duplicate values.
4. Adjusted: The adjusted values will be the range that we will actually perform the LARGE function. Because we have summed the original value and the unique identifier, we know that there are no duplicate values.

logic
5. Rank Number: We will use this rank number as our “K” within the LARGE function.
6. Rank Lookup: This range uses the LARGE function to find the Nth value (#5) within our Lookup Row (#4).
7. Match Row: Now that we know the Nth value we need to find the absolute row for which this value is located in the source data (#2).
8. Finally, with the absolute row identified (#7), we can perform a VLOOKUP of this absolute row to return any other information we desire. In this case, it was the name.

Now that you have the basics down you can check out this more advanced example and reverse engineer the source file. Let me know how this works for you or if you are able to build on this.

Share.

1 Comment

  1. Hi Ryan,
    I am looking for a similar solution. Can you give some hint on how to solve the below scenario?

    I have a requirement to do dynamic top 5 based on multiple combo box filter selections.

    extending to your above example : consider that there are three more columns Region, State and Year.
    So how to get dynamic ranking based on these columns i.e. Sales Rep, Region, State and Year.

    Thanks,
    Arun

Leave A Reply