Limit Pivot Report (not chart) to top n customers

[#1]

Hi,

Is there any way I can limit the no. of customers on a pivot chart. I know how to display the top n customers in a normal chart/graph.

For example if a user selects Option A the pivot will display all customers (sorted by the best customers), if a user selects Option B then the pivot will filter out the top n customers.

Thanks!

[eiconsulting]

I am sorry I have half an answer, I used the expression below and a variable (%rank) that you could set with an input box or a button. The value of the salesBYcust is ordered correctly and the lines with a value of 0 should disappear but they don't. I know there are other posts on how to make "disappear" but they do (at least apparently) what I though and it works. If you succed let me know.

=if(num(rank(sum(F2),4))<=%rank,Sum (F2),0)

[johnw]

Yeah, expressions with rank() in them don't seem to suppress properly. I'm not sure why. One way to achieve the same thing is to use a calculated dimension:

if(option='A' or aggr(rank(expression),customer)<=n,customer)

[#4]

Thanks Guys but unfortunately I have tried all combinations without success.

Basically my calculated dimension is =if(analysis_a = '02', g_customer, card_customer) and it works fine.

I want to do something like analysis_a = '02' then show me the top 20 customers or else show me all customers. The closest I have come upwith is

=if(analysis_a = '02' and rank(sum(val)) <=20,g_customer,if(analysis_a <> '02' and rank(sum(val)) <=2000,card_customer))

This formula works as an expression (I have set the upper limit to 2000 as I will never have more than 2000 customers) but when I try to use this formula as a dimension it creates an error??

Question: How can I display the top 20 customers when analysis_a = '02', is analysis_a <> '02 then show me all customers?

[johnw]

Example showing it working just like I suggested. I'm not sure I understand the specifics of your case, such as the difference between g_customer and card_customer. But perhaps this is the dimension you need:

if(analysis_a<>'02' or aggr(rank(sum(val)),g_customer)<=20,g_customer)

[#6]

John/All,

Apologies for the confusion. g_customer and card_customer are different lists of customers. If I select analysis_a = '02' I want to show the top 20 customers from the listing g_customer. If I select an analysis_a <> '02' I want to show a complete list of customers from the card_customer list.

The above equation on my last post works as an expression but when I try to use this as a dimension an error occurs.

thanks,

FC

[johnw · Accepted solution]

if(analysis_a='02',if(aggr(rank(sum(val)),g_customer)<=20,g_customer),card_customer)

[#8]

Excellent line of code, works are treat and many thanks!

[btrompetter]

Hallo,

I use this function. But know the sort didn't work.

The function is:

if(
aggr(
rank(
count( {$< $(=only(Flag)) = { $(=vTotalLFLFilter) } >} AD_ID_COUNT)
),PARTNER
)<=9
,PARTNER)

and I try to sort them with this expression

count( {$< $(=only(Flag)) = { $(=vTotalLFLFilter) } >} AD_ID_COUNT)

But the sort didn't work.

regards

Bastian Trompetter