BOTTOMCOUNT MDX Function in TM1: Syntax, Use and Examples

Definition

The TM1 MDX function BOTTOMCOUNT in IBM Planning Analytics, returns the specified number of elements starting from the last element of a “set of elements” retrieved based on specified criteria. BOTTOMCOUNT MDX function also returns specified number of elements in ascending order by number as defined in a numeric expression. TM1 MDX functions can be applied along with other MDX functions like TM1FILTERBYLEVEL, TM1SUBSETALL etc.

Syntax of the BOTTOMCOUNT MDX Function in TM1

Here is the syntax for the BOTTOMCOUNT function :

BOTTOMCOUNT ( 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 , it is a valid MDX expression (cells) that returns a number not string. If used, the BOTTOMCOUNT MDX function sorts the resulting set in ascending order according to numeric expression for the number of elements defined in Count.

Examples of the BOTTOMCOUNT MDX Function

Here we use a Product dimension to illustrate the functionality of the BOTTOMCOUNT MDX Function. The Product dimension structure is as shown in below image.

The leaf elements within Product dimension is over twenty plus.

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

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

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

BOTTOMCOUNT MDX with Count

Now let’s apply BOTTOMCOUNT function with a count of seven on the MDX function referred to above. Let’s see the results.

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

By applying the BOTTOMCOUNT MDX function with the count of seven results in the last seven 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 seven elements resulted from the MDX applied on the Product dimension.

BOTTOMCOUNT 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.

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

We now see different setup of elements than the seven 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 ascending order and also the data displayed for only seven 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 below MDX.

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

In above image, seven different products are sorted in ascending order with least units sold being the first.

BOTTOMCOUNT MDX, TAIL MDX ARE THEY SAME?

Does the BOTTOMCOUNT MDX function and the TAIL MDX function generate same result? The answer depends on the way we use the BOTTOMCOUNT function.

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

The result would be very different to the TAIL function when we use the BOTTOMCOUNT function with a numerical expression. The BOTTOMCOUNT MDX function is a powerful tool that lets you sift through your multidimensional datasets and effortlessly extract the non performers or cost centres with little expense, based on your chosen criteria.

BOTTOMCOUNT MDX?

If you are looking for the opposite of the BOTTOMCOUNT MDX function, please head over to this post on the TOPCOUNT 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.