top 5 for the 3rd dim in a pivot

[ilanbaruch]

hi all

in my model i have a large pivot table..

as there is no option of dimension limit in a pivot i would want to present top 5 of the third dim in the pivot

and also to present 'Others'

any ideas ?

advanced thanks

[Jacek · Accepted solution]

Example with calculated dimension for TOP 3

=if( aggr( NODISTINCT rank( sum(Data) ), Category1,Category2, Quarter) <=3 , Quarter)

+ option checked: suppress when value is null

Result before (full data) and after (top 3):

[ilanbaruch]

Hi, Thank you !

[ilanbaruch]

Hi Jacek,

in the same pivot i have another expression,   sum(Total <Category2> Data)  to calculate the relative for each row.

after the change, it doesn't retrieve the (Total) number for each row

maybe you you have an idea  ?

[Jacek]

please try: aggr(NODISTINCT sum(TOTAL <Category2> Data), Category1,Category2)

[ilanbaruch]

Thank you very much