January 13, 2011 at 7:38 am
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
September 16, 2013 at 9:57 am
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") + "]")};
September 19, 2013 at 5:55 am
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