Rank Formula || Need to Cumulate in Reverse order

ShubhamCh

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.

 

 

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

Thanks!

-Shubham

ManjunathKN

Hi @ShubhamCh 

 

Let me know if below solution works.

 

 

Thanks,
Manjunath

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

@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