Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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....
Hi,
Here is a dynamic way, but user will have to enter the rank.
Regards,
Kaushik Solanki
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
Thanks Kaushik its a good idea but I dnt have that option of user selecting rank....
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
Nothing attached...
Sample Application
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.
Thanks Man...