Highlighted
Certified Master Anaplanner

Behaviour of SEQUENTIAL parameter in RANK function

Hi All,

 

We have a requirement to sequence items in a export and we've been using the RANK function to achieve that in a similar way that is listed in several posts in the community (example: https://community.anaplan.com/t5/Anaplan-Platform-Discussions/COUNTIF-How-to-add-sequence-numbers-wi...)

 

However, when applying the simplest version of the formula as in "RANK ( 1 , DESCENDING ,  SEQUENTIAL )" to a module I'm not getting the sequence as normally displayed for a specific list, but some order which might be related to the creation order. 

 

When I export the information from an Anaplan unfiltered view the result of this formula I get the result below:

item sequence.JPG

 

Given the above I'd like to clarify the following:

- Is the SEQUENTIAL in the ORDER function related to a display order or to some internal index in the list? If it is to the display order what could be wrong with the simple logic/extraction above?

 

- If SEQUENTIAL is not about display sequence, what would be a reasonable way to get this sequencing in the line items as per display and not per internal index?I was considering creating a property or property module just to maintain an extra field with the sequencing I want, but I'd expect there is some way to expose that.

 

Let me know if something is unclear.

 

Cheers,

5 REPLIES 5
Highlighted
Community Boss

Re: Behaviour of SEQUENTIAL parameter in RANK function

Looks like the ranking is done across your SKU PARENT level. If your expectation is to produce a ranking within each SKU PARENT, you could use RANK ( 1 , DESCENDING ,  SEQUENTIAL, TRUE, SKU PARENT ) where SKU PARENT is a line item formatted as SKU PARENT list and with a formula parent(item(SKU)). Alternatively you could pull the parent property from a module dimension-ed with SKU level. Hope this helps. 

Highlighted
Certified Master Anaplanner

Re: Behaviour of SEQUENTIAL parameter in RANK function

Hi Arun,

I think I understand your answer, but that's not what I am looking for. As per my understanding below is a comparative of what is currently happening vs. what you proposed vs. what I am after

 CURRENTPROPOSEDDESIRED
SKU 1111
SKU 2222
SKU 3333
SKU 4444
SKU 5555
SKU 6666
SKU 7777
SKU 8888
SKU 9999
SKU 10101010
SKU 11111111
SKU 12121212
SKU 13131313
SKU 14141414
SKU 15151515
SKU 16161616
SKU 17171717
SKU 18181818
SKU 19191919
SKU 20202020
SKU 21212121
SKU 22222222
SKU 23232323
SKU 24242424
SKU 25252525
SKU 26262626
SKU 27272727
SKU 28762828
SKU 29772929
SKU 30853030
SKU PARENT 1   
SKU 3128131
SKU 3229232
SKU 3330333
SKU 3431434
SKU 3532535
SKU 3681636

 

Let me know if I misunderstood.

 

Regards,

Highlighted
Community Boss

Re: Behaviour of SEQUENTIAL parameter in RANK function

I understand your requirement now.

SEQUENTIAL is not giving us the display sequence, it is the order of creation of the list item itself i believe.

 

I think you can achieve this by a workaround process. Just a thinking, try it out.

 

Create a rank @ SKU level. Have the summary as MAX

Create a rank @ SKU_PARENT level. 

Create a module by SKU Parent, create a lineitem as MAX RANK - get the summary of rank @ SKU level

Final rank = rank @ SKU + MAXRANK[lookup for the parent having rank-1]

 

Be mindful of the limitations on RANK and its performance impact.

Highlighted
Certified Master Anaplanner

Re: Behaviour of SEQUENTIAL parameter in RANK function

That looks more promising than what I've been trying so far. That or creating an ad-hoc export list.

 

Thanks! Will give it a go between today and tomorrow and let you know the outcome.

Highlighted
Certified Master Anaplanner

Re: Behaviour of SEQUENTIAL parameter in RANK function

Hi Arun,

 

For curiosity, I did some further tests on this and if you reorder some items they do get reindexed so the SEQUENTIAL concepts is certainly not displaying a pure creation order either.

 

In any case, inspired on your approach I managed to find solution that seems to do the trick in a single module.

 

1. Create an "ORIGINAL ORDER" item with simplest version of the formula RANK(1,DESCENDING,SEQUENTIAL)

2. Apply to "ORIGINAL ORDER" the summary=formula (so that parents are also ordered between themselves)

3.Create a JOINT ORDERformula which is PARENT ORDER+ ORIGINAL ORDER/100

4.Create a FINAL ORDER with RANK(JOINT ORDER, ASCENDING, SEQUENTIAL)

Order Fix.JPG

Based on my findings so far, that looks to be always be valid for any hierarchy with two levels plus a total parent and it seems also feasible to expand for 3 or more levels.

Thanks for the help.