Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
rohitians
Creator III
Creator III

Ranking Code Optimization??

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

9 Replies
rohitians
Creator III
Creator III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rohitians
Creator III
Creator III
Author

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

rohitians
Creator III
Creator III
Author

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
Partner - Champion III
Partner - Champion III

Nothing attached...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rohitians
Creator III
Creator III
Author

Jatin Kolhe

Sample Application

jagan
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

Thanks Man...