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

 Author Message robinrai3 SSC-Enthusiastic Group: General Forum Members Points: 186 Visits: 365 hi All in mdx land,I have two queries which would do the same thing...doing a rolling 5 day average at the day levelI 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 1FROM Saleswhich gives results ;Date Sales Amount RollingAvgLast5Days01 Jul 2001 14477.3382 14477.338202 Jul 2001 13931.52 14204.429103 Jul 2001 15012.1782 14473.678804 Jul 2001 7156.54 12644.394105 Jul 2001 15012.1782 13117.950906 Jul 2001 14313.08 13317.139107 Jul 2001 7855.6382 12213.522408 Jul 2001 7855.6382 11200.875409 Jul 2001 20909.78 12183.809110 Jul 2001 10556.53 12750.474111 Jul 2001 14313.08 12633.957712 Jul 2001 14134.8 12604.244413 Jul 2001 7156.54 12487.7280i've done the same again but using the parallel period to get the previous 5th dayand then used this member in the average functionWITH 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 1FROM Salesthis is where it errors with the msg :a calculated member cannot be used as an operand of a range operatoras 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 around2) is there any other way to define a range using a calculated member so the avg function can take it Argrithmag Neocram Forum Newbie Group: General Forum Members Points: 2 Visits: 11 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") + "]")}; Mark Fitzgerald-331224 Hall of Fame Group: General Forum Members Points: 3881 Visits: 1538 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 ROWSFROM [Sales Cube]`Fitz