RANK function options

Need help on Rank function from Anapedia  Result = RANK(Source, Direction, Ties, Include, Group) I have 2 group parameters for rank function (Parent(Product) and Parent(office)) but don't know how to include them both to formula in Group section

Best Answer

  • I wasn't able to include both group parameters in one rank function, but I was able to iterate a rank over Group 1 then Group 2. 

    Result = RANK(Source, Direction, Ties, Include, Group)
    Result2 = RANK(Result, Direction, Ties, Include, Group2)

    When I did this, I had 'Result' as descending, and 'Result2' as ascending to preserve the order of the ranks, i.e. rank '1' would still remain the highest.
    The rank is going to depend on which order you iterate this though, so ranking group2 then group yields different ranks since they are dependent.

Answers

  • Thank you Ryan!
    Good solution
  • Hi Ryan
    Tried this case recently - it doesn't work correctly
    because Result2 on Group2 parameter calcs rank on all values of Result set
    Result set contains few (depends on Group items number) 1st , 2nd, 3d places
    and Result 2 rank all these places as common set

    I made another trick for this case
    I could then post it description if it's interesting
  • Hi Oleg,

    Did you then have to differentiate the ties that occurred? I would be very interested to see what you came up with.
  • Hi Ryan
    lets say we have target module where we have target lists/dimensions and where we want to build ranking across these lists
    it's 5 step solution for case with 2,3 and more groupping ties  
    1. we create new ties_fullnames module using lists we want to group by 
    2. we build formula in ties_fullnames like =name(item(list1))&name(item(list2))name(item(list3)). So in cells we get all variants for grouping
    3. we create new list ties_fullnames and import their values from module ties_fullnames
    4. in target module we create new linitem grouping =finditem(ties_fullnames,name(item(list1))&name(item(list2))name(item(list3)))
    5. then we use grouping in Rank function
    works fine
  • Hello,

    I'm attempting to rank project variances by project manager and need the rank value to reset so that each project manager can view their top project variances by month and total year. I haven't been able to come up with a solution so any help is greatly appreciated.

    Thanks!

    Chase
  • Hi Chase,

    Is Project Manager a dimension in your model? If so, have you tried using that in the Group part of the RANK function (more details here: https://community.anaplan.com/t5/Calculation-Functions/RANK/ta-p/17411)?

    Thanks,
  • Hi Chris,

     

    Current dimensions in the module are:

    - Projects list (rolls up to a project category and then to a project manager)

    - Months list (fake timescale)

    - Line items

     

    Current requirements:

    - Need to give each PM the ability to see their top 10 project variances.

     

    I'm currently using the rank function and grouping it on the Months dimension but need the projects to be ranked individually for each PM and not for the entire Projects list.

  • Hi Chase,

    In order to accommodate your requirement using the standard RANK function, you would need to add Project Managers as a dimension in your model (Project Managers can be assigned to Projects through a list-formatted line item and then pulled in to your project variance module the same way that it is right now).

    There are likely other workarounds here, but would it be possible to try that approach before going too deep into the weeds in customizing your model?

    Hope that helps,
  • Chris,

     

    Do you mind emailing me at ccantwell@carpedatuminc.com? I have a couple of screenshots I'd like to provide you with that might help clarify what it is I'm looking at.

     

    Chase

  • Hi Chase,

    Any chance you can include those screenshots here on this thread? Would like this to be an accurate record of the issue and resolution for future community members who may come across this thread with a similar question. (Obviously not sharing any production data.)

    Thanks,