RANK within Multiple Hierarchies

Hi,

I'm trying to use the RANK function within a Module with multiple dimensions with hierarchies.

As explained in Group option in RANK Function by @DavidSmith I've created a Group By list but however when you're on a parent in a hierarchy this 'Group By' line item will result into blank. Changing the summary method to Formula should do the trick, but however Anaplan does not allow for this.

Any suggestions or help with this is greatly appreciated!

Thanks,
Hendrik

Accepted answers

JaredDolich

Okay @HendrikDeCuyper, I think I got the solution.

I went ahead and added the time dimension too. I think that was a follow up question you had. I'll post a note there to come to this question for the answer.

This will look like a lot, but once you do one system module the rest fall into place pretty quickly. Honestly, it took me longer to write this than it did to solve it.

Check it out! Ping me if you want to discuss.

Note: I borrowed material from @Mark_W_Shemaria best practice article on how to identify levels within a hierarchy. Brilliant article. Highly recommend it.

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 DISCO methodology outlined by @DavidSmith 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 @Mark_W_Shemaria best-practice article “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 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 were 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. Check that off our UAT list!

At the most aggregate level, our ranking is also working well here.

Step 3: Create the Dashboard
As a 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.

All comments

AlejandroGomez

Hi @HendrikDeCuyper

(edited) I have double read it and realised that the issue is with the "Group by" item. Could you attach a picture? Would be nice to see the format and formula being used to retrieve the values of the "Group By" Line Item.

Regards,

Alex

Olivehorse

HendrikDeCuyper

Hi @AlejandroGomez

Thanks for reaching out.

If I would be able to select Formula here it would work, however Anaplan gives an error.

I'm thinking of creatively maybe assigining a number to every list and then aggregate this to have a unique number to aggregate by?

This isn't bulletproof however so I hope you have a better proposal!

Thanks again.

Hendrik

JaredDolich

@HendrikDeCuyper

The RANK function can be a bit tricky to work with at first.

@AlejandroGomez has the right solution - but the GROUP BY that he's referring to is the last parameter in the RANK Function.

You can try to use the summary GROUP BY but I think you'll get erroneous results (or error as you found).

You can achieve virtually any rank within the structured list.

Some suggestions first though.

1. Use a system module to hold all your GROUP BYs. This is best practice using the D.I.S.C.O. method, by @DavidSmith. You want to avoid having Anaplan recalculate the same formula in modules that use more than one dimension.

2. Create as many of the GROUP BY lists as needed.

3. Create a filter that only shows the lowest level of your structured hierarchy.

First create the system Module, a module that contains only one dimensions, in this case a product hierarchy.

Notice I concatenated the GROUP BYs based on the level I want a rank.

Here's the GROUP BY list in grid mode.

Here's the rank using a normal RANK function not including any of the other parameters. Notice how some are tied.

We used the filter on the lowest level "P3 Only?" - this avoids having to use the "select levels" which tend to give odd formatting when used.

Now we'll create a forced rank using the "SEQUENTIAL" parameter.

Here's a Rank from the lowest level to it's Parent. The rank resets after each style-color.

here's a rank to the style (either shirts or sweaters). The rank starts over at the style level.

AlejandroGomez

very detailed answer @JaredDolich

I hope it helps @HendrikDeCuyper

Cheers!

HendrikDeCuyper

Hi @JaredDolich

Thank you for the effort and reaching out.

I appreciate the elaborate explanation but at the moment do not grasp how this will solve my problem.

As your explanation and solution still only operates within one hierarchy and is not flexible.

Within one module my client wants to be able to rank his customers on several flexible dimensions. Based on the selections in the dimension the RANK() must adapt to show the correct ranking.

Below I've attached a screenshot of the Pivot view.

C5 - Customer Level 3 = Dimension that should be ranked

Segment = Flat List with Top Level

Organic = Flat List with Top Level

Sales Entities = Flat List with Top Level

R1 - Region = Highest Level in the Hierarchy with Top Level

RL1 - Crop = Lowest Level in the Hierarchy with 2 Parent Levels

As you can select the combination of a Top Level 'All Sales Entities' with the Parent 'RL2 - Supercrop' the 'Group By' line item should reflect this selection, however due to the limitation in the summary method for text-formatted line items it will only show the item on the lowest level.

Hope this clarifies the issue more.

Thank you again.

Kind regards,

Hendrik

JaredDolich

@HendrikDeCuyper

Just pulling this post back up to the top so we can continue the conversation!

Regarding the top level - I'm going to take a shot a building this in my sandbox and see if we can't solve this.

My hunch is that it will work because we're not placing the "group by" line item in the output module but rather in the system module.

We just need to figure out what it is we're ranking. I don't see how a top level would matter but we'll give it a shot. Stay tuned for more!

rob_marshall

@HendrikDeCuyper

In the module you shared, it looks to be very sparse, did you consider creating a composite hierarchy with that information? For example, I am not sure, but are all customers in every region and are all products part of every every region and every customer? It might make sense to create a number composite list, this way the overall size will be much smaller and might be able to help you with the RANK() function.

Hope this helps,

Rob

JaredDolich

Okay @HendrikDeCuyper, I think I got the solution.

I went ahead and added the time dimension too. I think that was a follow up question you had. I'll post a note there to come to this question for the answer.

This will look like a lot, but once you do one system module the rest fall into place pretty quickly. Honestly, it took me longer to write this than it did to solve it.

Check it out! Ping me if you want to discuss.

Note: I borrowed material from @Mark_W_Shemaria best practice article on how to identify levels within a hierarchy. Brilliant article. Highly recommend it.

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.