none
Equivalent Function in MDX for Month(X) as in SQL

    Question

  • Hi All,

       In SQL we write conditions Month(date)=09 to get the month field from date time. I want to know the equivalent function in MDX to get the month value as an integer. I need to create a calcuated member based on the above condition. Please let me know how to achieve this.

    Thanks and regards,
    Ram
    Wednesday, September 09, 2009 9:48 AM

Answers

  • Hi Ram

    I think the first statement for the AcutalDate will do the trick, please notice the MM for the month format.

    CREATE MEMBER CURRENTCUBE.[MEASURES].[ACTDATENOW]
     AS vba!format(now(),"MM/dd/yyyy"),
    VISIBLE = 1;

    create member currentcube.[measures].ADCTDATEYEAR as
        YEAR([MEASURES].[ACTDATENOW]);

    create member currentcube.[measures].ADCTDATEMONTH as
        MONTH([MEASURES].[ACTDATENOW]);

    create member currentcube.[measures].ADCTDATEDAY as
        DAY([MEASURES].[ACTDATENOW]);

    Hope this helps

    Tom
    • Marked as answer by Ramakrishnan.lh Wednesday, September 09, 2009 11:29 AM
    Wednesday, September 09, 2009 11:12 AM

All replies

  • take alook at this

    -- The First Calculated member is the value of NOW()
    WITH  MEMBER [Measures].[Full Date] as 'NOW()'
    -- The Second Calculated Member is the Day part of the first calculated member.
    MEMBER [Measures].[What Day] as 'DAY([Full Date])'
    -- The Third Calculated Member is the Month part of the first calculated member.
    MEMBER [Measures].[What Month] as 'MONTH([Full Date])'
    -- The Fourth Calculated Member is the Year part of the first calculated member.
    Member [Measures].[What Year] as 'YEAR([Full Date])'
    SELECT
       {[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS
    FROM Sales
    Praxy
    Wednesday, September 09, 2009 9:55 AM
  • Hi Praxy,

       Thanks a lot for the reply. When i execute the above query it gives me wrong results

    Full Date                                 What Day What Month What Year
    9/9/2009 4:21:25 PM                    30                 12            1899

    Please let me know where am i going wrong.

    Regards,
    Ram
    Wednesday, September 09, 2009 10:53 AM
  • Hi Ram

    I think the first statement for the AcutalDate will do the trick, please notice the MM for the month format.

    CREATE MEMBER CURRENTCUBE.[MEASURES].[ACTDATENOW]
     AS vba!format(now(),"MM/dd/yyyy"),
    VISIBLE = 1;

    create member currentcube.[measures].ADCTDATEYEAR as
        YEAR([MEASURES].[ACTDATENOW]);

    create member currentcube.[measures].ADCTDATEMONTH as
        MONTH([MEASURES].[ACTDATENOW]);

    create member currentcube.[measures].ADCTDATEDAY as
        DAY([MEASURES].[ACTDATENOW]);

    Hope this helps

    Tom
    • Marked as answer by Ramakrishnan.lh Wednesday, September 09, 2009 11:29 AM
    Wednesday, September 09, 2009 11:12 AM
  • How Can i get quarters or semesters (half years) in your example?
    Monday, May 31, 2010 10:14 PM