How to use the RANK function

Highlighted
Contributor

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.

9 REPLIES 9
Highlighted
Master Anaplanner/Community Boss

Re: How to use the RANK function

@takushimozuru 

 

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

Highlighted
Super Contributor

Re: How to use the RANK function

Hi @takushimozuru 

 

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

 

 

Highlighted
Contributor

Re: How to use the RANK function

@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.
Highlighted
Certified Master Anaplanner

Re: How to use the RANK function

Hi @takushimozuru ,

 

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

 

Highlighted
Certified Master Anaplanner

Re: How to use the RANK function

Hello, @andre.lie 

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

 

Highlighted
Certified Master Anaplanner

Re: How to use the RANK function

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.

Highlighted
Contributor

Re: How to use the RANK function

@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

Highlighted
Master Anaplanner/Community Boss

Re: How to use the RANK function

@takushimozuru ,

 

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

Highlighted
Master Anaplanner/Community Boss

Re: How to use the RANK function

Brilliant @rob_marshall !!

Now that's thinking out of the box.


Jared Dolich