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]

swethathoom posted on on, jan 27 2010 12:01

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

[#3]

I usually use

dimension = if( aggr( rank( sum(Total_Amt)) , Idcustomer ) <= 10 , Idcustomer ) display the top 10

expression = sum(Total_Amt)

jj

[#4]

Thanks JJ,I have tried as you have suggested but pivot table still displays all the customers does not limit 10.Pls suggest.

-Swetha

[#5]

Could anyone help as it is very uregent.Pls help.

[#6]

I don't understand because it works perfect in one of my app.

Maybe you can post your application to understand the bug.

JJ

[#7]

I am unable to attcah the application but enclosed is the screen shot.

[#8]

what is your formula syntax to get the top10 ?

I suspect you want to display only the top10 customer for each mailing operation

so you need in the pivot only 2 dimensions :

customer_mailing, if( aggr( rank( sum(Total_Amt)) , Idcustomer ) <= 10 , Idcustomer ). Don't put as dimension the address and the city

if you dont't have an id for customer, create one in the script :

customer_name & ' ' & customer_address as Idcustomer

hope it will be OK.

jj

[#9]

Thanks JJ.

You mean I can only take two dimensions and then only the above formula wud work?

But my requirements is that the pivot table shud display the customer_mailing_name,address and city of top 250 customers spend.

My expressions is as below:

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

Thanks,

Swetha

[#10]

Sorry,cirrected below:

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