Are you using any calculated dimension in your chart expression? Please provide what expression and dimensions you have used in your chart.
As you have near about 10 years of data in your fact table and you are creating chart only on 2011 year data then probably you can extract latest year (2011) data in another fact and use that fact for this chart.
The other possible solution would be you use database rank function in SQL query and create another fact table in script itself which is having top 5 countries base on 2011. But if you are using QVDs then probably you need to create a seperate QVD for this functionality.
Hope this help,
I have built two straight tables. One consists of the yearly data for each country from 2000-2011 and the other contains the monthly data of 2011 and 2012-till date. It also contains the month on month % change and YTD calculations.
is there any way of getting it fixed in the script only while i'm doing a reload?
As per my understanding in your first table while showing yearly data for each country from 2000-2011 you display only top 5 countries that have highest sales in year 2011and you are displaying the same countries against all 11 years.
In your next table you are diplaying the top 5 countries on monthly bases for months between 2011 to 2012 till date.
If my understanding is correct you can do two things in script.
1. For first chart - In you script create seperate table having yearly data for all countries. Create seperate table to find out top 5 countries id using order by clause. Use these ids in chart for creating claculated dimension for displaying these 5 countries against all years and group others.
2. For second chart - In you script create seperate table having 2011 - 2012 monthly data for all countries. Now use this table in your chart and use dimension limit to limit no of countries based on sales.
It would be good if you create some dummy data and a qvw to explain your scenario better. Then I or someone else on this forum can probably understand your problem clearly and provide you solution.
Hope this help,