Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

a calculated member cannot be used as an operand of a range operator Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 7:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:21 AM
Points: 69, Visits: 326

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







Post #1047277
Posted Monday, September 16, 2013 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 3,015, Visits: 1,250
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
Post #1496339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse