MDX COUSIN FUNCTION

MDX COUSIN FUNCTION

by

In MDX, the Cousin function will write the same level member under the Ancestor specified in the second parameter. For example, If you know the value of the first Quarter in Calendar year 2014. And you want to find the sales of the last Quarter in the Calendar year 2015. Then you can use this MDX Cousin function.

MDX Cousin Function Syntax

The basic syntax of the MDX Cousin is:

COUSIN( Member_Expression, Ancestor_Member_Expression )
  • Member_Expression: Any Multidimensional Expression that returns valid Member. It will find the member Level. For instance, whether the member is the first month of a quarter, semester or Year, etc.
  • Ancestor_Member_Expression: Any Multidimensional Expression that returns valid Ancestor Member. The MDX Cousin function will find the Cousin of First argument present in this Ancestor_Member.

In this article, we will show you how to write the Cousin function in the MDX query with examples. For this, We are going to use the below show data

MDX COUSIN FUNCTION

MDX Cousin Function Example

In this example, we are going to use Cousin function to find the cousin of Q1 CY 2012 in Calendar Year 2013

SELECT 
 {
  [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
  COUSIN (
      [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
      [Date].[Calendar].[Calendar Year].[CY 2013]
	 )
  } ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]
MDX COUSIN FUNCTION 1

In the above MDX Query, We used [Internet Sales amount] on the Rows

[Measures].[Internet Sales Amount] ON ROWS

The first line of code after the SELECT will check for Q1 CY 2012 and write the Internet sales amount of Q1 CY 2012. As we all know, Q1 CY 2012 is the First Quarter in Calendar year 2012.

[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],

Below line of MDX cousin code will check for the First Quarter in the Calendar year 2013( because Q1 CY 2012 is the First Quarter in the Calendar year 2012) and then write the Internet sales amount of Q1 CY 2013

COUSIN (
      [Date].[Calendar].[Calendar Quarter].[Q1 CY 2012],
      [Date].[Calendar].[Calendar Year].[CY 2013]
	)

MDX Cousin Function Example 2

In this example, we are going to use Cousin function to find the cousin of January 2012 in the Fourth Quarter of Calendar Year 2013

SELECT 
 {
  [Date].[Calendar].[Month].[January 2012],
  COUSIN (
      [Date].[Calendar].[Month].[January 2012],
      [Date].[Calendar].[Calendar Quarter].[Q4 CY 2013]
         )
  } ON COLUMNS,
  [Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

MDX COUSIN FUNCTION 3

In the above MDX Query, We used [Internet Sales amount] on the Rows

[Measures].[Internet Sales Amount] ON ROWS

The first line of MDX Cousin code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calendar year 2012.

[Date].[Calendar].[Month].[January 2012],

Below line of code will check for the First Month of the Calendar Quarter Q4 CY 2013 (i.e., October) and then write the Internet sales amount of October 2013

COUSIN (
        [Date].[Calendar].[Month].[January 2012],
        [Date].[Calendar].[Calendar Quarter].[Q4 CY 2013]
        )

NOTE: If we replace the Calendar Quarter from the above lines of code with Calendar semester 2, MDX Query will write the First month in semester 2

Finding Range Using MDX Cousin Function

In this example, we are going to calculate the Range between one point to another using the MDX Cousin Function.

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  { 
   [Date].[Calendar].[Month].[January 2012]:
   COUSIN (
      [Date].[Calendar].[Month].[January 2012],
      [Date].[Calendar].[Calendar Quarter].[Q2 CY 2013]
	  )
  } ON ROWS
FROM [Adventure Works]

MDX COUSIN FUNCTION 4

The first line of code after the SELECT will check for January 2012 Month and write the Internet sales amount of January 2012. As we all know, January is the First Month of the Q1 CY 2012 in the Calendar year 2012.

[Date].[Calendar].[Month].[January 2012],

Below line of MDX cousin code will check for the First Month of the Calendar Quarter Q2 CY 2013 (i.e., April)

 COUSIN (
      [Date].[Calendar].[Month].[January 2012],
      [Date].[Calendar].[Calendar Quarter].[Q2 CY 2013]
	 )

Now the range Operator (:) between the above statements will write all the Calender Month from January 2012 to April 2013.