Top N,Other and Grand Totals

[rkondaveti]

Hello,

We have  requirements to show Sales amonut by Site. 

Site	Sales	Cost 1	Cost 2	Time PC	Profitability
London	7500
manchster	5434
New York	4560
Hongkong	2300
Beriln	1500
Top 5 sales Total	21294
Others	3400
Total	24694

All measure have complex set analysis formulas.

We are able to develop this using calculated dimension:

=If(Dummy=1 and aggr(rank($(vSales)),Site)<=vTopCustomer,Site,if(Dummy=1 and aggr(rank($(vSales)),Site)> vTopCustomer,Dual('Others Clients',-1),

      if(Dummy=2 and aggr(rank($(vSales)),Site)<= vTopCustomer,Dual('Top ' & vTopCustomer & ' by Sales Total',1),Null())))

we have 4 tabales like this on same sheet by showing same data diffrent dimensions ( like top 10 clients, top 10 projects,...)

All are working fine, but have occupying 50Gb memory  when slect year 2012, 2012 due data for those years and taking 2  mins time load when slection are made.

Is there any best way to impletement to reduce amount memory  it is occupying.

we are on QV 10 , Would it possible to do this using QV 11 dimension limit ?

Cheers,

Raghav

[Miguel_Angel_Baeyens]

Hi,

Yes, Dimension Limits should allow you to get this done with any dimension. In the Dimension Limit tab of the chart properties, select "Restrict which values...", "Show Only", "First", 10 (or any expression you need, note that you can write variables or expressions), then check the "Show Others".

Hope that helps.

Miguel

[rkondaveti]

Hi Miguel,

I tired that i got Others total  and Grand total  using dimension limit but i am not sure how can i get the

Top 5 sales Total

21294

Could you please expain. sample qvw would be greatfull.

Cheers,

Raghav

[Miguel_Angel_Baeyens]

Hi Raghav,

To get that line, just click the "Show Total" option in the Dimension Limit tab.

Miguel

[rkondaveti]

Hi Miguel,

When i click on Show total i get full total rather than top 5 total..

Please check sample app.