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

Tagged:

Best Answer

Answers

  • 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 

  • Hi @AlejandroGomez 

     

    Thanks for reaching out.

     

    HendrikDeCuyper_0-1581067757425.png

     

    HendrikDeCuyper_1-1581067779637.png

     

    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

     

     

  • @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.

    rank000A.png

    Here's the GROUP BY list in grid mode.

    rank000B.png

     

    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.

    rank001.png

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

    rank002.png

     

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

    rank003.png

     

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

    rank004.png

     

  • very detailed answer @JaredDolich  

     

    I hope it helps  @HendrikDeCuyper 

     

    Cheers!

  • 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 

     

     

    HendrikDeCuyper_0-1581414284287.png

     

    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

  • @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!

  • @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

  • Jared,

    I understand in concept what you are doing. I need to do something similar with two lists, one that has 6 level and one that has 4. I am creating the SYS module for the L6 (Segment) list. I have set things up as you have documented but I can't get the Segment L6 value line item to behave as your P3 Value line item. I am getting a value of 6 if the Segment is at the base-level and a value of 1 at all other levels.

    I can't get my head wrapped around how Denominator is even being used in this formula since there is no reference to it in the formula for any of the "value" line items. What needs to be done to have the Ratio utilize the Denominator line item?

     

    thanks,

     

    Anthony

  • Nevermind, I figured it out.
    I haven't used the "Ratio" summary method much and I see where you set the formula for the Ratio Calculation.

  • Dear Jared, 

    many thanks for such a brilliant solution! i have used the method and it seems to work properly)

     

    but it would be great to have the RANK function with the option to rank each hierarchy level automatically some day, i think.

    may be some day the RANK will get the sixth argument...

     

    Danil

     

  • Hey @JaredDolich,

    Have you found a way to rank values independent of time (in a module using Time as a dimension)? Some models from the App Hub use custom time as a workaround, but I'm curious if there's a way to rank values across all time periods.

  • Great question @DavidEdwards 

    I have to admit, I'm stumped on this one. Rank appears to disregard the group by when the time period changes. Meaning it resets, even if I use all time period. I even tried using a TRUE boolean. I'm surprised I never noticed this.

    Give me some time to research this - love puzzles.

     

  • may i know how to setup the formula on column Rank to Parent

  • @HendrikDeCuyper I came here to say you are a genius! This was very helpful.

    Thank you for sharing!