HEAD MDX Function in TM1: Syntax, Use and Examples

Definition

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

Syntax of HEAD MDX

Here is the syntax for the HEAD function :

HEAD ( 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 HEAD MDX function

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

HEAD MDX with No Count

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

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

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

HEAD MDX with Count

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

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

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

Limited to Subset?

Is the HEAD 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 version: Actual in General Ledger cube and apply the HEAD 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.

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

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

Complex Set with TOPCOUNT

Let us apply the HEAD 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 a TOPCOUNT 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 top 3 elements from this set from the above mentioned MDX.

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

Here in the below screenshot, after applying the HEAD MDX function on top of the earlier MDX with TOPCOUNT function, with 3 as count, we now get below outcome.

TAIL MDX?

If you are looking for the opposite of the HEAD 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.