Rank Formula || Need to Cumulate in Reverse order

Hi Everyone,

I have a module dimensioned by List 1 and Time (Quarter). There is one imported line item (List formatted). I want to number these imported cells data according to the quarter.

For example:-

List item A has values in the following quarters Q1FY23, Q2FY23, Q4FY23, Q1FY24, Q3FY24 (other quarters are blank) so I want to rank them in descending order like the value in Q3FY24 --> 1 and so on. This ranking should be grouped by list items. I created one static line item which is equal to one when the previous column is not blank. I tried cumulating this static line item in the next line item. Cumulating is working but I want to reverse its order. I have been writing the rank formula over cumulate line item but unable to get the desired result. The result of rank formula is confusing me. I can't understand how this function is ranking the values.

I am sharing the screenshot. I need the result as shown in the other line item. 0 can be considered as NaN.

Screenshot 2022-10-20 at 11.55.11 PM.png

Screenshot 2022-10-20 at 11.55.24 PM.png

Also if someone can explain rank formula when there are more than one dimension, that'll be very helpful.

Thanks!

-Shubham

Accepted answers

ManjunathKN

Hi @ShubhamCh

Let me know if below solution works.

Thanks,
Manjunath

All comments

Misbah

@ShubhamCh

How to use Rank in multiple hierarchies - here is the post that can help you

https://community.anaplan.com/t5/Anaplan-Platform/RANK-within-Multiple-Hierarchies/td-p/59564

However, I do believe that RANK does not traverse thru time periods meaning Ranking happens within non time dimensions. If you have a Time dimension in your source line item, then it will rank all the list items within one time period across all dimensions. And once it is done it will go to another time period and do the same thing over and over. This is my understanding of how the Ranking works for TIME and VERSION dimension, unless I am proven wrong I would like to believe so.

Thanks,

Misbah

Miz Logix

ShubhamCh

@Misbah

Yeah. I observed the behaviour of Rank function with time. It ranked every member as 1. I couldn't use it. I thought I was doing something wrong.

I couldn't get the desired result using Rank function but @DmitrySorokin helped me to solve it, using a different calculation approach. The result is not exactly as shown in the image attached to the question but it gets the work done.

Thanks Misbah for explaining the Rank function over time dimension.

-Shubham

ManjunathKN

Hi @ShubhamCh

Let me know if below solution works.

Thanks,
Manjunath

ManjunathKN

@ShubhamCh

just to add on above point, I have used text formatted in values like your example, if you use list/number use appropriate data conversion.

Thanks

ShubhamCh

Hi @ManjunathKN

Yeah it is almost close to the solution that @DmitrySorokin created except he didn't create a top level on periods but used Timesum funtion to get the max value of Cumulate but I accept this as a solution because it is doing the desired work.

Thanks Manjunath. 😊

- Shubham

Quick Links

Can you help?

Unanswered questions

Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…