在 DATE 维度上使用 ANCESTOR 函数 [英] Using the ANCESTOR function on a DATE dimension

问题描述

这是我的脚本:

WITH MEMBER [Date].[Date - Calendar Month].[2MthPrev] AS
    (
    ANCESTOR(
        CLOSINGPERIOD([Date].[Date - Calendar Month].[Calendar Month]),
        2
    ))
SELECT  
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[2MthPrev]
    }
    ON ROWS,
    NON EMPTY
        [Measures].[Revenue]
    ON COLUMNS
FROM [OurCube] 

查询运行没有错误，但结果窗格为空.

The query runs with no error but the result pane is empty.

我试图在 [Date - Calendar Month] 层次结构中创建一个自定义成员，该层次结构比层次结构级别 [日历月] 上个月的前两个月代码>.因此，如果多维数据集中最后加载的月份是 2013 年 7 月，那么我希望 [2MthPrev] 会显示 2013 年 5 月的结果.

I've attempted to create a custom member in the [Date - Calendar Month] hierarchy that is two months previous to the last month in the hierarchy's level [Calendar Month]. So if the last loaded month in the cube is July 2013 then I'd hope that [2MthPrev] would show the results from May 2013.

我认为问题出在自定义成员 [2MthPrev] 及其对 ANCESTOR 的使用 - 这是如何解决的?

I believe the problem is with the custom member [2MthPrev] and its use of ANCESTOR - how is this fixed?

推荐答案

此查询返回给定度量值组的上次填充日期之前的 2 个月.您可能需要摆弄它以制作计算成员.tail 中的第二个参数是可选的.如果不包含，则默认值为 1.

This query returns 2 months prior from the last populated date for the given measure group. You may have to fiddle with it to make a calculated member. The second argument in tail is optional. If you don't include it, the default value is 1.

所以我要返回集合的第一个项目 (Item(0)) 之前(滞后)2 的项目，该集合包括月份集合中的最后一个月(尾部)，其中有值的月份度量组(存在子句).

So I'm returning the item that is 2 prior to (lag) the first item (Item(0)) of the set which includes the last month (tail) from the set of months for which there are values in the Measure Group (exists clause).

select {Tail(Exists([Date].[Date - Calendar Month].[Calendar Month].members, , "Measure Group Name")).Item(0).lag(2)} on 0
 from [OurCube]

这篇关于在 DATE 维度上使用 ANCESTOR 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！