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!!
Answers
-
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!
0 -
@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!
0 -
@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.
0 -
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
5 -
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
1 -
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.
0 -
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
0
