Help required for code optimization??

[rohitians]

Hello Everyone,

I have this situation where in  i have five bar charts.....i am picking up top five values from the list box which is sorted according to highest sales.....so i pick up first location name and display details for that location in first bar chart.....now thing is  i have to declare five variables ....

vLocation1 =AGGR(IF(RANK((  SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Actual)  +    SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Expected)    ) /100000)=1,Location_Name_Short),Location_Name_Short)   

and in bar chart i am using calculated dimeson as follows..

=if(Location_Name_Short=vLocation1,Upper(Item_Name))

can declaring so many variables be avoided....

Thanks and Regards,

Rohit

[sujeetsingh]

I am seeing only one variable there .

Well it is ok

[rohitians]

Only the rank changes.. every thing else would remain the same..

[sujeetsingh]

Ok give me sample please Rohit

[#5]

Hi,

You want to have seperate chart for each ranked location right, first chart to show item for rank 1 location, second chart to show rank 2 location, etc., In that case, I guess you hav to go with 5 set of variables.

[rohitians]

I have 5 set of variables already i want to avoid those five so is der any way i can do that.....

[jonathandienst]

Hi

You can use a parameterised variable like this:

Set vLocation1 = AGGR(IF(RANK((SUM({$<Item_Ledger_Entry_Type_Code={0}>}Cost_Amt_Actual)  +   SUM({$<Item_Ledger_Entry_Type_Code={0}>} Cost_Amt_Expected)) /100000)=$1,Location_Name_Short),Location_Name_Short)  

And then use this it like this:

=if(Location_Name_Short='$(vLocation1(1))',Upper(Item_Name))

=if(Location_Name_Short='$(vLocation1(2))',Upper(Item_Name))

...

HTH

Jonathan

[rohitians]

Thank you Jonathan I thaught of this but it is showing syntax error... at $1..

[jonathandienst]

Make sure you are using Set and not Let to define Location1. I didnt test so you might need to play with the = signs to get it to work.

Set vLocation1 = =AGGR(IF(RANK((SUM({$...

Test the macro expansion of Location1 in a textbox:

=$(Location1(1))

=$($(Location1(1)))

HTH

Jonathan