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.

takushimozuru_0-1581311956998.png

 

Best regards.

Best Answer

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

Comments

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

     

    Thanks,

    Misbah

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

     

     Screenshot_9.png

    2. Better to create another System module for Filter 

     

    Screenshot_10.png

     

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

    Screenshot_12.png

     

    Hope this helps

     

    Thanks

    Akhtar

     

     

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

    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?

     

    Regards,

    Andre

     

  • 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

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

     

    2020-02-10_19-09-41.png

     

    Change the summary to MAX for the Date line item:

    2020-02-10_19-09-59.png

     

    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']

     

    2020-02-10_19-10-33.png

     

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

    2020-02-10_19-11-32.png

     

    Create view filtering on Load?

    2020-02-10_19-16-09.png

     

    Final solution:

    2020-02-10_19-15-56.png

    Let me know if this helps,

     

    Rob

  • Brilliant @rob_marshall !!

    Now that's thinking out of the box.