locked
MDX on last 6 month RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I want to return list of most recent 6 month, so I need to find out what the current month is, then return list of 6 month back,for example, if current month is Nov, then I want to return Jun,Jul,Aug,Sept,Oct,Nov, so I try the following test using AdventureWorks:

    select

     

    {} on 0,

    ([Date].[Month of Year].

     

    currentmember.lag(5): [Date].[Month of Year].currentmember) on 1

    from

     

    [Adventure Works]

    But it returns All Periods, instead of last 6 month, anything I miss?

    Thanks

    Don

    Wednesday, November 11, 2009 5:25 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

    How to get the today's date in MDX 

    With
Set [CurrentMonth] as
{[Date].[Calendar].[Month].&[2003]&[11]}
select{} on 0, 
LastPeriods(6, [CurrentMonth].item(0)) on 1
from
[Adventure Works]

     

     

     

    - Deepak
    • Proposed as answer by Raymond-Lee Friday, November 13, 2009 6:11 AM
    • Marked as answer by Raymond-Lee Friday, November 20, 2009 9:18 AM
    Wednesday, November 11, 2009 7:12 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I want to create mdx expression like "Running sales amount by most recent 3 month", so if it is now November, then the running total by most recent 3 month would be:
              Amount
    Sept    21211
    Oct     12121
    Nov     12122

    What is the mdx function to do that? I know there is Lag function, but it seems Lag (3) of current member will give you Oct, Nov and Dec, which is not running 3 month starting from Nov.

    Don
    • Merged by Raymond-Lee Friday, November 20, 2009 9:17 AM the same topic
    Tuesday, November 10, 2009 6:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Try this...


    WITH

    SET [Last 3 Months] AS
           {Ancestor(Time.Time.CurrentMember,[Time].[Time].Month).Lag(2)
           :Ancestor(Time.Time.CurrentMember,[Time].[Time].Month)}


    SELECT
    {[Last 3 Months]} on columns

     

    from [Cube Name]

    Regards
     Gokul

    Tuesday, November 10, 2009 6:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Using  LastPeriods works perfectly in your case. Here is the complete MDX that I got it working in Adventure Works 2008 database.

    WITH
      MEMBER [Measures].[Last3Months] AS
        Sum
        (
          {
            LastPeriods
            (3
             ,[Date].[Fiscal].CurrentMember
            )
          }
         ,[Measures].[Amount]
        )
    SELECT
      {
        [Measures].[Last3Months]
       ,[Measures].[Amount]
      } ON COLUMNS
     ,Descendants
      (
        [Date].[Fiscal].[Fiscal Year].&[2004]
       ,[Date].[Fiscal].[Month]
       ,self
      ) ON ROWS
    FROM [Adventure Works];


    Thanks,
    Ashok
    Tuesday, November 10, 2009 10:45 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Note that .currentmember will not automatically return the current month, unless the default member has been somehow set to it. There's a sample Adventure Works query below which works - you can refer to this blog entry for discussion of how to set the current date:

    How to get the today's date in MDX 

    With
Set [CurrentMonth] as
{[Date].[Calendar].[Month].&[2003]&[11]}
select{} on 0, 
LastPeriods(6, [CurrentMonth].item(0)) on 1
from
[Adventure Works]

     

     

     

    - Deepak
    • Proposed as answer by Raymond-Lee Friday, November 13, 2009 6:11 AM
    • Marked as answer by Raymond-Lee Friday, November 20, 2009 9:18 AM
    Wednesday, November 11, 2009 7:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    Definetely you got your result set, but i prefer that right here this link more helpfull to others and also for those who are new with MDX.

    Please check the below link in which date filtering describes with different taste for reducing the updating time, and how beautifully describe about the AS2008 boclk system.

    http://sqlblog.com/blogs/mosha/archive/2008/10/22/optimizing-mdx-aggregation-functions.aspx

    Thanks
    M Ahsan

    Tuesday, April 2, 2013 5:34 AM