一组元组上的MDX DateAdd函数 [英] MDX DateAdd function over a set of tuples

问题描述

我正在尝试使用DateAdd()函数创建一组日期，但是在尝试传递一组元组作为参数时遇到错误. 下面的代码返回一个成员，但我正在寻找一组新日期.

I am trying to create a set of dates using DateAdd() function but I am getting errors while trying to pass a set of tuples as parameter. The below code returns a member but I am looking for a set of new dates.

与 会员[EFF INJ DT] AS DATEADD("M"，12，[伤害日期] .CurrentMember)

WITH Member [EFF INJ DT] AS DATEADD("M",12, [INJURY DATE].CurrentMember)

SELECT {[EFF INJ DT]} ON COLUMNS, [INJURY DATE].[DATE].Members ON ROWS
FROM [WVWC DATA CUBE FROI SROI]

我有以下尝试:

 WITH 
Set [EFF INJ DT] AS 
DATEADD("M",12, [INJURY DATE].CurrentMember)

SELECT {[EFF INJ DT]} ON COLUMNS, [INJURY DATE].[DATE].Members ON ROWS
FROM [WVWC DATA CUBE FROI SROI]

推荐答案

如果您要向前移动12个成员，则可以使用Adv Works，然后可以通过多种方式进行导航.

Using Adv Works if you want to move 12 members forward then you can navigate in several ways.

尝试以下方法.它应返回currentmember之前12天的日期:

Try the following. It should return the date 12 days ahead of the currentmember:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
        [Date].[Calendar].CurrentMember.LEAD(12).MEMBERVALUE
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

好像您想返回将来的12个月日期.

Looks like you want to return the date which is 12 months in the future.

因此，首先我们需要通过ANCESTOR函数>来对应currentmember的年份，然后使用COUSIN函数向前移动到等效日期，如下所示:

So first we need the year that corresponds to the currentmember via the ANCESTOR function > then go forward to the equivalent date using the COUSIN function like the following:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
    COUSIN(
        [Date].[Calendar].CurrentMember, 
        ANCESTOR(
            [Date].[Calendar].CurrentMember, 
            [Date].[Calendar].[Calendar Year]).LEAD(1)
            ).membervalue   
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

适应您的情况并假设INJURY DATE是多级user hierarchy' that contains another level called日历年`，您可以执行以下操作:

Adapting to your scenario and assuming that INJURY DATE is a multi-level user hierarchy' that contains another level calledCalendar Year` you could do the following:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
        COUSIN(
            [INJURY DATE].CurrentMember, 
            ANCESTOR(
                [INJURY DATE].CurrentMember, 
                [INJURY DATE].[Calendar Year]).LEAD(1)
                ).MEMBERVALUE   
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[INJURY DATE].[DATE].Members} ON 1
FROM [Adventure Works];

---

编辑

通过VBA工具，我们在Adv Wrks中具有以下功能:

Via the VBA tools we have the following in Adv Wrks:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
      DATEADD(
      "M",
      12,
      [Date].[Calendar].CurrentMember.membervalue 
      )  
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[Date].[Calendar].[Month].&[2006]&[4].CHILDREN} ON 1
FROM [Adventure Works];

适应您的情况:

WITH 
    MEMBER [MEASURES].[EFF INJ DT] AS
    DATEADD(
    "M",
    12,
    [INJURY DATE].CurrentMember.membervalue 
    )  
SELECT 
    {[MEASURES].[EFF INJ DT]}  ON 0,
    {[INJURY DATE].[DATE].Members} ON 1
FROM [Adventure Works];

这篇关于一组元组上的MDX DateAdd函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！