limit the number of results for a given dimension in a pivot table

[#1]

Does anyone know of a way to limit the number of results displayed for an expresion for a dimension in a pivot table?

I have a table that contains sales which has a date dimension across the top and customers as dimension 1 and items as dimensions 2 down the side. I would like to see all customers but only the top 3 items for each customer. Is this possible without doing prior analysis to identify those customer/item combinations and then loading those records as a dimension?

Also on the wish list is to be able to display the total sales for each customer below their top 3 items but I suspect that this would be contrary to the logic of a pivot table and would need to be tackled through a straight table. Any thoughts?

As always, all help is much appreciated.

[#2 · Accepted solution]

Hi,

you need a dynamic dimension like:

=Aggr(If(Rank(Sum(Amount)) <=3, Company, 'Others'), Company)

So you solve the first part of your questions.

Rainer

[#3]

How do you want to identify top 3 products for any customer? You can write a set expression to get top product on the basis of some metrics e.g. top3 products w.r.t sales,etc.

[#4]

Good question. I hadnt thought up to now but I guess I will build a set based on last month or last quarter.

Regarding the aggr function above, how might that look in terms of my customer and item dimensions? I have been having a play around with it and have hit some good pointers but also some unexpected results.

edit:

This may be the expression I was looking for (apart from the set part)

=aggr(if(rank(sum(sales))<=3, Item,'Others'),Customer,Item)