Sunday, March 13, 2011

How to calculate YTD, QTD, MTD and WTD?


YTD:

YTD function returns a set of sibling members from the Year level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Ytd([Member_Expression ])

Example:

Let's discuss YTD function by using a sample query. If you execute following MDX query,it will return a set of sibling members from Year level starting with the first sibling member i.e. January 2003 and ending with the specified member i.e. September 2003.
WITH

  SET [Required Set of Months] AS
    YTD([Date].[Calendar].[Month].[September 2003])
SELECT
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
 ,[Required Set of Months] ON ROWS
FROM [Adventure Works];

Output:



Let's have a look at another example.If you execute following MDX query, then query will return the cumulative SUM of “[Reseller Sales Amount]” from Jan 03 to Dec 03.


WITH 
  
MEMBER [Measures].[Calculate YTD] AS 
    
Sum
    (
      
YTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate YTD]
  } ON COLUMNS
 ,
Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];

Output:


If you observe the value of a measure “Calculate YTD” for a month of February 2003 then that is the SUM of “Reseller Sales Amount” of “January 2003 and February 2003”.Similarly the value of measure for a month of “December 2003” is SUM of “Reseller Sales Amount” from “January 2003 to December 2003”.

QTD:

QTD function returns a set of sibling members from the Quarter level as a given member, starting with the first sibling member and ending with the specified member.

Syntax: Qtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Quarter level (i.e. from 3rd Quarter of year 2003 (Q3)) starting with first sibling member i.e. July 2003 and ending with the specified member i.e. September 2003

WITH 
  
SET [Required Set of Quarters] AS 
    
QTD ([Date].[Calendar].[Month].[September 2003]) 
SELECT 
  {[Measures].[Reseller Sales Amount]} ON COLUMNS
 ,[Required Set of Quarters] ON ROWS
FROM [Adventure Works];




Let's have a look at another example.If you execute the following MDX query,the query will return the SUM of “Reseller Sales Amount”, aggregated over each quarter i.e. if you observe value of a measure “Calculate QTD” for “March 2003”, you will find that it is a sum of “January 2003, February 2003 and March 2003” as these members belongs to first Quarter. Similarly if you observe a value for “June 2003”, you will find that it is a sum of “April 2003, May 2003 and June 2003” as these members belongs to second Quarter.


WITH 
  
MEMBER [Measures].[Calculate QTD] AS 
    
Sum
    (
      
QTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Calculate QTD]
  } ON COLUMNS

,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month]
   ,SELF
  ) ON ROWS
FROM [Adventure Works];


MTD:

MTD function returns a set of sibling members from the Month level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Mtd([Member_Expression ])

Example:

If you execute following MDX query,it will return a set of sibling members from Month level,starting with the first sibling member i.e. “September 1, 2003” and ending with the specified member i.e. “September 8, 2003”.

WITH 
  SET [Set Of Members] AS
    MTD([Date].[Calendar].[Date].[September 8, 2003])
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Set Of Members] ON ROWS
FROM [Adventure Works];








Let’s discuss one more example, If you execute following MDX query, you will get a value “$525,802.16” which is the SUM of “Internet Sales Amount” from “1st July 2003” to “19th July 2003”.

WITH 
  
MEMBER [Measures].[Calculate MTD] AS 
    
Sum
    (
      
MTD([Date].[Calendar].CurrentMember)
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate MTD]} ON COLUMNS
 ,[Date].[Calendar].[Date].[July 19, 2003] ON ROWS
FROM [Adventure Works];




WTD:

WTD function returns a set of sibling members from the Weeks level as a given member, starting with the first sibling and ending with the specified member.

Syntax: Wtd([Member_Expression ])

Example:

To understand WTD in more proper way, modify “Calendar Weeks” hierarchy. Add “Date” level to the “Calendar Weeks” hierarchy and execute following MDX query.



WITH 
  
SET [Output set of members using WTD] AS 
    
WTD([Date].[Calendar Weeks].[Date].[September 8, 2003]) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON COLUMNS
 ,[Output set of members using WTD] ON ROWS
FROM [Adventure Works];

If you execute above MDX query,it will return a set of sibling members from Week level,starting with the first sibling member i.e. “September 7, 2003” and ending with the specified member i.e. “September 8, 2003”.


Let's have a look at another example;If you execute the following MDX query,it will return  “$114,368.59” which is the SUM of “Internet Sales Amount” from “16th February 2003 to “21st February 2003”.


WITH 
  
MEMBER [Measures].[Calculate WTD] AS 
    
Sum
    (
      
WTD
      (
        [Date].[Calendar Weeks].
CurrentMember
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {[Measures].[Calculate WTD]} ON COLUMNS
 ,[Date].[Calendar Weeks].[Date].[February 21, 2003] ON ROWS
FROM [Adventure Works];



6 comments:

  1. Great job. Very simple to understand. Thank you for share this job with us.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. nice article (y)
    thank you for sharing this article

    ReplyDelete
  4. Nice article
    How to add this formula in the cube?
    Thank's

    ReplyDelete