TM1: Top N Elements from a Tuple

Writing MDX statements is a very helpful skill for a TM1 person, but what if you need to automate this task and generate many MDX statements? TI process in conjunction with excel formulas might save you in this tricky situation.

Say the requirement is to have Top 5 lists of products in each product category or in each country or Top 3 products for Top 3 product categories. In this case, you would need to create many subsets or hierarchies (in case you need a consolidated value), the easiest way to achieve this is to write a TI process that will do it for you and then use its output to generate Excel reports.

So the task for today is to generate a report for top 3 product categories and within these categories generate lists of top 3 products and to top it up, it should be done by channel.

The sample data is coming from Great Outdoors model.

Rolling up products to product category level makes the task a bit clearer:

Total number of product subsets that we need is 24: 8 subsets per channel times 3 product lines each.

In this case top to bottom approach is to be used to break this task into steps:

1. Generate Top 3 Subsets/Hierarchies for Product categories by Channel (results in getting 8 subsets with 3 elements each)
2. For each of these subsets generate sub-subset with Top 3 products within that range.

The TI that generates these subsets it the following,  which is not as customisable as it should be, but using the previous blog post, you should be able to modify your MDX statements:

PROLOG TAB:

cTopElements=3;

cDimName='products';


DATA TAB:

sLevel1Subset=vElement | ' top 3';

IF(SUBSETEXISTS(cDimName,sLevel1Subset)=1);

SUBSETDESTROY(cDimName,sLevel1Subset);

ENDIF;

#Generate Base Consolidated subset

cMDXExpression=’

{

HEAD(

ORDER(

{TM1FILTERBYLEVEL( {TM1SUBSETALL(

[price and cost].([Versions].[Budget Version 1],

[months].[Total Year],

[price and cost measures].[Unit Sale Price],

[channels].[‘|vElement|’]), BDESC),

‘|NUMBERTOSTRING(cTopElements)|’)

}’;

SubsetCreatebyMDX(sLevel1Subset, cMDXExpression);

#Generate level0 subsets

i=1;

#Loop through all elements of top subsets in order to get lower level subsets

WHILE(i<=SUBSETGETSIZE(cDimName,sLevel1Subset));

sElement0Level=SubsetGetElementName(cDimName, sLevel1Subset, i);

sLevel0Subset=vElement |’ ‘| sElement0Level | ‘ top 3′;

cMDXExpression=’

{

HEAD(

ORDER(

{TM1FILTERBYLEVEL( {TM1SUBSETALL(

[price and cost].([Versions].[Budget Version 1],

[‘|cDimName|’].[‘|sElement0Level|’],

[months].[Total Year],

[price and cost measures].[Unit Sale Price],

[channels].[‘|vElement|’]), BDESC),

‘|NUMBERTOSTRING(cTopElements)|’)

}’;

SubsetCreatebyMDX(sLevel0Subset, cMDXExpression);

i=i+1;

END;


This is the result of the TI:

Now the easiest way to output this information is to use excel report:

Excel Report: top3_top3_channel