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

RANK_issue_67988.JPG

Accepted answers

All comments

JaredDolich

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

Try adding/concatenating the date (as text) to your "location" line item. Rank works off the key you create.
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!

CommunityMember127834

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

CommunityMember127834

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

jasonblinn

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

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

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.

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]

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

rob_marshall

@CommunityMember127834 ,

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

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

Rob

AWhitworth

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.

bletarte

Hello,

I'm not sure if this is still open, but I believe I was able to achieve it using the RankCumulate function. See below:

Hope this helps!

Bruno

Quick Links

All Categories
Recent Posts
Activity
Unanswered
Groups
Help
Best Of

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…