How to use the RANK function


Hi everyone.


I would like to use RANK function and extract only the data for the most recent date.

However, the module have over 10 million cells. RANK function cannot be used...
What should I do now?


The following error message is displayed.



Best regards.

Best Answer

  • dmitrii.mamaev

    Hello, Taku!

    In my opinion you should at first transform your module with 3 dimensions to module with 2 dimensions only, and only then use Rank function.


    The easiest way is to make view in current module that filter only the calendar date you need, and then import it to new module with action. It would make from 300x to 1000x values less. It would be less than 1mln cells for rank function.


  • @taku.s 


    Initially it used to be lesser than 10 Million, they have increased it to 10 Million in Q2 2018 but this still is a limitation - I guess to avoid performance issues. However I would like to ask if you you are trying to rank in multidimensionality or Is it just 2 dimensional where you dimension is too big for Rank function to perform. Can you please post your requirement and the way you have coded it. 




  • Hi @taku.s 


    I agree with @Misbah , If you can provide us screenshot then we can provide better solution.


    As of now i can think one workaround for your case:


    1. Create a System module to select the " From & To date"



    2. Better to create another System module for Filter 




    3.  One one line item in your main module for Rank which you want to export.



    Hope this helps






  • @Akhtar.shahbaz @Misbah

    Thank you for your reply.

    The module have three dimensions.
    Dimensions: Calendar,item and place

    I'm so Sorry, It's confidential, so I can't write any details here...

    Please give me a solution.

    Best regards.
  • Hi @taku.s ,


    What is your module's time scale? 

    What is the most recent date definition?  Is it last date that has data against any Place list item or is it something else?





  • Hello, @andre.lie 

    As I understood from previous question of @takushimozuru he uses fake date dimension against built-in one.


  • @dmitrii.mamaev 


    Thank you for your reply.

    As you advised, after changing the module to 2 dimensions, imported the data, I could use the RANK function.


    The problem has been solved.Thank you so much!!!


    Best regards.



  • @taku.s ,


    I know you already marked this as solved, but why not use MAX as the summary for the date?  This way, you will not have to create a new list, have to run an action, and it will always be dynamic.


    Please consider the following:

    I have a module with two dimensions/lists, but this also works for 3.




    Change the summary to MAX for the Date line item:



    Create a new line item named Load? (formatted as a boolean) with the following formula: 

    Date = Date[SELECT: TIME.All Periods, SELECT: 'OSU L1'.'All Employee''s']




    Now, you will only load the data with the "highest" date



    Create view filtering on Load?



    Final solution:


    Let me know if this helps,



  • Brilliant @rob_marshall !!

    Now that's thinking out of the box.

  • Hello Team, Can Dynamic Ranking work in a dimensionless module as well? I have 5 dimensions and the user wants to view top 10 values based on selection of one dimension or a combination of multiple dimension. How can that be achieved?