SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
robinrai3
robinrai3
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

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 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 ;

Date Sales Amount RollingAvgLast5Days

01 Jul 2001 14477.3382 14477.3382
02 Jul 2001 13931.52 14204.4291
03 Jul 2001 15012.1782 14473.6788
04 Jul 2001 7156.54 12644.3941
05 Jul 2001 15012.1782 13117.9509
06 Jul 2001 14313.08 13317.1391
07 Jul 2001 7855.6382 12213.5224
08 Jul 2001 7855.6382 11200.8754
09 Jul 2001 20909.78 12183.8091
10 Jul 2001 10556.53 12750.4741
11 Jul 2001 14313.08 12633.9577
12 Jul 2001 14134.8 12604.2444
13 Jul 2001 7156.54 12487.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
Argrithmag Neocram
Argrithmag Neocram
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

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
Mark Fitzgerald-331224
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

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 ROWS
FROM [Sales Cube]


Fitz
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search