topic Re: Ranking Code Optimization?? in QlikView

Ranking Code Optimization??

rohitians — Tue, 10 Dec 2013 05:35:25 GMT

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

Re: Ranking Code Optimization??

rohitians — Thu, 12 Dec 2013 04:50:27 GMT

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

Re: Re: Ranking Code Optimization??

kaushiknsolanki — Thu, 12 Dec 2013 05:45:29 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Ranking Code Optimization??

jonathandienst — Thu, 12 Dec 2013 06:00:33 GMT

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

Re: Ranking Code Optimization??

rohitians — Thu, 12 Dec 2013 06:19:07 GMT

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

Re: Ranking Code Optimization??

rohitians — Thu, 12 Dec 2013 06:20:50 GMT

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

Re: Ranking Code Optimization??

jonathandienst — Thu, 12 Dec 2013 07:50:28 GMT

Nothing attached...

Re: Ranking Code Optimization??

rohitians — Fri, 13 Dec 2013 05:04:42 GMT

Jatin Kolhe

Sample Application

Re: Ranking Code Optimization??

jagan — Fri, 13 Dec 2013 05:40:50 GMT

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.

Re: Ranking Code Optimization??

rohitians — Fri, 13 Dec 2013 05:50:24 GMT

Thanks Man...