Quickie: Top 5 or Top 4 with “Others”

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.

Top 4

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 (76 downloads)

Back To Top