a calculated member cannot be used as an operand of a range operator

  • hi All in mdx land,

    I have two queries which would do the same thing...doing a rolling 5 day average at the day level

    I have a 3 level calendar hierarchy i.e [year] , [year month], [year month day]

    the first uses the lag method which works

    WITH MEMBER [RollingAvgLast5Days] AS avg( [Order Date].[CalendarHierarchy].CurrentMember.lag(5) : [Order Date].[CalendarHierarchy].CurrentMember, [Measures].[Sales Amount])

    SELECT {[Measures].[Sales Amount], [RollingAvgLast5Days]} ON 0,

    [Order Date].[CalendarHierarchy].[Year Month Day Desc] on 1

    FROM Sales

    which gives results ;

    DateSales AmountRollingAvgLast5Days

    01 Jul 200114477.338214477.3382

    02 Jul 200113931.52 14204.4291

    03 Jul 200115012.178214473.6788

    04 Jul 20017156.5412644.3941

    05 Jul 200115012.178213117.9509

    06 Jul 200114313.08 13317.1391

    07 Jul 20017855.638212213.5224

    08 Jul 20017855.638211200.8754

    09 Jul 200120909.78 12183.8091

    10 Jul 200110556.53 12750.4741

    11 Jul 200114313.08 12633.9577

    12 Jul 200114134.812604.2444

    13 Jul 20017156.5412487.7280

    i've done the same again but using the parallel period to get the previous 5th day

    and then used this member in the average function

    WITH MEMBER [Previous5Days] AS ParallelPeriod([Order Date].[CalendarHierarchy].[Year Month Day Desc], 5 , [Order Date].[CalendarHierarchy].[Year Month Day Desc].CurrentMember)

    MEMBER [RollingAvgLast5Days] AS avg( [Previous5Days] : [Order Date].[CalendarHierarchy].CurrentMember, [Measures].[Sales Amount])

    SELECT {[Measures].[Sales Amount], [RollingAvgLast5Days]} ON 0,

    [Order Date].[CalendarHierarchy].[Year Month Day Desc] on 1

    FROM Sales

    this is where it errors with the msg :

    a calculated member cannot be used as an operand of a range operator

    as a sanity check if i replace it with a literal date it does work however...

    my question is

    1) is this a shortcoming in when defining a range in mdx or is there a work around

    2) is there any other way to define a range using a calculated member so the avg function can take it

  • bump.... anyone with a solution to this? i have a similar problem, in that I'm trying to generate the date range using stringtomember, so I don't have to have hard coded dates:

    create member currentcube.[rollingYear] as Tail(StrToMember("[Date].[Date Hierarchy].[Month].[" + Format(Measures.maxInternalDate, "MMMM yyyy") + "]"), 12);

    Then i use the rollingYear:thisYear (another calculated member) but get the operator error.

    If I create the rollingYear with the range in it i get a member error when a tuple was expected when I use the rollingYear:

    create member currentcube.[rollingYear] as {Tail(StrToMember("[Date].[Date Hierarchy].[Month].[" + Format(Measures.maxInternalDate, "MMMM yyyy") + "]"), 12):StrToMember("[Date].[Date Hierarchy].[Month].[" + Format(Measures.maxInternalDate, "MMMM yyyy") + "]")};

  • You will need to combine the two calculations : (parellelperiod and current member to make the set within the AVG function).

    WITH MEMBER [Measures].[Last 6 Members Average] AS

    AVG(

    (

    {

    PARALLELPERIOD([Order Date].[Date].[Date], 5, [Order Date].[Date].currentmember) :

    [Order Date].[Date].currentmember

    },

    [Measures].[Internet Order Quantity]

    )

    )

    SELECT

    { [Measures].[Internet Order Quantity], [Measures].[Last 6 Members Average] } ON COLUMNS,

    [Order Date].[Date].[Date].[ALL].children ON ROWS

    FROM [Sales Cube]

    Fitz

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply