topic Re: Behaviour of SEQUENTIAL parameter in RANK function in Anaplan Platform
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48340#M6398
<P>Hi Arun,</P><P>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</P><TABLE><TBODY><TR><TD> </TD><TD>CURRENT</TD><TD>PROPOSED</TD><TD>DESIRED</TD></TR><TR><TD>SKU 1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>SKU 2</TD><TD>2</TD><TD>2</TD><TD>2</TD></TR><TR><TD>SKU 3</TD><TD>3</TD><TD>3</TD><TD>3</TD></TR><TR><TD>SKU 4</TD><TD>4</TD><TD>4</TD><TD>4</TD></TR><TR><TD>SKU 5</TD><TD>5</TD><TD>5</TD><TD>5</TD></TR><TR><TD>SKU 6</TD><TD>6</TD><TD>6</TD><TD>6</TD></TR><TR><TD>SKU 7</TD><TD>7</TD><TD>7</TD><TD>7</TD></TR><TR><TD>SKU 8</TD><TD>8</TD><TD>8</TD><TD>8</TD></TR><TR><TD>SKU 9</TD><TD>9</TD><TD>9</TD><TD>9</TD></TR><TR><TD>SKU 10</TD><TD>10</TD><TD>10</TD><TD>10</TD></TR><TR><TD>SKU 11</TD><TD>11</TD><TD>11</TD><TD>11</TD></TR><TR><TD>SKU 12</TD><TD>12</TD><TD>12</TD><TD>12</TD></TR><TR><TD>SKU 13</TD><TD>13</TD><TD>13</TD><TD>13</TD></TR><TR><TD>SKU 14</TD><TD>14</TD><TD>14</TD><TD>14</TD></TR><TR><TD>SKU 15</TD><TD>15</TD><TD>15</TD><TD>15</TD></TR><TR><TD>SKU 16</TD><TD>16</TD><TD>16</TD><TD>16</TD></TR><TR><TD>SKU 17</TD><TD>17</TD><TD>17</TD><TD>17</TD></TR><TR><TD>SKU 18</TD><TD>18</TD><TD>18</TD><TD>18</TD></TR><TR><TD>SKU 19</TD><TD>19</TD><TD>19</TD><TD>19</TD></TR><TR><TD>SKU 20</TD><TD>20</TD><TD>20</TD><TD>20</TD></TR><TR><TD>SKU 21</TD><TD>21</TD><TD>21</TD><TD>21</TD></TR><TR><TD>SKU 22</TD><TD>22</TD><TD>22</TD><TD>22</TD></TR><TR><TD>SKU 23</TD><TD>23</TD><TD>23</TD><TD>23</TD></TR><TR><TD>SKU 24</TD><TD>24</TD><TD>24</TD><TD>24</TD></TR><TR><TD>SKU 25</TD><TD>25</TD><TD>25</TD><TD>25</TD></TR><TR><TD>SKU 26</TD><TD>26</TD><TD>26</TD><TD>26</TD></TR><TR><TD>SKU 27</TD><TD>27</TD><TD>27</TD><TD>27</TD></TR><TR><TD>SKU 28</TD><TD>76</TD><TD><FONT color="#FF0000">28</FONT></TD><TD><FONT color="#FF0000">28</FONT></TD></TR><TR><TD>SKU 29</TD><TD>77</TD><TD><FONT color="#FF0000">29</FONT></TD><TD><FONT color="#FF0000">29</FONT></TD></TR><TR><TD>SKU 30</TD><TD>85</TD><TD><FONT color="#FF0000">30</FONT></TD><TD><FONT color="#FF0000">30</FONT></TD></TR><TR><TD>SKU PARENT 1</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SKU 31</TD><TD>28</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">31</FONT></TD></TR><TR><TD>SKU 32</TD><TD>29</TD><TD><FONT color="#FF0000">2</FONT></TD><TD><FONT color="#FF0000">32</FONT></TD></TR><TR><TD>SKU 33</TD><TD>30</TD><TD><FONT color="#FF0000">3</FONT></TD><TD><FONT color="#FF0000">33</FONT></TD></TR><TR><TD>SKU 34</TD><TD>31</TD><TD><FONT color="#FF0000">4</FONT></TD><TD><FONT color="#FF0000">34</FONT></TD></TR><TR><TD>SKU 35</TD><TD>32</TD><TD><FONT color="#FF0000">5</FONT></TD><TD><FONT color="#FF0000">35</FONT></TD></TR><TR><TD>SKU 36</TD><TD>81</TD><TD><FONT color="#FF0000">6</FONT></TD><TD><FONT color="#FF0000">36</FONT></TD></TR></TBODY></TABLE><P> </P><P>Let me know if I misunderstood.</P><P> </P><P>Regards,</P>Tue, 09 Jul 2019 04:32:11 GMTpaolovm2019-07-09T04:32:11ZBehaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48337#M6396
<P>Hi All,</P><P> </P><P>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: <A href="https://community.anaplan.com/t5/Anaplan-Platform-Discussions/COUNTIF-How-to-add-sequence-numbers-within-groups-of-repeating/m-p/41335#M4333" target="_blank">https://community.anaplan.com/t5/Anaplan-Platform-Discussions/COUNTIF-How-to-add-sequence-numbers-within-groups-of-repeating/m-p/41335#M4333</A>)</P><P> </P><P>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. </P><P> </P><P>When I export the information from an Anaplan unfiltered view the result of this formula I get the result below:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="item sequence.JPG" style="width: 414px;"><img src="https://community.anaplan.com/t5/image/serverpage/image-id/25138i63B132BEAD33DF94/image-size/large?v=1.0&px=999" role="button" title="item sequence.JPG" alt="item sequence.JPG" /></span></P><P> </P><P>Given the above I'd like to clarify the following:</P><P>- 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?</P><P> </P><P>- 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.</P><P> </P><P>Let me know if something is unclear.</P><P> </P><P>Cheers,</P>Tue, 09 Jul 2019 03:34:09 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48337#M6396paolovm2019-07-09T03:34:09ZRe: Behaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48338#M6397
<P>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 <SPAN>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. </SPAN></P>Tue, 09 Jul 2019 04:17:19 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48338#M6397ArunManickam2019-07-09T04:17:19ZRe: Behaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48340#M6398
<P>Hi Arun,</P><P>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</P><TABLE><TBODY><TR><TD> </TD><TD>CURRENT</TD><TD>PROPOSED</TD><TD>DESIRED</TD></TR><TR><TD>SKU 1</TD><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>SKU 2</TD><TD>2</TD><TD>2</TD><TD>2</TD></TR><TR><TD>SKU 3</TD><TD>3</TD><TD>3</TD><TD>3</TD></TR><TR><TD>SKU 4</TD><TD>4</TD><TD>4</TD><TD>4</TD></TR><TR><TD>SKU 5</TD><TD>5</TD><TD>5</TD><TD>5</TD></TR><TR><TD>SKU 6</TD><TD>6</TD><TD>6</TD><TD>6</TD></TR><TR><TD>SKU 7</TD><TD>7</TD><TD>7</TD><TD>7</TD></TR><TR><TD>SKU 8</TD><TD>8</TD><TD>8</TD><TD>8</TD></TR><TR><TD>SKU 9</TD><TD>9</TD><TD>9</TD><TD>9</TD></TR><TR><TD>SKU 10</TD><TD>10</TD><TD>10</TD><TD>10</TD></TR><TR><TD>SKU 11</TD><TD>11</TD><TD>11</TD><TD>11</TD></TR><TR><TD>SKU 12</TD><TD>12</TD><TD>12</TD><TD>12</TD></TR><TR><TD>SKU 13</TD><TD>13</TD><TD>13</TD><TD>13</TD></TR><TR><TD>SKU 14</TD><TD>14</TD><TD>14</TD><TD>14</TD></TR><TR><TD>SKU 15</TD><TD>15</TD><TD>15</TD><TD>15</TD></TR><TR><TD>SKU 16</TD><TD>16</TD><TD>16</TD><TD>16</TD></TR><TR><TD>SKU 17</TD><TD>17</TD><TD>17</TD><TD>17</TD></TR><TR><TD>SKU 18</TD><TD>18</TD><TD>18</TD><TD>18</TD></TR><TR><TD>SKU 19</TD><TD>19</TD><TD>19</TD><TD>19</TD></TR><TR><TD>SKU 20</TD><TD>20</TD><TD>20</TD><TD>20</TD></TR><TR><TD>SKU 21</TD><TD>21</TD><TD>21</TD><TD>21</TD></TR><TR><TD>SKU 22</TD><TD>22</TD><TD>22</TD><TD>22</TD></TR><TR><TD>SKU 23</TD><TD>23</TD><TD>23</TD><TD>23</TD></TR><TR><TD>SKU 24</TD><TD>24</TD><TD>24</TD><TD>24</TD></TR><TR><TD>SKU 25</TD><TD>25</TD><TD>25</TD><TD>25</TD></TR><TR><TD>SKU 26</TD><TD>26</TD><TD>26</TD><TD>26</TD></TR><TR><TD>SKU 27</TD><TD>27</TD><TD>27</TD><TD>27</TD></TR><TR><TD>SKU 28</TD><TD>76</TD><TD><FONT color="#FF0000">28</FONT></TD><TD><FONT color="#FF0000">28</FONT></TD></TR><TR><TD>SKU 29</TD><TD>77</TD><TD><FONT color="#FF0000">29</FONT></TD><TD><FONT color="#FF0000">29</FONT></TD></TR><TR><TD>SKU 30</TD><TD>85</TD><TD><FONT color="#FF0000">30</FONT></TD><TD><FONT color="#FF0000">30</FONT></TD></TR><TR><TD>SKU PARENT 1</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>SKU 31</TD><TD>28</TD><TD><FONT color="#FF0000">1</FONT></TD><TD><FONT color="#FF0000">31</FONT></TD></TR><TR><TD>SKU 32</TD><TD>29</TD><TD><FONT color="#FF0000">2</FONT></TD><TD><FONT color="#FF0000">32</FONT></TD></TR><TR><TD>SKU 33</TD><TD>30</TD><TD><FONT color="#FF0000">3</FONT></TD><TD><FONT color="#FF0000">33</FONT></TD></TR><TR><TD>SKU 34</TD><TD>31</TD><TD><FONT color="#FF0000">4</FONT></TD><TD><FONT color="#FF0000">34</FONT></TD></TR><TR><TD>SKU 35</TD><TD>32</TD><TD><FONT color="#FF0000">5</FONT></TD><TD><FONT color="#FF0000">35</FONT></TD></TR><TR><TD>SKU 36</TD><TD>81</TD><TD><FONT color="#FF0000">6</FONT></TD><TD><FONT color="#FF0000">36</FONT></TD></TR></TBODY></TABLE><P> </P><P>Let me know if I misunderstood.</P><P> </P><P>Regards,</P>Tue, 09 Jul 2019 04:32:11 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48340#M6398paolovm2019-07-09T04:32:11ZRe: Behaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48348#M6406
<P>I understand your requirement now.</P><P>SEQUENTIAL is not giving us the display sequence, it is the order of creation of the list item itself i believe.</P><P> </P><P>I think you can achieve this by a workaround process. Just a thinking, try it out.</P><P> </P><P>Create a rank @ SKU level. Have the summary as MAX</P><P>Create a rank @ SKU_PARENT level. </P><P>Create a module by SKU Parent, create a lineitem as MAX RANK - get the summary of rank @ SKU level</P><P>Final rank = rank @ SKU + MAXRANK[lookup for the parent having rank-1]</P><P> </P><P>Be mindful of the limitations on RANK and its performance impact.</P>Tue, 09 Jul 2019 05:42:10 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48348#M6406ArunManickam2019-07-09T05:42:10ZRe: Behaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48349#M6407
<P>That looks more promising than what I've been trying so far. That or creating an ad-hoc export list.</P><P> </P><P>Thanks! Will give it a go between today and tomorrow and let you know the outcome.</P>Tue, 09 Jul 2019 06:06:18 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48349#M6407paolovm2019-07-09T06:06:18ZRe: Behaviour of SEQUENTIAL parameter in RANK function
https://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48418#M6436
<P>Hi Arun,</P><P> </P><P>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.</P><P> </P><P>In any case, inspired on your approach I managed to find solution that seems to do the trick in a single module.</P><P> </P><P>1. Create an "ORIGINAL ORDER" item with simplest version of the formula RANK(1,DESCENDING,SEQUENTIAL)</P><P>2. Apply to "ORIGINAL ORDER" the summary=formula (so that parents are also ordered between themselves)</P><P>3.Create a JOINT ORDERformula which is PARENT ORDER+ ORIGINAL ORDER/100</P><P>4.Create a FINAL ORDER with RANK(JOINT ORDER, ASCENDING, SEQUENTIAL)</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Order Fix.JPG" style="width: 702px;"><img src="https://community.anaplan.com/t5/image/serverpage/image-id/25168i3DCEA641E21CA9DA/image-size/large?v=1.0&px=999" role="button" title="Order Fix.JPG" alt="Order Fix.JPG" /></span></P><P>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.</P><P>Thanks for the help.</P><P> </P><P> </P><P> </P>Wed, 10 Jul 2019 02:13:40 GMThttps://community.anaplan.com/t5/Anaplan-Platform/Behaviour-of-SEQUENTIAL-parameter-in-RANK-function/m-p/48418#M6436paolovm2019-07-10T02:13:40Z