MDX Get a TopCount over multiple dimensions and add a rest/remainder (icCube)

Question

I would like to create a TopCount over multiple dimensions, and include a "rest"/"remainder" and a sub-total.

I use the following MDX on the default Sales schema in icCube:

with
   
   member [Product].[Product].[All Products].[rest] as "All Products - top 2"
   
   SET [top] AS    
   Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
          TopCount(  s1.CurrentMember * [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) +  s1.CurrentMember * {[Product].[Product].[All Products].[rest] , [Product].[Product].[All Products]} )
   
select
   
   [Measures].[Amount]  on 0
   [top]      on rows
   
   from sales

The result is in the following picture.

How to get a value for "rest"? Using the formula: "All Products" -/- the Top 2

score 3 · Accepted Answer · answered Feb 01 '22 at 11:57

The answer will depend on whether the dimension includes many-to-many relations or not.

If there are no many-to-many, you can either use the SubCubeComplement function:

MEMBER [Product].[Product].[All Products].[rest] as Eval(  SubCubeComplement( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]) ) ,  [Product].[Product].defaultMember )

or calculate it (all minus the sum of the TopCount set):

MEMBER [Product].[Product].[All Products].[rest] as  ([Product].[Product].defaultMember) - Sum( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].currentMember )

The risk here is that in case you have many-to-many relations, the two solutions above can be subtracting unwanted rows (as they may contain articles that should have been in the final set.)

Therefore, if you have many-to-many relations use the Eval function with the following syntax:

MEMBER [Product].[Product].[All Products].[rest] as  Eval( [Product].[Product].[Article].Members -  TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].defaultMember )

The statement will therefore be (please note the adjustment on the [top] set definition):

with
   
// v1 (no many-to-many) - behaves like a FILTERBY
   // MEMBER [Product].[Product].[All Products].[rest] as Eval(  SubCubeComplement( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]) ) ,  [Product].[Product].defaultMember )
   
//v2 (no many-to-many)
   // MEMBER [Product].[Product].[All Products].[rest] as  ([Product].[Product].defaultMember) - Sum( TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].currentMember )
   
//v3 (many-to-many)
   MEMBER [Product].[Product].[All Products].[rest] as  Eval( [Product].[Product].[Article].Members -  TopCount(   [Product].[Product].[Article].Members, 2, [Measures].[Amount]), [Product].[Product].defaultMember )
   
   SET [top] AS    
   Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
          s1.CurrentMember * TopCount( [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) +  s1.CurrentMember * {[Product].[Product].[All Products].[rest] , [Product].[Product].[All Products]} )
   
select
   
   [Measures].[Amount]  on 0
   [top]      on rows
   
   from sales

Version 3 will also work if there are no many-to-many relations.

score 1 · Answer 2 · answered Feb 01 '22 at 11:28

After some time puzzling I found the solution.

The solution is a combination of icCube's MDX++ functions, namely SubCubeMinus in combination with the category member.

Here's the answer:

with
   
   SET [top 2]  as
   Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
          TopCount(  s1.CurrentMember * [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) )
   
   category calculated member  [Product].[Product].[All Products].[rest] as subcubeminus(([Customers].[Geography].[All Regions] , [Time].[Calendar].[2010], [Product].[Product].[All Products]) , [top 2])
  
   SET [top 2]  as
   Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
          TopCount(  s1.CurrentMember * [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) )
   
   SET [tuples_top2_rest_total] AS    
   Generate( { {[Customers].[Geography].[Region] } * [Time].[Calendar].[2010] } as s1,
          TopCount(  s1.CurrentMember * [Product].[Product].[Article].Members, 2, [Measures].[Amount] ) +  s1.CurrentMember * {[Product].[Product].[All Products].[rest] , [Product].[Product].[All Products]} )
   
select
   
   [Measures].[Amount]  on 0
   [tuples_top2_rest_total]   on rows
   
from sales

Result

Explanation

Create the Top 2 using the Generate function;
Create the calculated category member "rest" (ie others) all the data in the cube for Total Region, 2010 and Total Product MINUS the TOP 2 per combination for region and 2010.

MDX Get a TopCount over multiple dimensions and add a rest/remainder (icCube)

2 Answers2