OLAP Cube Calculation for Last Year Comparison on MDX

Alexo 2020-01-31 22:26

我得到了解决方案：

此问题的多维数据集计算代码：

// ------------------------------------------------------------------------
//
//    Comparison Week Day - Date.Calendar
//
// ------------------------------------------------------------------------
SCOPE ( 
    [Date].[Year - Quarter - Month - Date].MEMBERS,
    [Date].[Date].MEMBERS );                    

    ///////////////////////////////////////////////////////////////////////////////////////
    ( [Date Calculations Week Day].[ComparisonWD].[Previous Year], 
      [Date Calculations Week Day].[AggregationWD].Members ) 
          = SUM({ParallelPeriod([Date].[Year - Quarter - Month - Date].[Date], 364, Descendants( [Date].[Year - Quarter - Month - Date].CurrentMember , [Date].[Year - Quarter - Month - Date].[Date]).item(0)) :
                     Parallelperiod ([Date].[Year - Quarter - Month - Date].[Date], 364,  Descendants( [Date].[Year - Quarter - Month - Date].CurrentMember , [Date].[Year - Quarter - Month - Date].[Date]).item((Descendants( [Date].[Year - Quarter - Month - Date].CurrentMember , [Date].[Year - Quarter - Month - Date].[Date])).Count - 1))}, [Date Calculations Week Day].[ComparisonWD].DefaultMember );            
END SCOPE;    


SCOPE ([Date Calculations Week Day].[ComparisonWD].[Diff. Over Previous Year]);                   
    THIS = IIF( IsEmpty( [Date Calculations Week Day].[ComparisonWD].DefaultMember ) 
                         OR IsEmpty( [Date Calculations Week Day].[ComparisonWD].[Previous Year] ),
                NULL,
                [Date Calculations Week Day].[ComparisonWD].DefaultMember 
                - [Date Calculations Week Day].[ComparisonWD].[Previous Year] );                   
    NON_EMPTY_BEHAVIOR(THIS) = [Date Calculations Week Day].[ComparisonWD].DefaultMember;                   
    FORE_COLOR(THIS) = IIF( [Date Calculations Week Day].[ComparisonWD].[Diff. Over Previous Year] < 0, 255, 0);                     // 255 = RED
END SCOPE;                   

SCOPE ([Date Calculations Week Day].[ComparisonWD].[Diff. % Over Previous Year]);                   
    THIS = IIF( IsEmpty( [Date Calculations Week Day].[ComparisonWD].[Diff. Over Previous Year] ) 
                         OR IsEmpty( [Date Calculations Week Day].[ComparisonWD].[Previous Year] ),
                NULL,
                [Date Calculations Week Day].[ComparisonWD].[Diff. Over Previous Year]
                / [Date Calculations Week Day].[ComparisonWD].[Previous Year] );                   
    NON_EMPTY_BEHAVIOR(THIS) = [Date Calculations Week Day].[ComparisonWD].DefaultMember;                   
    FORMAT_STRING(THIS) = 'Percent';                   
    FORE_COLOR(THIS) = IIF( [Date Calculations Week Day].[ComparisonWD].[Diff. % Over Previous Year] < 0, 255, 0);                     // 255 = RED
END SCOPE;                   

///////////////
// Tuple (All years, All Months) is the default number (keeps compatibility with OWC11)
( [Date].[Year].[All], [Date].[MonthYear].[All],
  Except( [Date Calculations Week Day].[ComparisonWD].[ComparisonWD].MEMBERS, [Date Calculations Week Day].[ComparisonWD].DefaultMember ) ) = [Date Calculations Week Day].[ComparisonWD].DefaultMember;

现在在每个级别上都有求和值：

使用此日期计算工作日维度，您可以在“工作日”逻辑中为每个度量显示上一年的值。

OLAP Cube Calculation for Last Year Comparison on - MDX

相关问题