Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 a calculated member cannot be used as an operand of a range operator Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 13, 2011 7:38 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, October 4, 2016 9:36 AM Points: 71, Visits: 346
 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
Post #1047277
 Posted Monday, September 16, 2013 9:57 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, October 10, 2013 12:47 PM 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") + "]")};
Post #1495167
 Posted Thursday, September 19, 2013 5:55 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 18, 2016 1:24 AM Points: 3,662, Visits: 1,510
 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
Post #1496339

 Permissions