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

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

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

那么如果要计算上图中以周为单位的累加值,应该如何处理? 比如说现在是 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。

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

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 级别中的第一个成员。

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

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

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]

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

但是有要注意的地方,如果这里我们使用的是 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 的计算全部都不正确。

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

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

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

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

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

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 结果-

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

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

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

05-11 22:32