Rank Formula Work Around

Hi!

 

I have a module dimensionalized by a list of unique identifiers that is the child of a Location list. 

 

I'm trying to rank survey dates (a date formatted line item) in descending order based off of how recent they are BUT there are multiple UID's with the same survey date. 

 

The problem is, with the "RANK" formula, the "Ties" and "Include" logic that is built into the formula can't be left out without the formula failing.

 

For example, the minimum, maximum, average, and sequential logic that has to be selected within the formula all don't work for me.

 

In the screenshot I've provided, you can see that the rank jumps from 4 to 29 because I have it set to "minimum" in the formula. 

 

Ideally, I want this to display the number 5 rather than 29.

 

Does anyone have any ideas for a work around this?

 

Thanks so much!! 

Comments

  • @CommunityMember127834 

    Rank is my favorite function in Anaplan so I'm super motivated to help you with this one.

    Without building this out myself I can think of two ideas that might solve your issue.

    1. Try adding/concatenating the date (as text) to your "location" line item. Rank works off the key you create.
    2. Convert the date to a numeric field rather than a date field. You can covert it to YYYYMMDD or use a number like Epoch Time (number of seconds from 1/1/1970).

    If you can't get either of these to work, I'll create your scenario in a sandbox and give you step by step instructions. Hope this helps!

     

     

  • @JaredDolich Hey Jared, 

     

    Thanks for your response -

     

    I can't get either of those to work.

     

    The example you mentioned would be super helpful!

     

    Thank you so much! 

  • @JaredDolich I also wanted to add that the reason I couldn't get this to work is because I keep running into the same issue -

     

    That is, I still have to choose the  "minimum, maximum, average" etc functionality within the formula.

     

    Ideally, I would like to run the formula like it is in the screenshot attached.

     

    CommunityMember127834_0-1587589973635.png

     

  • @CommunityMember127834 ,

     

    I may be missing something, but does this get you what you want?

     

    2020-04-27_08-57-25.png2020-04-27_08-57-36.png

     

    Hope this works for you and is what you are looking for,

     

    Rob

  • I am encountering the same problem and thought I would resurrect this thread and see if anyone found a complete solution? 

     

    I got to Jason's idea on my own before searching for a better way as this isn't ideal when we have to combine the rank with other lists and larger time ranges. in excel it's possible using a combination of If, Sum and Count in an array but this isn't possible in Anaplan.