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 ?
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])
Quote from: ArnaudStephanHTH on 30 Sep 2019 06:55:05 AMI 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.