Top N customers not displaying top n but all teh results.

[#1]

Hi All,

I want to display the top 250 customers using pivot table I have used an expression below :

if(aggr(rank(Sum(Total_Amt)),Customer_Mailing_Name,Customer_Address_Line_1,Customer_City)<=10,Sum(Total_Amt))

I am not sure if I have to add the above expression as calculated dimension or as an expression.

And also the above expression returns me all the customers does not limit to 250 which is my concern.

Please help in this issue.

Thanks,

Swetha

[#2]

Yes but create a unique field in the script to display customer information :

Load *

Customer_Mailing_Name,

Customer_Address_Line_1,

Customer_City,

Customer_Mailing_Name & ' ' & Customer_Address_Line_1 & ' ' & Customer_City as Customer_Id,

....

Then you can apply the formula

=IF(Aggr(Rank(Sum(Total_Amt)), Customer_Id )<=250, Customer_Id)

jj

[#3]

Hi JJ,

I tried the option suggested by you but it still displays all the rows doe not limit to 250.

Can anyone help me in this regrad.

Thanks,

Swetha

[#4]

Sorry ,but it limits to 250 but not displaying from higher to lower.the order is zigzag.

[#5]

I could even get it sorted so basically one problem is solved.

One last question is that my Customer_id filed has customer_mailing_name,address_,city combine as one field but if I want to see them as sepaarte fileds in pivot table,Is n't it possible?Pls suggest.Your answers are highly appreciable.

Regards,

Swetha

[#6]

Any Experts,Pls answer.

[#7]

Hi Sweeta,

Did you try with a straigth table instead of pivot ?

Then try with 2 dimensions :

Customer_Mailing_Name,

Customer_Name & ' ' & Customer_Address_Line_1 & ' ' & Customer_City

If you post your app, i'm sure to be able to help you.

Jean-Jacques

[#8]

Hi JJ,

I have changed the code as you have suggested for pivot table and it worked fine.But my only concern is that:

1.Customername,address,city,state,zip are appearing as one column as enclosed in teh word doc.Can it be not represented as sepaarte cols instead of one column.

I am unable to send the application as it too big for an attachment.

I have alos tried using straight table but there also it works fine only with one dimension if I add more than one dimension the order jumbles.Pls suggest.

Thanks,

Swetha

1.

[sathishkumar_go]

Hi Swetha,

Just try this in dimension

=IF(Aggr(Rank(Sum(Total_Amt)),Customer_Mailing_Name,Customer_Address_Line_1)<251,Customer_Mailing_Name,Customer_Address_Line_1)

Regards

Sathish

[sathishkumar_go]

Hi swetha

Sorry for my previous It won't work.

just try this in dimension

IF(Aggr(Rank(Sum(Total_Amt)),Customer_Mailing_Name) < 251, Customer_Mailing_Name)

Regards

Sathish

[#11]

Thanks Satish.

But your expression would only gives me top 250 customers by customer_mailing_names.But I would also need their addresses not just mailing_names.Pls suggest if possible.

Thanks,

Swetha