Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I usually use
dimension = if( aggr( rank( sum(Total_Amt)) , Idcustomer ) <= 10 , Idcustomer ) display the top 10
expression = sum(Total_Amt)
jj
Thanks JJ,I have tried as you have suggested but pivot table still displays all the customers does not limit 10.Pls suggest.
-Swetha
Could anyone help as it is very uregent.Pls help.
I don't understand because it works perfect in one of my app.
Maybe you can post your application to understand the bug.
JJ
I am unable to attcah the application but enclosed is the screen shot.
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
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
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)