Summarize a category after a certain rank

[ArnaudStephanHTH]

Hello,

My data looks like that :


 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

Week	Section	Quantity
20	A	2
20	B	5
20	C	4
20	D	3
20	E	10
20	F	6
20	G	5.5
20	H	9
21	A	1
21	B	4
21	C	7
21	D	4
21	E	2.4
21	F	3
21	G	6
21	H	5

And I would like to show it in a pivot table, but I would like to only show the top 5 sections by week, summarizing the remaining sections in an "Other" category.

I created a rank like this : rank([Quantity] for [Week];[Year]) and filtered to only include the 5 top ranks. It works fine, but I don't know how to create the "Other" category and display it.

Any ideas on how to proceed ?

[ArnaudStephanHTH]

I found a solution, although I'm sure there are better ways.

First I created a new data item to categorize the ranks : CASE WHEN [Rank] < 6 THEN [Rank] ELSE 999 END
Then I computed the total based on this new data item :total ([Quantity] for [Rank Inf 5])

[MatusM]

I found a solution, although I'm sure there are better ways.

First I created a new data item to categorize the ranks : CASE WHEN [Rank] < 6 THEN [Rank] ELSE 999 END
Then I computed the total based on this new data item :total ([Quantity] for [Rank Inf 5])

I have the same use case, but this solution is not working properly on Cognos 12.0.3.

Totals of top X items vs Other are not computed correctly, probably because Rank is being determined not in the beginning, but in parallel with Total calculation.

Solve order property of data items does not help either.