Rank Formula Work Around

Highlighted
Occasional Contributor

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

5 REPLIES 5
Highlighted
Master Anaplanner/Community Boss

Re: Rank Formula Work Around

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

 

 


Jared Dolich
Highlighted
Occasional Contributor

Re: Rank Formula Work Around

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

Highlighted
Occasional Contributor

Re: Rank Formula Work Around

@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

 

Highlighted
Certified Master Anaplanner

Re: Rank Formula Work Around

@CommunityMember127834 

 

I think I have created a workaround for this one, or this might at least give you an additional idea.

 

If you create an Isfirstoccurrence based on the date, then you can find the first time this date occurs on your dimension:

jasonblinn_0-1587804563021.png

Once we have that then we can Rank based on the First occurrence. The 4th argument in the Rank is a Bool, which is what our isfirstoccurrence is going to be. We also need an If statement here to not return a NaN value. My Formula in my rank line item looks like this: IF Isfirstoccurrence THEN RANK(Survey Date, ASCENDING, SEQUENTIAL, Isfirstoccurrence) ELSE 0

jasonblinn_1-1587804718760.png

Then, I create a Line/module that has no dimensions except for Day. My Formula is going to read: Rank[SUM: Survey Date] . This will bring in the Ranked value to the individual date so that we can reference it by the rest of the items using the same date.

 

jasonblinn_2-1587804869393.png

 

And Finally, I add my Final Rank. The formula is looking at the prior line item and looking up the date of that record. The formula reads: Time to Data[LOOKUP: Survey Date]

 

jasonblinn_3-1587805004744.png

As you can see, I now have all of my items ranked sequentially and have no numbers that are being skipped. 

 

I hope this helps!

Jason

 

 

 

 

 

 

 

Highlighted
Master Anaplanner/Community Boss

Re: Rank Formula Work Around

@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