代码之家 › 专栏 › 技术社区 › Bob Gear

MDX:移动平均计算仅给出(空)结果

mdx ssas

Bob Gear · 技术社区 · 8 年前

我正在尝试在SSAS 2014中使用MDX计算移动平均值,但所有结果都是(空)。

阅读多个网络参考和博客帖子使我认为这应该有效,给出了3个月的移动平均值:

With
  MEMBER [Measures].[MA3] AS 
    Avg(
         [Date].[Calendar Years].[Month].CurrentMember.Lag(2) 
          : [Date].[Calendar Years].[Month],
         [Measures].[Project Views]
       )

SELECT  { [Measures].[Project Views], [Measures].[MA3] } ON 0,
          [Date].[Calendar Years].[Month]  ON 1
FROM [ProjectAccesses]

但是,每列中都会出现(null)。

  MEMBER  [Measures].[C3] AS 
    Count(
          [Date].[Calendar Years].[Month].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].[Month],
          INCLUDEEMPTY
         )

但再次声明,没有检测到语法错误,但所有值都为(null),这让我相信月份范围集可能是空的。

我已经绕了几个小时了,已经没有什么可以尝试的了。

谁能提出可能出了什么问题吗;我是MDX的新手,所以我的查询可能结构错误,或者在我的多维数据集配置中有一些错误,但我无法找到。

谢谢

3 回复 | 直到 8 年前

ebayindir 8 年前

我相信你需要改变

": [Date].[Calendar Years].[Month],"

到

": [Date].[Calendar Years].currentmember,"

[日期]。[日历年]。[月]->应该给出级别,而不是您想要的当前成员

whytheq 8 年前

WITH
  MEMBER [Measures].[MA3] AS 
      AVG(
           [Date].[Calendar Years].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].CurrentMember,
           [Measures].[Project Views]
         )
  MEMBER  [Measures].[C3] AS 
    COUNT(
           [Date].[Calendar Years].CurrentMember.Lag(2) 
             : [Date].[Calendar Years].CurrentMember,
           INCLUDEEMPTY
         )
  MEMBER  [Measures].[Diagn1] AS  
    [Date].[Calendar Years].CurrentMember.member_caption
  MEMBER  [Measures].[Diagn2] AS  
    [Date].[Calendar Years].CurrentMember.Lag(2).member_caption
SELECT  
    { 
      [Measures].[Project Views]
    , [Measures].[MA3] 
    , [Measures].[C3] 
    , [Measures].[Diagn1]
    , [Measures].[Diagn2]
    } ON 0,
    [Date].[Calendar Years].[Month].MEMBERS  ON 1
FROM [ProjectAccesses];

Bob Gear 8 年前

我找到了解决办法。我以为我需要根据我的[日历年]层次结构中的一个成员来组成我的集合。然而,以下没有层次结构,工作正常(尽管我无法解释为什么原始版本没有)。

With
  MEMBER [Measures].[MA3] AS 
      Avg(
           [Date].[Month].CurrentMember.Lag(2) 
             : [Date].[Month].CurrentMember,
           [Measures].[Project Views]
         )
  MEMBER  [Measures].[C3] AS 
    Count(
           [Date].[Month].CurrentMember.Lag(2) 
             : [Date].[Month].CurrentMember,
           INCLUDEEMPTY
         )

SELECT  { [Measures].[Project Views], [Measures].[MA3] , [Measures].[C3] } ON 0,
          [Date].[Calendar Years].[Month]  ON 1
FROM [ProjectAccesses]