Continuous Ranking (no missing Rank)

    First of all I want to thank few people here before I go over this document

     

     

    I am going to share a slightly modified version of John's data used in his post (How to do this in the script) just to save myself from creating a new sample

     

    Table:
    LOAD * INLINE [
        Group, Vendor, ID, Trans_ID
        Black, UVW, A65, A165
        Black, WXY, A66, A166
        Black, WXY, A67, A167
        Black, WXY, A69, A169
        Black, WXY, A70, A170
        Black, WXY, A71, A171
        Black, XYZ, A72, A172
        Black, ZAB, A73, A173
        Black, ZAB, A74, A174
        Black, ZAB, A75, A175
        Black, ZAB, A76, A176
        Blue, QRS, A56, A156
        Blue, QRS, A57, A157
        Blue, QRS, A58, A158
        Blue, RST, A59, A159
        Blue, RST, A60, A160
        Blue, RST, A61, A161
        Blue, RST, A62, A162
        Blue, RST, A63, A163
        Green, KLM, A37, A137
        Green, KLM, A38, A138
        Green, LMN, A39, A139
        Green, LMN, A40, A140
        Green, LMN, A43, A143
        Green, LMN, A44, A144
        Green, LMN, A45, A145
        Green, LMN, A46, A146
        Green, NOP, A47, A147
        Green, NOP, A48, A148
        Green, NOP, A49, A149
        Green, NOP, A50, A150
        Green, OPQ, A51, A151
        Green, OPQ, A52, A152
        Green, OPQ, A53, A153
        Green, OPQ, A54, A154
        Green, OPQ, A55, A155
        Red, DEF, A22, A122
        Red, DEF, A23, A123
        Red, DEF, A24, A124
        Red, DEF, A25, A125
        Red, DEF, A26, A126
        Red, DEF, A27, A127
        Red, FGH, A28, A128
        Red, GHJ, A29, A129
        Red, GHJ, A30, A130
        Red, GHJ, A31, A131
        Red, GHJ, A32, A132
        Red, GHJ, A33, A133
        Red, IJK, A34, A134
        White, TUV, A64, A164
        Yellow, ABC, A1, A101
        Yellow, ABC, A10, A110
        Yellow, ABC, A11, A111
        Yellow, ABC, A12, A112
        Yellow, ABC, A2, A102
        Yellow, ABC, A3, A103
        Yellow, ABC, A4, A104
        Yellow, MYM, A98, A141
        Yellow, MYM, A99, A142
        Yellow, MYM, A100, A143
        Yellow, MYM, A101, A144
        Yellow, ABC, A5, A105
        Yellow, ABCC, A13, A113
        Yellow, YOY, A90, A122
        Yellow, YOY, A91, A123
        Yellow, YOY, A92, A124
        Yellow, YOY, A93, A125
        Yellow, YOY, A94, A126
        Yellow, YOY, A95, A127
        Yellow, YOY, A77, A201
        Yellow, ABCC, A78, A202
        Yellow, ABCC, A79, A203
        Yellow, ABDD, A80, A204
        Yellow, ABDD, A81, A205
        Yellow, ABDD, A82, A206
        Yellow, ABDD, A83, A207
        Yellow, ABEE, A15, A115
        Yellow, CDE, A16, A116
        Yellow, CDE, A17, A117
        Yellow, CDE, A18, A118
        Yellow, CDE, A19, A119
        Yellow, CDE, A20, A120
        Yellow, CDE, A21, A121
    ];
    
    
    
    

     

    The requirement is to find the Rank of Count(ID) here and it needs to be continuous (as shown below)

     

    GroupVendorCount(ID)Rank Needed
    YellowABC81
    YellowYOY72
    YellowCDE63
    YellowMYM44
    YellowABDD44
    YellowABCC35
    YellowABEE16

     

    But this output isn't possible using a simple Rank function (even with different combinations available within Rank function)

     

    Capture.PNG

     

    Rank(1,0) -> Num(Rank(Count(ID), 1, 0))

    Rank(2,0) -> Num(Rank(Count(ID), 2, 0))

    Rank(3,0) -> Num(Rank(Count(ID), 3, 0))

    Rank(4,0) -> Num(Rank(Count(ID), 4, 0))


    So in order to achieve "Rank Needed" column, we can use a work around provided by Massimo


    Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1),Group, Vendor), '00000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(Count(ID),1,1), '00000')), 6) + 1

     

    Capture.PNG

     

    But how does this formula work?

     

    Step 1-1

    Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1),Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor))

     

    Concate a distinct (USING DISTINCT) list of possible ranks by each group (TOTAL <Group>) which is sorted in ascending order using the expression -Aggr(Count(ID), Group, Vendor) and separated by '-'.

     

    Assign the possible list of rank a particular formatting here where they should all be 4 digit. I think the assumption here is that the max rank would ever be 9999, if this is not true, then you can change from '0000' to '00000'. This will now cover upto 99999 (this is where the division will come into play, which I will go into later)

     

    Capture.PNG

     

    Step 1-2

    Num(Rank(Count(ID),1,1),'0000')

     

    Find the rank for each row and assign it the same number formatting as the formatting assigned with the Concat function. '0000' or '00000'.

     

    Capture.PNG

     

    Step 2

    Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(Count(ID),1,1),'0000'))

     

    Find the location of '-' within the concatenated list of rank, but this index function has a third parameter telling it to look for which '-' in the list. The third parameter is basically the rank from Step 1-2 which goes like 1, 2, 3. So basically what is the location of first 1st '-', 2nd '-' and so on....

     

    Capture.PNG

     

    Step 3

    Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(Count(ID),1,1), '0000')), 5) + 1

     

    I don't think we have to use Div function here, I believe that if the list of rank is small you can also use Pick match, but for a larger rank, div will work better here.

     

    The idea is that since we have used '0000' as number formatting the location of '-' will change by 5 each time (2, 7, 12, 17, 22, 27, 32, ......) so dividing this by 5 will give us (0, 1, 2, 3, 4, 5, 6) and adding one to this will give us (1, 2, 3, 4, 5). Now for the cases we have repeating rank we will get the same position of '-' and for the next highest rank, even the concatenated list of ranks wouldn't have the next set of rank making this expression possible to get the number incrementally (look at the three highlighted rows below).

     

    Capture.PNG

     

    Now if for some reason we think that rank can run above 10,000, but less than 99,999, we might want to change the expression to this:

    Div(Index('-' & Concat(DISTINCT TOTAL <Group> Num(Aggr(Rank(Count(ID),1,1), Group, Vendor), '00000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(Count(ID),1,1), '00000')), 6) + 1


    Capture.PNG

     

    For overall rank, you can modify the expression to something like this:

    Div(Index('-' & Concat(DISTINCT TOTAL Num(Aggr(Rank(TOTAL Count(ID), 1, 1), Group, Vendor), '0000'), '-', -Aggr(Count(ID), Group, Vendor)), Num(Rank(TOTAL Count(ID),1,1), '0000')), 5) + 1

     

    Capture.PNG

     

    I hope this will end up being helpful for someone

     

    Best,

    Sunny