Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
In the Presentaion tab check "Suppress Zero-Values" and this should take care of the blank values. Hope this helps...
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.
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.
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.
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)
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?
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))
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))?
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