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-within-groups-of-repeating/m-p/41335#M4333)

 

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,

Best Answers

  • ArunManickam
    Answer ✓

    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.

  • paolovm
    Answer ✓

    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.

     

     

     

Answers

  • 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. 

  • 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,

  • 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.

  • Okay for anybody else encountering this behavior (of RANK formula with SEQUENTIAL parameter giving values that do not line up with the displayed order of the items) I have at least determined a few causes to this issue, which should help in potentially avoiding it. 

     

    As others have noticed, re-ordering the base-level list items (either manually or with an "Order List..." action) will cause their rank value to update, so it is not tied directly to the order the list items were initially created in: 

    NoahJ_0-1667402985782.png

     

    What is happening is basically that the SEQUENTIAL formula setting assigns values in the order the list items would be if you ignored all parent connection changes.  I have found a two different actions that will cause the RANK to appear "out of order", and they are:

     

    1. Re-assigning the "Parent" value of base level items. If an item is assigned to a new parent, or the parent value is deleted causing the item to be orphaned, the item will keep it's previous RANK value, despite the item moving its display location. This occurs whether the parent value change was manual or through an import. I believe this is what occurred in the original forum post.

    NoahJ_3-1667411962521.png

     

    Triggering a "re-order" of the items will resolve this issue even if the display order doesn't change, because the backend "index" of the items is reset in accordance with their new location. You can drag and drop items manually in the list settings, or create an "Order List..." action done using the original 'Rank' line item (no new line items required). Running that action will cause the list items to be re-indexed within their new parents and the RANK values will match the display order.

    NoahJ_0-1667416062854.png

    The exception is if there are orphan items, as they will be placed first in number order even though they are displayed last. If orphan items are expected in your situation, then you will need to use the formula workaround described in the next section.

     

    2. Changing the order of parent list items. If the items in the parent list are re-orderinged, the display order will update to reflect it but the RANK value will remain unchanged - thus causing "out of sync" sequential rank display that will persist even if the base level items are further re-ordered afterwards.

    NoahJ_1-1667403760471.png

     

    Because re-ordering the base level items does not resolve the issue, the "Order List..." action will not help in this case. However, I have found what I believe to be the simplest formula workaround, which resolves the issue in this situation (as well as the one above): 

    1. Create a line item called "Parent Rank" that only applies to the parent level, and set the formula to RANK(1, ASCENDING, SEQUENTIAL)
    2. Create or locate a Parent property or line item, so that you can do a lookup to the parent value.
    3. Create a line item called "New Rank" (or whatever fits your needs), with the formula RANK(Parent Rank[LOOKUP: Parent], ASCENDING, SEQUENTIAL)
    4. This creates rank values that match the display order of the items
      NoahJ_4-1667414134731.png
    5. This version of the formula will assign any orphan items as the first in the number order, even though they are displayed last. If you expect orphan items in your situation, you can simply adjust the "Parent Rank" formula to make sure that all values are below 0 by subtracting an arbitrarily large number: RANK(1, ASCENDING, SEQUENTIAL) - 9999999999
      NoahJ_5-1667414263593.png


    Hopefully this information was helpful - I know this behavior was confusing to me, and at least understanding why it was happening put my mind to ease a bit. Hopefully the fix / workarounds will help anybody else running into this issue.