Question on RANKCUMULATE formula

ClarenceA
Regular Contributor

Question on RANKCUMULATE formula

Dear all,

Kindly refer screenshot below. I am experimenting with the RANKCUMULATE formula with two versions shown below, respectively in the columns labelled "Account Counter 1" and "Account Counter 2".

"Account Counter 1" which has the formula RANKCUMULATE(To Change, 1, ASCENDING, TRUE, Country) is working correctly.

"Account Counter 2" has the simpler version of the formula with just RANKCUMULATE(To Change. 1) but it is not working correctly as shown below - the numbers should be running in sequence from 1 to 150 corresponding to the number of rows in the module but is not. Instead we have the first 5 from 1 to 5 in the correct sequence and then it jumps to 41 - does anybody know why this is happening?

Kind regards,

Clarence

 

TAR02 Account Review 210507_2.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Question on RANKCUMULATE formula

@ClarenceA 

That is correct.

Therefore, if you change the row order in the grid the sequence will not update accordingly. 

I can see that you have applied a sort. If you were to remove this and revert the list back to it original order then the sequence will increase as per your expectations. 

You can run an action that will re-sort the list as per a logic determined in a systems module.

Sort list action

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

PhilippErkinger
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

A very simple support calculation module for the redistribution on a lower level with some balancing checks may help. ... but that's all I can tell you. 😉

Planning Rockstar at Bluesprint

View solution in original post

13 REPLIES 13
vinayvm
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

Hi @ClarenceA ,

 

In the 2nd version of the formula, there is COUNTRY at the 5th place (which is meant for Rank Groups). With the Rank group, you can calculate ranks within that group (in this case COUNTRY). Hence, you see Ranks 1 to 20 for US, and again Ranks starting from 1 for UK.

 

Hope this helps!

PhilippErkinger
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

@ClarenceA 

 

As @vinayvm states, the 'Account Counter 1' formula uses the Rank Group function, limiting it within each country group. 'Account Counter 2' has no limits and will just rank it according to appearance in the module. This may be quite difficult to understand in a multi-dimensional module. To understand the behaviour of RANKCUMULATE, it helps to pivot all lists to the rows, show all parents and remove all sorts. I think then it may become more clear what it is doing.

 

More info can be found  here 

Planning Rockstar at Bluesprint
ClarenceA
Regular Contributor

Re: Question on RANKCUMULATE formula

Many thanks both @PhilippErkinger @vinayvm for explaining why RANKCUMULATE(To Change,1) is not behaving the way as one would expect - what would then be the correct arguments to use to get the correct sequence from 1 to N where N is the number of rows in the module?

Thanks and regards,

Clarence

  

PhilippErkinger
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

@ClarenceA That's a bit tricky, because it fully depends on the dimensions used in the module and their natural order in the list. It may help to have a support module or try different scenarios with ranking groups.

 

Is this from the L3 course? The module seems familiar. 

Planning Rockstar at Bluesprint
ClarenceA
Regular Contributor

Re: Question on RANKCUMULATE formula

Hi Philipp it is indeed from L3 🙂
PhilippErkinger
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

@ClarenceA then I wonder what you want to achieve with the rankcumulate in this module? You may think too complex, the solution may be simple.... just a small hint. 😎

Planning Rockstar at Bluesprint
ClarenceA
Regular Contributor

Re: Question on RANKCUMULATE formula

Hi @PhilippErkinger 

Completely agree. I might been overcomplicating the situation.

What I'm trying to come up with is a method to locate the Redistribution Ratio shown in the screenshot below. I am able to calculate the Redistribution Ratio for the country (bordered in green) but the question is how do I move that to the next column to complete the calculation needed?

Which is when I started thinking about RANKCUMULATE to come up with the index needed to locate the cell required. Any ideas of what  I should be thinking if I'm missing the point?

Kind regards,

Clarence

 

TAR02 Account Review 210507_1.jpg

PhilippErkinger
Certified Master Anaplanner

Re: Question on RANKCUMULATE formula

A very simple support calculation module for the redistribution on a lower level with some balancing checks may help. ... but that's all I can tell you. 😉

Planning Rockstar at Bluesprint

View solution in original post

ClarenceA
Regular Contributor

Re: Question on RANKCUMULATE formula

Thanks Philipp, let me give some thought to that approach. Best regards, Clarence.