Dynamic Ranking: Rank a Line Item Based on Selectors
Summary
Level: Advanced
In any line of business, you might be asked to create a dashboard that ranks the sales of your customers based on whatever values are displayed in a two-dimensional grid but based on multiple dimensions chosen from selectors by the user, including top levels. In this example, we will use four dimensions: customer (the dimension to be ranked), product, reporting units, and time. Inspired by this question, we used Calculating Levels in a Hierarchy for Dashboard Filtering.
Assumptions
We will assume that only four dimensions are needed: Customers, Reporting Units, Product, and Time. However, this scenario will work for any number of dimensions and at any level, including time and versions.
Our example uses one year but will work for any number of years. Our time dimension also uses months, quarters, halves, year, and all periods.
Normally, sales transactions would come from the data hub or from a transaction module. In this example we used randomized data and entered them in the reporting module since this part of the user story is not important to the solution.
User Story
As a sales manager responsible for product placement, I need a report that allows me to rank the sales of my customers based on the hierarchy levels I choose from Product, Reporting Units, and Time. My customer hierarchy has three levels, but I only want to see the lowest level in the grid, or level three. I know this is right when I can choose from the three selectors, at any hierarchy level, and my customers are sequentially ranked. In the event of a tie, I want Anaplan to increment the number of customers. Rankings should be ascending, meaning the best customer should be number one.
When I’m at the lowest level of the hierarchies the grid should look like this:
When I’m at the highest level of the hierarchies, the grid should look like this:
Customer Lists
There are three customer lists that include a top level.
Product Lists
There are three product lists that include a top level.
Reporting Unit (regions) List
There is one list for reporting units that includes a top level.
Time
Time uses one year and is by month. Included are quarters, halves, year, year-to-date, and all periods.
Solution
Our ability to rank dynamically is solved by using the group by property in the RANK function. We will need to determine a method for calculating a group by value based on the values selected by the user. Normally, the RANK function is fixed to the dimensions in the grid, but in our case, we need it to change as the user changes the hierarchy levels.
This solution will heavily use the D.I.S.C.O. methodology and will require a system module for every dimension. In these modules, we need to calculate a unique value at every level of the hierarchy and pass that value to our group by line item.
Lastly, we will need a reporting module and a respective dashboard to display the results.
Summary of Steps
- Create System Modules for all the dimensions, including time. Create a single line item that holds all the unique values of each list item at every level of the hierarchy.
- In the reporting module create a line item that concatenates the chosen hierarchy levels and use this line item as the group by property in the RANK function.
- Create a friendly dashboard to accommodate this report.
Step 1: System Modules
This step is the most tedious, but once created it can be reused many times. For reference, please read Calculating Levels in a Hierarchy for Dashboard Filtering. We will be using the same reasoning here but with different logic to accommodate our group by attribute.
Our strategy is to create a line item that can detect what level of the hierarchy we are on. Once we know that, we can calculate a text line item that is assigned the level of that hierarchy.
SYS01 P3 Product: Product Dimension
With the spirit of the PLANS methodology, we have broken up all the formulas into their component parts. We start with the list format, convert it to text, assign a hierarchy value, then build our group by property.
This is also one of the very few times where a subsidiary view is helpful. You will see a few line items with this applied.
Note: To help simplify this article, we will assume the same method to be applied to the Reporting Units dimension, so we will only show the product system module build-out here.
P3 Value Ratio = P2 Value / Denominator
P2 Value Ratio = P1 Value / Denominator
The most important line items here are “P3 Value” and “Text to Use”. P3 value has a numeric value at each hierarchy level. We’ll need that to assign a text value to the Text to Use line item. The “Text to Use” line item will be our lookup value for this dimension, and we will concatenate with the other “Text to Use” line items from the other dimensions to create an overall Group By Text formatted line item.
SYS03 Time Selected:
The time system module is built almost the same as the standard lists, except for the summary column.
Month value ratio = Quarter value / denominator
Quarter value ratio = Half value / denominator
Half value ratio = Year value / denominator
Year value ratio = All Period value / denominator
SYS04 C3 Customer Location
Our customer system module is rather simple. We just need one line item that contains a Boolean to detect the customer level only. Set the line item = TRUE. We will use this to filter our grid in the reporting module.
Step 2: Create the Group by line item and assign it to the RANK function
Now that we have all our “Text to Use” line items, we need to concatenate them together to form a group by attribute text line item we can use in the RANK function. As mentioned earlier, we made a shortcut by entering the sales directly into our reporting module. Normally, these sales would come from a data hub or transaction module within the model or from an import process.
Group by =
'SYS01 P3 Product'.Text to Use & " | " & 'SYS02 Reporting Units'.Text to Use & " | " & 'SYS03 Time Selected'.Text To Use
The group by is simply a concatenation of the “Text to Use” line items from each of the system modules. We then use that line item in our RANK function.
Results:
At the lowest level, our Ranking is working perfectly! Notice how the Group by line item reflects the choices made by the user in the selectors. We also have sequential ranks.
At the most aggregate level, our ranking is also working well here.
Step 3: Create the Dashboard
As the last step, we want to allow the sales manager the ability to rank his/her customers. With a few clicks, we can create a simple dashboard. Don’t forget to turn on sync.
Conclusion
As a sales manager, you will most likely be interested in some type of ranking of your customers, and you aren’t going to be interested in jumping between dashboards when you want to change dimensions. In this example, we have consolidated the hierarchies in the respective system modules and placed them into our reporting module where we could use just one RANK formula. Our RANK formula dynamically changes as the user changes the hierarchy dimensions.
Comments
-
This particular use case could also be solved by just using Sort - Descending on the dashboard, which would make it also easier for the end-user to see the ranking. Or do you specifically want to keep the order of customers as preset by the list?
Yet it's a good basis if you want to dynamically switch between Ascending & Descending, have positive and negative values mixed or if want to show only a subset (like Top 10, Top 15, Worst 10 etc.). Additional line items could then allow the end-user to change the ranking mode or filters.
0 -
Nicely worked @JaredDolich - i've used a similar methodology (text to use) where you've got calculations at L4 level which sum to L3, but they don't sum to L2 and you need to run calculation at L3 (mix/rate fun here!) to create a nicely visual output.
@ChrisWeiss something to be converted to a recipe maybe?
1 -
Well Done! Thank you
0 -
Hello! Is there a way to use this functionality if you can't dimension the modules by multiple dimensions (space constraint)?
Right now the module(Dimensionless) has line item filters that are connected with another system module. IS there a way to do dynamic ranking in such a scenario?0
