locked
MDX to return last month of a quarter RRS feed

  • Question

  • I have an MDX query where I need to return the last month of a quarter going back 2 years or 8 quarters.  I want the results to be like:

    Q1 2012   March 2012    #####

    Q2 2012   June 2012      #####

    ....

    Q4 2013   Dec 2013     #####

    I"ve tried this:  TAIL(Descendants(Lastperiods(8,STRTOMEMBER(@DataDate)), [Product Post Date].[Calendar].[Month] ),1 )

    but it's only returning the last monthe for the last Quarter and I need the last month for the last 8 Quarters.

    What am I doing wrong here ?   Thanks.


    J. Way

    Thursday, January 9, 2014 11:19 PM

Answers

  • You can use the generate function for this:

    select {} on 0,
    generate(
        [Date].[Calendar].[Calendar Quarter].Members
    //   ,Tail(Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]), 1)
       ,[Date].[Calendar].CurrentMember.LastChild
       ,All
    )
    on 1
    from [Adventure Works]

    To only return the last 8 quarters from @DataDate (assuming this holds a date unique member name):

    select {} on 0,
    generate(
        { Ancestor( StrToMember('[Date].[Calendar].[Date].&[20090620]'), [Date].[Calendar].[Calendar Quarter] ).Lag(7) :
          Ancestor( StrToMember('[Date].[Calendar].[Date].&[20090620]'), [Date].[Calendar].[Calendar Quarter] )
        }
       ,[Date].[Calendar].CurrentMember.LastChild
       ,All
    )
    on 1
    from [Adventure Works]


    Christian Wade
    http://christianwade.wordpress.com/
    Please mark correct responses as answers!

    Friday, January 10, 2014 12:26 AM

All replies

  • You can use the generate function for this:

    select {} on 0,
    generate(
        [Date].[Calendar].[Calendar Quarter].Members
    //   ,Tail(Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]), 1)
       ,[Date].[Calendar].CurrentMember.LastChild
       ,All
    )
    on 1
    from [Adventure Works]

    To only return the last 8 quarters from @DataDate (assuming this holds a date unique member name):

    select {} on 0,
    generate(
        { Ancestor( StrToMember('[Date].[Calendar].[Date].&[20090620]'), [Date].[Calendar].[Calendar Quarter] ).Lag(7) :
          Ancestor( StrToMember('[Date].[Calendar].[Date].&[20090620]'), [Date].[Calendar].[Calendar Quarter] )
        }
       ,[Date].[Calendar].CurrentMember.LastChild
       ,All
    )
    on 1
    from [Adventure Works]


    Christian Wade
    http://christianwade.wordpress.com/
    Please mark correct responses as answers!

    Friday, January 10, 2014 12:26 AM
  • That is exactly what I needed, thanks so much Christian!   I'm a bit confused on the use of the generate function though.  Can you explain what it's doing ?

    J. Way

    Friday, January 10, 2014 6:26 PM
  • You're welcome Judy!

    I'll do my best.  I think the simplest way to conceptualize the generate function (in this case, not the string concatination case for which it can also be used) is ...

    • For each tuple/member in the first set (passed as a param), "apply" the 2nd set param to it.
    • Union all the results together, and thereby "generate" a new set.
    • What makes it useful is you can use CurrentMember to explicitly refer to members from the 1st set when doing this.

    Consider the following example ...

    As discussed in the MSDN definition, it is often used with TOPCOUNT.  If you need to show the top 10 products sold in each particular calendar year, generate a new set that for each year, derives the top 10 products, and then union all the results together into a newly generated set.

    Copy/paste from MSDN:

    ---------------------------------copy/paste start:

    The following example query displays the top 10 Products for each Calendar Year on Rows:
    SELECT 
    {[Measures].[Internet Sales Amount]}
    ON 0,
    GENERATE( 
    [Date].[Calendar Year].[Calendar Year].MEMBERS
    , TOPCOUNT(
    [Date].[Calendar Year].CURRENTMEMBER
    *
    [Product].[Product].[Product].MEMBERS
    ,10, [Measures].[Internet Sales Amount]))
    ON 1
    FROM [Adventure Works]

    Note that a different top 10 is displayed for each year, and that the use of Generate is the only way to get this result. Simply crossjoining Calendar Years and the set of top 10 Products will display the top 10 Products for all time, repeated for each year, as shown in the following example:

    SELECT 
    {[Measures].[Internet Sales Amount]}
    ON 0,
    [Date].[Calendar Year].[Calendar Year].MEMBERS
    * 
    TOPCOUNT(
    [Product].[Product].[Product].MEMBERS
    ,10, [Measures].[Internet Sales Amount])
    ON 1
    FROM [Adventure Works]

    ---------------------------------copy/paste end

    I hope that helps!


    Christian Wade
    http://christianwade.wordpress.com/
    Please mark correct responses as answers!

    Friday, January 10, 2014 7:20 PM