Tricky Aggregation in top 10 Values

[sasikanth]

Hello Guys here i have a special requirement ,

I have the following fields

ID , country, region, Cust_cd and year, Sales

Here i want to calculate top 10 customers vs Other customers based on year and country

my formula is

=sum({<Cust_cd={$(=concat(if(aggr(rank(sum(sales),4),cust_cd)<=10,chr(39)&Cust_cd&chr(39)),','))}>}Sales)

and in a bar chart i am taking year and Country as Diemensions ,

when i made any selections on seperate list boxes country and year , then only it is giving correct values,

if i did not select any value then it is showing some other values ,

the problem i have identified is when taking top 10 cust_cd , for the first time it is taking based on total COUNTRY field,

if you select a specific country then only it is showing top 10 values in that country,

as i am taking country as dimension , the chart has to show top 10 valeus accordingly ,

can you guys please help in sloving this problem , please let me know where i am doing mistake ...

Thanks

Sasi

[SergeyMak]

Hi,

Can you share your app or sample of your data

Regards,

Sergey

[sasikanth]

soryy i cannot ,

simple i am taking County as Dim and the above exp as EXPRESSION,

by default it is taking top 10 CUST based on all the countries , if i select on specific county then top 10 based on that country it is taking ,,,

[SergeyMak]

It's hard to say.

Just create a spreadsheet with some dummy data just to save the time and get result faster....

[djsampat]

For top 10 use this:

rangesum(Top(sum(Amount),1,10))

  For all products use this:

sum(total Amount)

or

try this

if(aggr(Rank(Sum(sales),10),region)<=10,aggr(Sum(sales),region,country))

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards
Dhruv

[sasikanth]

Not working

Here top 10 custmers i want , if based on region how can i get top 10 and others ?

[SergeyMak]

Hi,

There is no Country field in your data.

What should I use instead of?

Regards,

Sergey

[sasikanth]

Can any body please help on this...

I have few Regions(4) and Cust_code, i need to calculate /create a barchart like

For every year top 10 values of the region
please check the image once

i have tried using Expression using

=

sum({<CUST_CD={$(=Concat(if(aggr(rank(sum (SALES ),CUST_CD)<=10,chr(39) & CUST_CD & chr(39)),','))}>}SALES)

Note: here common codes are there between Regions ,

Thanks

Sasi

[sasikanth]

any body?

[djsampat]

Please upload a sample Qvw so we can work with your data as an example.