locked
Detect non existent date range in mdx RRS feed

  • Question

  • Hi All
    Looking at a weighted averages example on the internet we get:
     MEMBER [Measures].[SMA12] AS Avg(
      [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
      [Measures].[Internet Sales Amount]
     )
    But if there was no month 12 months ago, perhaps we would not want to return a value. Can anayone help with how to not carry out the calculation. Have tried the following but its not quite working.
    MEMBER [Measures].[SMA12] AS 
    IIF(isempty( [Ship Date].[Month Name].CurrentMember.Lag(11), [Measures].[Internet Sales Amount]), 0,
        Avg(
          [Ship Date].[Month Name].CurrentMember.Lag(11):[Ship Date].[Month Name],
          [Measures].[Internet Sales Amount]
         )
    )
    Thanks
    Monday, May 30, 2011 12:33 PM

Answers

  • Check if somewhat like this helps you;

    WITH 
      MEMBER [Measures].[x] AS 
        IIF
        (
            (
              [Date].[Calendar].CurrentMember.Lag(11)
             ,[Measures].[Reseller Sales Amount]
            )
          = 0
         ,Null
         ,'YourCalculation'
        ) 
    SELECT 
      [Measures].[x] ON 0
    FROM [Adventure Works]
    WHERE 
      [Date].[Calendar].[Month].&[2005]&[2];



    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by BIfool Monday, May 30, 2011 1:40 PM
    Monday, May 30, 2011 1:22 PM

All replies

  • Hi All

    This has solved the problem for me by creating a flag.

    Got the code from:

    http://shahamishm.blogspot.com/2008/12/exists-function-mdx-query.html

     

     

    with member [Measures].[test]

    as
    iif(isempty((exists ([Date].[Fiscal].currentmember,{[Date].[Fiscal].
    [Fiscal Year].&[2003]:[Date].[Fiscal].[Fiscal Year].&[2004]}).item(0),
    [Measures].[Internet Sales Amount])),"Outside","inside")

    select {[Measures].[Internet Sales Amount], [Measures].[test]} on 0 ,
    [Date].[Fiscal].[Fiscal Year] on 1
    from
    [Adventure Works]

     

    Unless anyone has a better idea?

    Thanks

    Bif 

    Monday, May 30, 2011 1:19 PM
  • Check if somewhat like this helps you;

    WITH 
      MEMBER [Measures].[x] AS 
        IIF
        (
            (
              [Date].[Calendar].CurrentMember.Lag(11)
             ,[Measures].[Reseller Sales Amount]
            )
          = 0
         ,Null
         ,'YourCalculation'
        ) 
    SELECT 
      [Measures].[x] ON 0
    FROM [Adventure Works]
    WHERE 
      [Date].[Calendar].[Month].&[2005]&[2];



    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by BIfool Monday, May 30, 2011 1:40 PM
    Monday, May 30, 2011 1:22 PM
  • Thanks again Aniruddha
    Monday, May 30, 2011 1:40 PM
  •  

    I would think, it is better if you do based on the count of set rather than the measure value=0. 

    A 0 is a valid value and what if you have the .Lag(11) member a 0 measure value. 

    With 
    Member AvgCarsOwned As	
    	iif({[Date].[Calendar].currentmember.lag(1)}.count=1,
    	AVG(
    	{[Date].[Calendar].currentmember.lag(1):[Date].[Calendar].currentmember},
    	 [Measures].[Internet Sales Amount]
    	),null)
    SELECT AvgCarsOwned on 0,[Date].[Calendar].[Month].&[2001]&[7] on 1
    from 
    [Adventure Works]
    Update: Just a thought. Did not see it being marked as answered. anyways may be useful

    vinu
    Monday, May 30, 2011 2:10 PM