Tutorial Gateway

MDX COUSIN FUNCTION

by

In Multidimensional Expression, Cousin function will write the same level member under the Ancestor specified in the second parameter. For example, If you know the value of first Quarter in Calender year 2014 and you want to find the sales of last Quarter in Calender 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 first month of a Quarter, semester or Year etc.
  • Ancestor_Member_Expression: Any Multidimensional Expression that returns valid Ancestor Member. Cousin function will find the Cousin of First argument present in this Ancestor_Member.

In this article we will show you, How to write Cousin function in 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 Calender Year 2013

MDX CODE

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]

OUTPUT

MDX COUSIN FUNCTION 1

ANALYSIS:

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

[Measures].[Internet Sales Amount] ON ROWS

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 Calender year 2012.

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

Below line of code will check for the First Quarter in Calender year 2013( because Q1 CY 2012 is the First Quarter in Calender 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 Fourth Quarter of Calender Year 2013

MDX CODE

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]

OUTPUT
MDX COUSIN FUNCTION 3

ANALYSIS

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

[Measures].[Internet Sales Amount] ON ROWS

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 Calender year 2012.

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

Below line of code will check for the First Month of the Calender 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 Calender Quarter from the above lines of code with Calender 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 other using MDX Cousin Function.

MDX CODE

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]

OUTPUT
MDX COUSIN FUNCTION 4

ANALYSIS:

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

[Measures].[Internet Sales Amount] ON ROWS

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 Calender year 2012.

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

Below line of code will check for the First Month of the Calender 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.

Thank You for Visiting Our Blog

Placed Under: MDX