时间不在轴上时出现ParallelPeriod问题

2024-05-19 • 问答

此查询中的计算所得成员[金额LM]（=上个月的金额）有什么问题？如果我在行上指定时间，这似乎可以工作，但是即使我在where子句中有一个日期，也可以指定失败的时间。

WITH

MEMber [Amount LM] as
    (
        [Measures].[Amount],ParallelPeriod (
            [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month],1,[Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember
        )
    )


SELECT 
   {[Amount LM],[Measures].[Amount]} ON  COLUMNS,[Measure Types].[Name].&[22] --not related to [Measures].[Amount]; Used as a measure switch in the real case but adjusted here for simplicity
-- comment out the next line to fail
* [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month] 

ON rows


FROM [Cube]
WHERE [Date].[Year Number].&[2019]
;

得出工作案例：

                           Amount LM  |  Amount
    Costs  |    Jan 2019  | (null)    |  109600
    Costs  |    Feb 2019  | 109600    |  218300.5
    Costs  |    Mar 2019  | 218300.5  |  392250
    Costs  |    Apr 2019  | 392250    |  206800
    Costs  |    May 2019  | 206800    |  174700
    Costs  |    Jun 2019  | 174700    |  298400
    Costs  |    Jul 2019  | 298400    |  264550
    Costs  |    Aug 2019  | 264550    |  424100
    Costs  |    Sep 2019  | 424100    |  129650
    Costs  |    Oct 2019  | 129650    |  330050
    Costs  |    Nov 2019  | 330050    |  (null)
    Costs  |    Dec 2019  | (null)    |  (null)

没有

* [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month]

零件，LM量为空

          |Amount LM| Amount
    Costs | (null)  | 2548400.5

您的计算没有问题。要了解Null，您需要知道

ParallelPeriod的工作原理
UserHierarchy中的“ CurrentMember”如何工作

有空

“ [日期]。[年-季度-月-日期-层次结构]。[年和月]”

在行轴上

“ [日期]。[年-季度-月-日期-层次结构] .CurrentMember”

上面的“ .currentmember”表达式返回USER HIERARCHY的当前成员“ [年-季度-月-日期-层次结构]”。然后，PARALLEL PERIOD函数尝试针对级别“ [年和月]”查找表亲成员。

当您从行中删除用户层次结构时，currentmember返回2019，这是LEVEL“ YEAR Number”的成员，因此并行期间找不到与LEVEL“ [Year and Month]”相关的表亲（因为currentmember高于指定的LEVEL）。要检查，将月份中的年份替换为月份成员（行中没有日期），它将起作用。要解决此问题，您需要提供一个替代逻辑，以防万一通过了更高级别的成员，或者使用其他脚本在下面的脚本中返回任何情况下返回100。

WITH

MEMBER [Amount LM] as
case when [Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember.level.name <>"Year and Month" then 100
else 
(
    [Measures].[Amount],ParallelPeriod (
        [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month],1,[Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember
    )
)
end

以下是您在Adventure工程中遇到的问题的一个示例。我添加了两列来检查返回了什么ParallelPeriod和.currentmember

WITH
MEMBER [Amount LM] as
(
    [Measures].[Internet Sales Amount],ParallelPeriod ([Date].[Calendar].[Month],[Date].[Calendar].currentmember)
)

MEMBER [Amount LM_ParallelPeriodName] as
(
    ParallelPeriod ([Date].[Calendar].[Month],[Date].[Calendar].currentmember).name
)

MEMBER [Amount LM_CalendarCurrentmember] as
(
    [Date].[Calendar].currentmember.name
)

SELECT 
{[Amount LM],[Measures].[Internet Sales Amount],[Amount LM_ParallelPeriodName],[Amount LM_CalendarCurrentmember]} ON  COLUMNS,[Product].[Category].&[1] * [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

结果

现在让我们评论

“ [日期]。[日历]。[月]”

WITH
MEMBER [Amount LM] as
(
    [Measures].[Internet Sales Amount],[Product].[Category].&[1] --* [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]

结果注意ParallelPeriod返回的Null，现在让我们从Month的位置替换Year。这样可以使paralellperiod返回有用的内容

WITH
MEMBER [Amount LM] as
(
    [Measures].[Internet Sales Amount],[Product].[Category].&[1] * [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Month Name].[September 2013]

结果

时间不在轴上时出现ParallelPeriod问题

yhj861212 回答：时间不在轴上时出现ParallelPeriod问题

大家都在问