我试图排除期间 P13(财政年度末的特殊时期)进行一段时间的相对计算,例如使用 ParallelPeriod 超过 12 个月的总和,或使用 Lag() 的平均值。这是到目前为止的MDX:
WITH MEMBER [Measures].[Sum Amount 12 Months]
AS
Sum(
ParallelPeriod( [Time].[Fiscal Calendar].[Fiscal Period],11):[Time].[Fiscal Calendar] ,
[Measures].[Amount]
)
我的层次结构中的句点如下:2014-P1,....,2014-P12,2014-P13,2015-P1,...
所以这个想法是首先模拟删除所有周期 -P13,然后聚合......但我真的需要帮助...
谢谢
您可以使用 EXCEPT
函数来摆脱P13
。
WITH SET P13PeriodMember
AS TAIL(EXISTS([Time].[Fiscal Calendar].[Fiscal Period].MEMBERS, [Time].[Fiscal Calendar].[Fiscal Year].CURRENTMEMBER), 1).ITEM(0).ITEM(0)
MEMBER [Measures].[Sum Amount 12 Months]
AS
SUM(
EXCEPT(
ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period].currentmember,11):[Time].[Fiscal Calendar].[Fiscal Period].currentmember,
P13PeriodMember),
[Measures].[Amount]
)
MDX现在的工作,Sourav_Agasti答案真的很有帮助,但是关于ParallelPeriod参数仍然存在问题。事实上,如果我们从集合中排除一个成员,我们仍然需要增加我们的参数以添加 1 个周期......
可能有一个更简单的解决方案,我会很高兴听到它!
WITH
SET P13Periods
AS
{ //Exclude P13 members (hardcode for test)
[Time].[Fiscal Calendar].[Fiscal Period].&[201013],
[Time].[Fiscal Calendar].[Fiscal Period].&[201113],
[Time].[Fiscal Calendar].[Fiscal Period].&[201213],
[Time].[Fiscal Calendar].[Fiscal Period].&[201313]
}
MEMBER [Measures].[Amount Sum 2 Months]
AS
SUM(
//Check if range Period contains a P13
//If yes, then we need to exclude this period from calculation AND add 1 Period to ParallelPeriod
IIF(
EXISTS(
P13Periods,
ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],1):[Time].[Fiscal Calendar]
).Count =1
,EXCEPT(
ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],2):[Time].[Fiscal Calendar]
,P13Periods
)
,ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],1):[Time].[Fiscal Calendar]
)
,
[Measures].[Amount]
)
SELECT {[Measures].[Amount],[Measures].[Amount Sum 2 Months]} on 0
,[Time].[Fiscal Calendar].[Fiscal Period] on 1
FROM [MyCube]