TOPCOUNT MDX Function in TM1: Syntax, Use and Examples

Definition

The TOPCOUNT MDX function in Planning Analytics allows you to return a defined number of elements from a set. Optionally, you can have them in descending order using a numeric expression. This TM1 MDX function can be applied along with other MDX functions like TM1FILTERBYLEVEL, TM1SUBSETALL etc.

Syntax of TOPCOUNT MDX

Here is the syntax for the TOPCOUNT function :

TOPCOUNT ( Set , Count [,Numeric Expression] )

Where:

• Set: a set is a valid MDX expression which returns a set.
• Count: is a positive number, defines the number of elements to be returned from the set.
• Numeric Expression: this is optional, a valid MDX expression (cells) that returns a number not string. If used, the TOPCOUNT MDX function sorts the set in descending order according to the numeric expression and returns the number of elements defined in Count.

Examples of the TOPCOUNT MDX Function

Here we will use a Product dimension to illustrate the functionality of the TOPCOUNT MDX function. The Product dimension structure is shown below.

The leaf elements within the Product dimension is over twenty plus.

TM1FILTERBYLEVEL(TM1SUBSETALL([Product].[Product]) , 0)

The cube referred here is Gross profit, structure is as below.

In the screenshot, the Product dimension is in rows and Unit price, Unit Sold and Unit cost measures are in columns.

TOPCOUNT MDX with Count

Now let’s apply the TOPCOUNT MDX function with a count of 5 on the MDX function referred above.

TOPCOUNT(TM1FILTERBYLEVEL(TM1SUBSETALL([Product].[Product]) , 0) , 5)

Here is the results:

By applying the TOPCOUNT MDX function with the count of five results in the last five elements from the set. Note the first element in the result is the last element from the set of 20 odd products. In the image below, we have the cube result after applying the function.

The data is now displayed for the five elements resulted from the MDX applied on the Product dimension.

TOPCOUNT MDX with Count and numeric expression

The last part of the syntax is to add the Numeric Expression. This will use the measure identified to give us an outcome based on cube data.

Here we are going to add a reference to Gross Profit cube, for the Actual version and use the Unit Price measure.

TOPCOUNT( TM1FILTERBYLEVEL( TM1SUBSETALL( [Product].[Product] ) , 0 ) , 5 , ( [Gross Profit].( [Version].[Version].[Actual] , ], [MEASURES - GROSS PROFIT].[MEASURES - GROSS PROFIT].[UNIT PRICE] )))

We now see different setup of elements than the five elements we saw above. Let’s now click apply and see how the data looks after applying the function with numeric expression.

The result seems awesome, the data looks sorted by Unit Price value in descending order and also the data displayed for only five elements, data sorted by Unit Price only.

In case we want to sort data by Units sold, just replace Unit Price with Units Sold as shown in the MDX below.

TOPCOUNT( TM1FILTERBYLEVEL( TM1SUBSETALL( [Product].[Product] ) , 0 ) , 5 , ( [Gross Profit].( [Version].[Version].[Actual] , ], [MEASURES - GROSS PROFIT].[MEASURES - GROSS PROFIT].[UNITS SOLD]  ) ) )

In above image, five different products are sorted in descending order with TOP units sold being first element.

TOPCOUNT MDX, HEAD MDX PROVIDE SAME RESULT?

Do the TOPCOUNT MDX and the HEAD MDX function generate same result ? The answer depends on the way we use the TOPCOUNT function.

The TOPCOUNT MDX function without a numeric expression returns the specified number of elements with no sort, result would match HEAD MDX Function.

The result would be very different to HEAD MDX function when we use TOPCOUNT MDX function with numerical expression. The TOPCOUNT MDX function is a powerful tool that lets you sift through your multidimensional datasets and effortlessly extract the top performers based on your chosen criteria.

BOTTOMCOUNT MDX?

If you are looking for the opposite of the TOPCOUNT MDX function, please head over to this post on the BOTTOMCOUNT MDX Function.

MDX Knowledge

We trust above content would have helped you gain some knowledge. To continue learning MDX check out our series of posts all on MDX. In addition to these, for MDX related information you may visit this list on the IBM website .

Do you need Help with MDX?

If you’d like some help with MDX in TM1/Planning Analytics – be it a simple tip to correct some code, or some assistance building new reports or PAW dashboards, please get in touch. We’d be delighted to help.