locked
Missing logic for week and months in MDX RRS feed

  • Question

  • Hi friends,

    I have written a query for weeks at  the same I need for  months as well in the same query... as shown in the screen shot

    missing logic  to write for months ... no idea how to mention iif function or case function.I need result which is shown in the screen shot

     

     

     

    screen shot 

    WITH 

      MEMBER [Measures].[Sales sum] AS 

        Sum

        (

          [Item].[Stock Category Group].[Stock Category Group].MEMBERS

         ,[Measures].[Net Amount - Sales]

        ) 

      MEMBER [Measures].[Sales Mix] AS 

        [Measures].[Net Amount - Sales] / [Measures].[Sales sum] 

       ,format_string = "Percent"

      MEMBER [Measures].[Sal this week] AS 

        (

          [Time].[Calendar Hierarchy].CurrentMember

         ,[Measures].[Net Amount - Sales]

        ) 

      MEMBER [Measures].[sal this week last year] AS 

        (

          [Time].[Calendar Hierarchy].CurrentMember.Lag(52)

         ,[Measures].[Net Amount - Sales]

        ) 

      MEMBER [Measures].[Sal var On Yr] AS 

     

          ([Measures].[Sal this week] - [Measures].[sal this week last year])

        / 

          [Measures].[sal this week last year] 

       ,format_string = "percent"

      MEMBER [Measures].[Sal margin this week] AS 

        (

          [Time].[Calendar Hierarchy].CurrentMember

         ,[Measures].[Margin % - Sales]

        ) 

      MEMBER [Measures].[sal margin this week last year] AS 

        (

          [Time].[Calendar Hierarchy].CurrentMember.Lag(52)

         ,[Measures].[Margin % - Sales]

        ) 

      MEMBER [Measures].[Sal Margin % var on yr] AS 

          [Measures].[Sal margin this week]

        - 

          [Measures].[sal margin this week last year] 

    SELECT 

      ( 

        [Time].[Calendar Hierarchy].[Calendar week].&[2010w40]

       ,{

          [Measures].[Net Amount - Sales]

         ,[Measures].[Sales Mix]

         ,[Measures].[Sal var On Yr]

         ,[Measures].[Margin % - Sales]

         ,[Measures].[Sal Margin % var on yr]

        }

      ) ON 0

     ,{

          [Item].[Stock Category Group].MEMBERS

        - 

          [Item].[Stock Category Group].&[]

      } ON 1

    FROM [Retail];

     

     

    can anyone help me ... I was dying to write logic for months as well....Thank you

     

     

     

     

    Monday, October 24, 2011 9:04 PM

