MDX Cookbook 10 - 计算 Year To Date 的 Running Total(YTD 与 PeriodsToDate 的区别)

在这个小节中我们将计算度量值的 Year To Date 的值，也就是计算从年开始到当前时间成员为止的度量值的累加结果。

下面的这个查询显示了所有以周为单位的 Reseller Sales Amount -

那么如果要计算上图中以周为单位的累加值，应该如何处理？ 比如说现在是 Week 28 CY 2005，那么它的 YTD 累加值就是 Week 27 CY 2005 的度量值 + Week 28 CY 2005 度量值。

WITH
MEMBER [Measures].[Reseller Sales YTD]
AS
   SUM(
         YTD([Date].[Calendar Weeks].CurrentMember),
         [Measures].[Reseller Sales Amount]
       )
SELECT
{
   [Measures].[Reseller Sales Amount],
   [Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Calendar Weeks].[Calendar Week].MEMBERS} ON 1
FROM [Adventure Works]

这样就实现了这种累加值的计算，也叫 Running Total。

 

YTD() 函数返回是一个集合，是一个时间段的集合。它的起点是当前成员所在层次结构上的第一个时间成员，结束点就是这个当前成员自身。所以对于 Week 28 CY 2005 来说，它的 YTD 就是 Week 27 CY 2005 到 Week 28 CY 2005；对于 Week 35 CY 2005 来说，它的 YTD 还是从 Week 27 CY 2005 到它自身。

因此，如果看看 CY 2006 就会发现，YTD() 函数的确是以年作为一个时间参照开始的。并且要注意， CY 2005 的第一个 Week 成员就是 Week 27，但是在 CY 2006 的时候 Week 1 是在 Week 级别中的第一个成员。

还有另外一种比较简单的方式计算累加，但是要注意这里的累加不再基于年，而是从当前层次结构上的第一个成员开始到当前成员的这段时间。

WITH
MEMBER [Measures].[Reseller Sales YTD] AS
Sum( 
 (NULL: [Date].[Calendar Weeks].CurrentMember),
 [Measures].[Reseller Sales Amount]
)
SELECT {
     [Measures].[Reseller Sales Amount],
     [Measures].[Reseller Sales YTD]
   }ON 0,
{[Date].[Calendar Weeks].[Calendar Week].MEMBERS} ON 1
FROM [Adventure Works]

但是有要注意的地方，如果这里我们使用的是 Fiscal Week 而不是 Calendar 会出现什么问题？

WITH
MEMBER [Measures].[Reseller Sales YTD]
AS
SUM(
YTD([Date].[Fiscal Weeks].CurrentMember),
[Measures].[Reseller Sales Amount]
)
SELECT
{
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Fiscal Weeks].[Fiscal Week].MEMBERS} ON 1
FROM [Adventure Works]

可以看到的结果是 YTD 的计算全部都不正确。

错误信息：By default, a year level was expected. No such level was found.

 

原因是为什么呢？ [Date].[Calendar Weeks] 这个用户自定义的层次结构有一个级别是 Calendar Year，它的类型是 Years。相对于 [Date].[Calendar Weeks] 的 Fiscal 层次结构是 [Date].[Fiscal Weeks]，它有一个级别是 Fiscal Year，它的类型是 FiscalYear 而不是 Years。

YTD() 实际上是 PeriodsToDate() 函数的缩写，但是它只能适用于含有 Year 这个类型级别的属性层次结构。如果我们在开发过程中忘记这样设计的话，那么我们就只可以使用 PeriodsToDate() 这种原型函数来实现 Year To Date 了。

WITH
MEMBER [Measures].[Reseller Sales YTD]
AS
  SUM(
        PeriodsToDate(
                              [Date].[Fiscal Weeks].[Fiscal Year],
                              [Date].[Fiscal Weeks].CurrentMember
                            ),
                            [Measures].[Reseller Sales Amount]
        )
SELECT
{
    [Measures].[Reseller Sales Amount],
    [Measures].[Reseller Sales YTD]
} ON 0,
{[Date].[Fiscal Weeks].[Fiscal Week].MEMBERS} ON 1
FROM [Adventure Works]

查询结果一样是这样的 Running Total 结果-

 

关于 PeriodsToDate() 函数的使用，我的这篇 MDX 读书笔记中有详细的解释 - MDX Step by Step 读书笔记(九) - Working with Time 处理时间

更多 BI WORK 博客系列看参看 - BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)