Using Rank

[#1]

Hi all,

Below is the table i am using

Customer name	Sale
A1	938
A2	324
A3	302
A4	463
A5	427
A6	0
A7	277
A8	387
A9	0
A10	183

i need create two  tables for top 5 and bottom 5 . In bottom 5 i need to exclude whose customer having "zero" in their sales    . User is flexible to select any top n or bottom  n b/w 5 (i.e., top 1-2 or bottom 2-3 or top 3-4)  .

Could any one help me .

Thanks in advance

[swuehl]

Create two tables, Customer name being dimension in both.

Then as expression

=Sum({<CustomerName = {"Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

[#3]

Hi Stefan,

Thanks for reply..

could you kindly provide me the expressions for $(vTopRank) and $(vBottomRank).

But however, the above expression helps me to filter out the data according to top or bottom N. but my requirement is  if i wanna to see only top 2 and 3 customer sales or bottom 2 and 3 customer sales .

Thanks in advance

[swuehl]

vTopRank and vBottomRank are just variables that hold a number (Top or Bottom N customers).

I thought that's what you intended (creating e.g. a slider so the user can select N)

I've  also noticed I forgot the leading equal sign in the set modifier to indicate an advanced search:

=Sum({<CustomerName = {"=Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)

resp.

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)

[#5]

Hi stefan,

Thanks for previous reply..

But when i using ur bottom expression(for bottom 4 customer) . the output is show below

but the user wanna to only bottom 2 to 4 records

that is, he wanna to see only

Kindly help me ..

Thanks in advance

[swuehl · Accepted solution]

You can add a range to the condition like

=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)

[#7]

hi,

Thanks a lot ...

[#8]

Hi

If we want to see along with zero values for bottom customers ?

i have removed >0 from the set analysis but i m not getting correct values .

Thanks in advance

[#9]

Hi ,

i have a varable

vProdVar= sum(aggr(if(IsNull(Map_Customername) or not( IsNull(Map_Key)),'',TotalMargin),Key,[customer name]))+sum(aggr(if(IsNull(Mapping_Customername) or not( IsNull(Mapping_Key)),'',[Total margin]*-1),Key,[customer name]))

in straight table .. i m using the below dimension  for selecting bottom customers.

AGGR(IF(Rank(-$(vProdVar),4)>=$(vMin) AND

          (Rank(-$(vProdVar),4)<=$(vMax)) ,[customer name] ),[customer name] )

i am getting error with rank function . Could you kindly help me ..

Thanks advance

[swuehl]

Not sure what your complex variable should return.

Could you post a small sample QVW that demonstrates also your data model?