Top 5 in pivot table not working

[infock12]

Hi all,

I looked at various forums and tried to make it work but it looks like I'm missing something.

I have a pivot table with SessionOwner and [Product Name] as Dimensions.

In expression, I have;

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, count(ProductID))

First Column - SessionOwner

Second Column - Product Name

Third Column - I am getting ALL the products but the values are shown only for the top 5 products. However, ALL the other products also appear in the result but with a blank value. How can I just display the top 5 values please?

Any help is appreciated

[trdandamudi]

In the Presentaion tab check "Suppress Zero-Values" and this should take care of the blank values. Hope this helps...

[Anonymous]

Hi Karthik,

you can try what Thirumala has said. you can also try of applying the same expression to other dimensions. But what I guess is what Thirumala suggested will work.

[infock12]

Hi TD,

Thanks for this, but it is already suppressed. I think I wasn't clear in my question. It is actually not a blank value.

Let us say there are 10 products. All 10 products does have a value but they appear as '-'. Only the top 5 values have a number. I have attached an excel spreadsheet of how it appears. Hope it helps.

[infock12]

Thanks AP, I have responded to Thirumala. I tried what Thirumala have suggested but the box was already checked. Basically, I want the pivot table to only show the top 5 products but it is displaying all the products. I have attached the spreadsheet in my previous response if that is helpful.

[sunny_talwar]

Do you have Median Sales and Median cost as your expressions also? You need to force there values to be null or 0 to hide those rows where Top 5 is 0

If(Len(Trim([Top 5])) > 0, YourExpression)

[infock12]

Hi both,

Sorry I must have said this earlier. I am using two more expressions, which are Median({<Cost={'>=10'}>}Cost), Median({<Sales={'>=10'}>}Sales).

I just tried removing those two expressions from the pivot and it works correctly, so it looks like it has to do with the two additional expressions. Do I have to include this in the If(Aggr(.. statement?

[sunny_talwar · Accepted solution]

Try like this

Exp1

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, count(ProductID))

Exp2

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, Median({<Cost={'>=10'}>}Cost))

Exp3

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, Median({<Sales={'>=10'}>}Sales))

[infock12]

Hi Sunny,

You are absolutely right, I just responded to the original post. I have two more expressions. Do you suggest adding your expression, which is If(Len(Trim([Top 5]))>0, Median({<Cost={'>10'}>} Cost))?

[sunny_talwar]

You can do either or...

Use the expression Label (Top 5) to check if it is null or use the actual if statement. I would prefer expression label