Answers

  • Hi,

    Is declaring all the calculated members on top of your query and then combining them in your Select statement as shown below doesn't work for you?

    SELECT 
      {
        (
          [Date].[Calendar].[Month].&[2001]&[7]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
       ,(
          [Date].[Calendar].[Calendar Quarter].&[2001]&[3]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
      } ON 0
    FROM [Adventure Works];

    It should be a simple process, probably you can start with few calculated members and once logic works include all of them.

    Regards,
    Santosh


    It feels good if you give us some points for helpful post. :)
    Tuesday, October 25, 2011 8:05 AM

All replies

  • Hi,

    Getting Month level data wouldn't be very difficult. I am not sure how is the strucuture of your Cube/Dimension, so I'd take an example from Adventure Works, you can then amend the sample code as per your requirements -

    Following is the expression for Oct, 2011 when I drag it in the SSMS -

    [Date].[Calendar].[Month].&[2011]&[10]

    To make it dynamic you can use below expression -

    StrToMember("[Date].[Calendar].[Month].&[" + Cstr(Year(Now())) +"]&[" + Cstr(Month(Now())) +"]")

    So in your code you can use it like -

    MEMBER [Measures].[Sal this Month] AS
    (
      StrToMember("[Date].[Calendar].[Month].&[" + Cstr(Year(Now())) +"]&[" + Cstr(Month(Now())) +"]")
      ,[Measures].[Net Amount - Sales]
    )

    Likewise you can get same month last year as -

    "[Date].[Calendar].[Month].&[" + Cstr(Year(Now())-1) +"]&[" + Cstr(Month(Now())) +"]"

    Please let us know if you need more inputs on this.

    HTH.

    Regards,
    Santosh


    It feels good if you give us some points for helpful post. :)
    Tuesday, October 25, 2011 1:16 AM
  • Most of your existing MDX will already work, as referencing the current member in the calendar hierarchy will be a month if the user is at the month level in the hierarchy. The only difference is when you're getting the same month from the previous year, you'll need to use 12 periods instead of 52. For example, the sal this week last year would change to...

    MEMBER [Measures].[sal this month last year] AS 
    	(
    		[Time].[Calendar Hierarchy].CurrentMember.Lag(12)
    	,	[Measures].[Net Amount - Sales]
        ) 
    

    However, you're far better off writing generic time calculations (WTD, MTD, YTD, Previous Period, etc...) in the cube, so they can be reused in all your reports. Have a read of http://www.sqlmag.com/article/sql-server-analysis-services/optimizing-time-based-calculations-in-ssas- which should get you started.


    David Stewart | My Microsoft BI Blog | @dstewartbi
    Tuesday, October 25, 2011 1:17 AM
  • Yes Indeed, But I need to month and week in the same Query     like this

                             last week                                                          Month to date

                 sal      sal mix    sal var  sal margin                sal     sal mix   sal var  sal margin

    All        

    Bracelets

    Charm

    ....

    .....

     

    I would like to display the data set like above.. so I need both week and month in the same Query   like   if week exist run first part  if month exist run second part like this I'm expecting  but I don't know how to write that. Could you please help me

    Tuesday, October 25, 2011 7:04 AM
  • Thank you for your reply Santosh....But I'm expecting data set like by using single query which should have week and month

    Yes Indeed, But I need to month and week in the same Query     like this

                             last week                                                          Month to date

                 sal      sal mix    sal var  sal margin                sal     sal mix   sal var  sal margin

    All        

    Bracelets

    Charm

    ....

    .....

     

     so I need both week and month in the same Query   like   if week exist run first part  if month exist run second part like this I'm expecting  but I don't know how to write that. Could you please help me

    Tuesday, October 25, 2011 7:07 AM
  • Hi,

    Here I am just giving you an idea, how you can achieve it in your case. I have taken 2 different measures and showing them in Month and Quarter levels.

    SELECT 
      {
        (
          [Date].[Calendar].[Month].&[2001]&[7]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
       ,(
          [Date].[Calendar].[Calendar Quarter].&[2001]&[3]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
      } ON 0
    FROM [Adventure Works];

    HTH.

    Regards,
    Santosh

     


    It feels good if you give us some points for helpful post. :)

    Tuesday, October 25, 2011 7:37 AM
  • Thank you, I have a query for week and month individually but I need to merge both of them to create reports at the same I need to pass parameters for week and month....

    for weeks : 

    WITH

     

    MEMBER [Measures].[Sales sum] AS

     

    Sum
     

    (

    [Item].[Stock Category Group].[Stock Category Group].

    MEMBERS
     

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[Sales Mix] AS

    [Measures].[Net Amount - Sales] / [Measures].[Sales sum]

    ,

    format_string = "Percent"

     

    MEMBER [Measures].[Sal this week] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember
     

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[sal this week last year] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember.Lag(52)

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[Sal var On Yr] AS

     

    (([Measures].[Sal this week] - [Measures].[sal this week last year])

    /

    [Measures].[sal this week last year])

    ,

    format_string = "percent"

     

    MEMBER [Measures].[Sal margin this week] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember
     

    ,[Measures].[Margin % - Sales]

    )

     

    MEMBER [Measures].[sal margin this week last year] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember.Lag(52)

    ,[Measures].[Margin % - Sales]

    )

     

    MEMBER [Measures].[Sal Margin % var on yr] AS

     

    ([Measures].[Sal margin this week] - [Measures].[sal margin this week last year])

     

    SELECT

    (

    ( [Time].[Calendar Hierarchy].[Calendar Week].&[2010-40]

    )

    ,{

    [Measures].[Net Amount - Sales]

    ,[Measures].[Sales Mix]

    ,[Measures].[Sal var On Yr]

    ,[Measures].[Margin % - Sales]

    ,[Measures].[Sal Margin % var on yr]

    }

    )

    ON 0

    ,{

    [Item].[Stock Category Group].

    MEMBERS
     

    -

    [Item].[Stock Category Group].&[]

    }

    ON 1
     

    FROM

    [Retail]

     

     

    for Months:

    WITH

     

    MEMBER [Measures].[Sales sum] AS

     

    Sum
     

    (

    [Item].[Stock Category Group].[Stock Category Group].

    MEMBERS
     

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[Sales Mix] AS

    [Measures].[Net Amount - Sales] / [Measures].[Sales sum]

    ,

    format_string = "Percent"

     

    MEMBER [Measures].[Sal this month] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember
     

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[sal this month last year] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember.Lag(12)

    ,[Measures].[Net Amount - Sales]

    )

     

    MEMBER [Measures].[Sal var On Yr] AS

     

    (([Measures].[Sal this month] - [Measures].[sal this month last year])

    /

    [Measures].[sal this month last year])

    ,

    format_string = "percent"

     

    MEMBER [Measures].[Sal margin this month] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember
     

    ,[Measures].[Margin % - Sales]

    )

     

    MEMBER [Measures].[sal margin this month last year] AS

    (

    [Time].[Calendar Hierarchy].

    CurrentMember.Lag(12)

    ,[Measures].[Margin % - Sales]

    )

     

    MEMBER [Measures].[Sal Margin % var on yr] AS

     

    ([Measures].[Sal margin this month] - [Measures].[sal margin this month last year])

     

    SELECT

    (([Time].[Calendar Hierarchy].[Calendar Month].&[2010-06]

     

    )

    ,{

    [Measures].[Net Amount - Sales]

    ,[Measures].[Sales Mix]

    ,[Measures].[Sal var On Yr]

    ,[Measures].[Margin % - Sales]

    ,[Measures].[Sal Margin % var on yr]

    }

    )

    ON 0

    ,{

    [Item].[Stock Category Group].

    MEMBERS
     

    -

    {[Item].[Stock Category Group].&[],[Item].[Stock Category Group].&[PACK]}

    }

    ON 1
     

    FROM

    [Retail]

    where

    {[Channel].[Name].&[LOL Central],

    [Channel].[Name].&[LOL Corporate],

    [Channel].[Name].&[LOL Intercompany]

    ,[Channel].[Name].&[LOL Retail]

    ,[Channel].[Name].&[LOL Web]

    ,[Channel].[Name].&[LOL Wholesale]}

    

    

    I would like to merge both of them at last need to show in the data set like this

     

    

                             last week                                                          Month to date

                 sal      sal mix    sal var  sal margin                sal     sal mix   sal var  sal margin

    All        

    Bracelets

    Charm

    ....

    .....

     

    

     

    In single Query ... I have individally but getting struggle to merge both of them

    

    any idea on this Santosh

     

     

     

     

    Tuesday, October 25, 2011 7:45 AM
  • Hi,

    Is declaring all the calculated members on top of your query and then combining them in your Select statement as shown below doesn't work for you?

    SELECT 
      {
        (
          [Date].[Calendar].[Month].&[2001]&[7]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
       ,(
          [Date].[Calendar].[Calendar Quarter].&[2001]&[3]
         ,{
            [Measures].[Internet Sales Amount]
           ,[Measures].[Internet Freight Cost]
          }
        )
      } ON 0
    FROM [Adventure Works];

    It should be a simple process, probably you can start with few calculated members and once logic works include all of them.

    Regards,
    Santosh


    It feels good if you give us some points for helpful post. :)
    Tuesday, October 25, 2011 8:05 AM