Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I am seeing only one variable there .
Well it is ok
Only the rank changes.. every thing else would remain the same..
Ok give me sample please Rohit
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.
I have 5 set of variables already i want to avoid those five so is der any way i can do that.....
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
Thank you Jonathan I thaught of this but it is showing syntax error... at $1..
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