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

[infock12]

Hi Sunny,

This works perfectly, thanks for that. Just one last question. I want to sort by the first expression descending. Inside the 'Sort' option, I tried different things such as unticking all the boxes and tried sort by first expression, didn't do it. Please could you advise?

[sunny_talwar]

Did you try sort by y value?

[infock12]

When I go into the Sort field, I tried the first Dimension (Session Owner) and selected Y value, but it didn't work. The second dimension Product Name does not give me the option of Y value

[sunny_talwar]

Would you be able to share a sample or a screenshot of what you have? and a little explanation of what you are looking for?

[infock12]

I am trying to sort the 3rd column descending (first expression) for each SessionOwner(first column). The first two columns are dimensions.

[sunny_talwar]

See if this discussion offers any help

Pivot Table - Sort top article per month

[infock12]

Thanks Sunny, will have a look and let you know

[sunny_talwar]

Sounds good

[Anonymous]

Hi Karthik,

Follow following steps:

Click on Properties-->Dimension--> click on suppress  when value in null

In this way you will get result for top 5 values only.

Thanks,

[infock12]

Thanks Pooja, very helpful. Sorted this out with Sunny's response.