TAIL MDX Function in TM1: Syntax, Use and Examples

Definition

The TM1 MDX function TAIL in IBM Planning Analytics is utilized to return the last element within a set by default. TAIL MDX Function also returns specified number of elements from the last part of “set of elements” retrieved based on specified criteria. TM1 MDX function can be applied in conjunction with other MDX functions like FILTER, TOPCOUNT etc.

Syntax of TAIL MDX

Here is the syntax for the TAIL function :

TAIL ( Set, [Count] )

Where:

• Set: a set of one or more elements, either derived using MDX or as a static set.
• Count: is a positive number, this is optional and defaults to 1 if not set.

Examples of the TAIL MDX Function

Here we will use an existing subset called Balance Sheet from the Account dimension. We will illustrate below using the TAIL function with both No Count and Count to show the difference. This is a set with more than 20 elements as illustrated here:

TAIL MDX with No Count

Now let’s see how we can use the TAIL function without the Count operator in MDX and see the results.

TAIL ( TM1SubsetToSet([Account] , "Balance Sheet" , "public") )

As you can see below, using TAIL without the Count operator, it will assume a count of 1 and therefore display the last element of the set.

TAIL MDX with Count

Let’s now use a the Count operator along with a set and see the results.

TAIL ( TM1SubsetToSet([Account] , "Balance Sheet" , "public"), 5 )

In the above MDX we used 5 as count and we now see the last 5 elements from the subset Balance Sheet.

Limited to Subset?

Is the TAIL MDX function limited to just a Subset? The answer is not really. The function needs a set, it can be a subset or a complex MDX which returns set of elements, below are few examples to illustrate this.

Complex Set with TM1FILTERBYLEVEL

Get all leaf elements which are descendants of BSHT consolidation which holds value > 1000 for the Actual version in General Ledger cube and apply the TAIL MDX function to this set with count as 2.

General Ledger cube :

The cube in context here is the General ledger cube as displayed above. We will illustrate it by adding in the FILTER, TM1FILTERBYLEVEL, DISTINCT and DESCENDANTS functions. I have spread the MDX over multiple lines and indented it to make it easier to read, but in reality it would probably be presented as a long string of code.

TAIL(
 FILTER(
   TM1FILTERBYLEVEL(
    DISTINCT(
     DESCENDANTS([Account].[Account].[BSHT]) 
    ) 
  , 0) 
 ,[General Ledger].([Version].[Version].[Actual]) > 500000)
, 2 )

In the above example, have presented the last two elements from the descendants of the BSHT element where the Actual version is greater than 500000.

Complex Set with TOPCOUNT

Let us apply TAIL MDX to the set which generates top 10 count of elements. The cube in context here is the General ledger cube as displayed above. We will illustrate it by adding in the TM1SubsetToSet and TOPCOUNT functions.

Here we have an TAIL MDX to find the TOP 10 elements from the subset Leaf level.

TOPCOUNT(TM1SubsetToSet([Account].[Account] , "Leaf level" , "public") , 10.0 , [General Ledger].([Version].[Actual],[Time].[All Year],[Currency].[Local],[Branch].Total Company]))

Below is the screenshot displaying the outcome from the MDX.

Let’s now get only the last 4 elements from this set.

TAIL(
TOPCOUNT(TM1SubsetToSet([Account].[Account] , "Leaf level" , "public") , 10.0 , [General Ledger].([Version].[Actual],[Time].[All Year],[Currency].[Local],[Branch].[Total Company]))
, 4 )

Here in the below screenshot, after applying TAIL MDX function on top of the earlier MDX with TOPCount function, with 4 as count, we now get below outcome

HEAD MDX?

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