Here’s a puzzle for you. A set, depending on filters, may have anywhere between 1 and 100 members. If there are more than 5 members, we should see 4 with an aggregated “Other” member. If there are 5 or fewer we should just see them.
We could try to do it with an insane combination of head and topCount and pull end up pulling out far too much hair. Instead, Cognos has a couple built in function that makes this a snap.
_firstFromSet is a function that will return the first N members from a set, with an optional overflow. Let’s say that Product Line has 5 members: Camping Equipment, Golf Equipment, Mountaineering Equipment, Outdoor Protection, and Personal Accessories. If we were to do _firstFromSet([Product Line],4,1) it would return all 5 members. Order Method, on the other hand, has 7 members. _firstFromSet([Order Method],4,1) will return only the first 4.
So this gives us either 4 or a max of 5 members, but how do we get that “Other” member? There is another, similar, function called _remainderSet. If the set in question contains more than a specified number of members, it will return a specified member. If the referenced set contains fewer, it will return an empty set. So let’s see an example:
_remainderSet ( member( currentMeasure - total(currentMeasure within set _firstFromSet([Dim],4,1)) , 'Other' , 'Other' , hierarchy([Dim]) ) , [Dim] , 5 )
So in this case, if [Dim] contains more than 5 members, it will create a calculated member that finds the total for the remaining members.
Putting it together we get:
union( _firstFromSet ( [Dim] , 4 , 1 ) , _remainderSet ( member( currentMeasure - total(currentMeasure within set _firstFromSet([Dim],4,1)) , 'Other' , 'Other' , hierarchy([Dim]) ) , [Dim] , 5 ) )
The report is, as usual, attached below.
Top 5 (936 downloads)
2 thoughts on “Quickie: Top 5 or Top 4 with “Others””
Hey Paul, thanks for replying in advance
I find this approach interesting, and might be helpful for one project I’m working on
I need to create a visualization that shows Qty Sold (measure) by year (lines) for each Week (x axis)
It should look like 1 line for 2017, one for 2016, and 1 line for average from 2013 and 2014
I’ve tested your approach and I think I’m quite close to the solution, but still cannot be able to get the desired result. I identified 2 sets: one from 2017 to 2015 and one group for 2013-2014. For the later one I’ve set average (currentMeasure within set [2Years]) but when I try to show them by week number, it shows the same value always instead of showing average for WeekNo for those two years.
Might be a little hard to explain here, but any idea what I’d need to review?
Thanks in advance
If I understand, you have a set of weeks (1-52) and you’re trying to show separate lines for 2015-2017 and 2013-2014? How are the weeks related to the years in your model? Do you have the capability to change the model at all?