Ranking Code Optimization??

[rohitians]

Hello Everyone,

I have the following code using which I sort the  list Box(Supergroup),

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}>}Values )/10000000

Now according to the values the list box get sorted,

Below I pick 1st value from the list box...in following manner,

vTherapy1=AGGR(IF(RANK(SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}>}Values )/10000000

)=1,SUPERGROUP),SUPERGROUP)  

i have to pick top 9 values...

in the above manner den i have to declare 9 variables which is not feasible...

plz suggest how to optimize the code..

Thanks and Regards,

Rohit

[rohitians]

Hello,

I am attaching a demo application where in i have to define 5 variables ...i want to optimize instead of that i want to define one variable and just pass on parameters....

[kaushiknsolanki]

Hi,

    Here is a dynamic way, but user will have to enter the rank.

Regards,

Kaushik Solanki

[jonathandienst · Accepted solution]

Hi

Define the variable like this:

Set vTherapy1=AGGR(IF(RANK(SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

Date={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}Date),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}Date))"}>}Values )/10000000

)=$1, SUPERGROUP),SUPERGROUP) 

Now you can invoke it like this

     =$(vTherapy1(1))   ... for first ranked

     =$(vTherapy1(2))   ... for second ranked

     etc

HTH

Jonathan

[rohitians]

Thanks Kaushik its a good idea but I dnt have that option of user selecting rank....

[rohitians]

Dear Jonathan,

Last tym you gave me the same idea but it isnt working....can you explain me the same in the demo application which i have attached......please if you can..

Thanks and Regards,

Rohit

[jonathandienst]

Nothing attached...

[rohitians]

Jatin Kolhe

Sample Application

[jagan]

Hi Rohit,

Use this as dimension

=if(PLANT='$(=$(vPlant1(3)))',ITEMS)

and use this as expression in Presentation tab

=SUM({<PLANT={'$(=$(vPlant1(3)))'}>}SALES)

Hope this helps you.

Regards,

Jagan.

[rohitians]

Thanks Man...