June 2, 2014 at 6:49 am
I am trying to get the data fromt the present calender week to the previous two weeks. If I specify the date range as parameters in the MDX query, the table gets filled up properly.
NON EMPTY { ([Project].[Projekt-Task].[Task].ALLMEMBERS* { [Date].[Date - CW].[Week].&[2014]&[201420] : [Date].[Date - CW].[Week].&[2014]&[201422]})} ON ROWS FROM [DWH]
But if I try and use the lag function, I get an error. Here is the MDX query.
NON EMPTY { ([Project].[Projekt-Task].[Task].ALLMEMBERS* [Date].[DATECW - CW].[Week].CurrentMember.Lag(2) ) } ON ROWS
FROM [DWH]
June 2, 2014 at 7:35 am
You're getting an error because you are using a member rather than a set in your crossjoin.
Try this:
SELECT NON EMPTY {[Measures].[planned_cumulative],[Measures].[Last Forecast CW] } ON COLUMNS,
NON EMPTY { ([Project].[Projekt-Task].[Task].ALLMEMBERS*{[Date].[DATECW - CW].[Week].CurrentMember.Lag(2) : [Date].[DATECW - CW].[Week].CurrentMember}) } ON ROWS
FROM [DWH]
June 2, 2014 at 8:01 am
Doesn't work.
I am getting an error message saying "The currentmember function expects a hierarchy expression for the 1-argument. A member expression was used"
June 2, 2014 at 8:05 am
Sorry, this'll do it:
SELECT NON EMPTY {[Measures].[planned_cumulative],[Measures].[Last Forecast CW] } ON COLUMNS,
NON EMPTY { ([Project].[Projekt-Task].[Task].ALLMEMBERS*{[Date].[DATECW - CW].CurrentMember.Lag(2) : [Date].[DATECW - CW].CurrentMember}) } ON ROWS
FROM [DWH]
June 2, 2014 at 8:26 am
Thank you. That did eliminate the error.
June 4, 2014 at 3:18 am
Although the query executes successfully, the year and the week columns are not being displayed. They do come up when i specify the week range manually like in the first query.
June 4, 2014 at 4:21 am
If you always want the current calendar week plus the two preceding it then you can construct the current week member by using a mixture of MDX and VBA. Then you can just lag it by 2. The following example uses AdventureWorks:
{
STRTOMEMBER(
"[Date].[Calendar Weeks].[Calendar Week].&["+CStr(Datepart("ww", Now()))+"&["
+CStr(Year(Now()))+"]"
)
:
STRTOMEMBER(
"[Date].[Calendar Weeks].[Calendar Week].&["+CStr(Datepart("ww", Now()))+"&["
+CStr(Year(Now()))+"]"
).Lag(2)
}
June 4, 2014 at 6:40 am
Oh ok. I totally understood what you told. Although i have a question, with regards to the way week hierarchy in my datawarehouse how would you apply the date part function.
I followed your method and tried this
"[Date].[Date - CW].[Week].&["+CStr(year(Now()))+"&["
+CStr(Datepart("ww", Now()))+"]")
.Lag(2)
: STRTOMEMBER(
"[Date].[Date - CW].[Week].&["+CStr(year(Now()))+"&["
+CStr(Datepart("ww", Now()))+"]"
)}
The query executes successfully without returning any rows.
This is the way the hiearchy is saved in the DW. "[Date].[Date - CW].[Week].&[2014]&[201420]"
I am sure its pretty simple and there's minor mistake which I might be doing, Alas I am a bit new to MDX.
June 4, 2014 at 7:56 am
Worked like a charm!! Thanks a lot for your assistance 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply