View Full Version : Skipping levels in a hierarchy

09-29-2008, 06:16 AM
I am trying to create code that will get the total sales for this year last
week. However, the nature of the data means that the time (YMW) hiearchy is Year.Month.Week.

Therefore I was wondering if there is a way of creating an MDX query that will skip the middle level (month), since we don't care what month the week falls in?

If it's not possible I have used a month lookup column but adding
this in the same format as the year (Comparison_YearMin) and week
(Prev_Yr_Comp_WeekMin) columns fails to validate in SAS Information Map.

The measure uses a table which has lookup columns for the previous year and week, since 2008 week 3 would not be 2007 week 3. At the moment (when testing in SAS Information Map) the query crashes at the week level.

This is the code that works up to the month level but crashes at the week level.

iif([YMW].currentmember.level is [YMW].[weeks],
SUM( StrtoMember("[YMW].[All YMW].["+
trim(left(put([Measures].[Comparison_YearMin],"10."))) +"].["+
trim(left(put([Measures].[Prev_Yr_Comp_WeekMin],"10."))) +"]"),
Measures.[SALES_EXC_VATSUM]), null)

Any help would be much appreciated.

09-29-2008, 09:01 AM
Just create a dimension without the month level

09-29-2008, 09:31 AM
The way the data is setup in the summary table means that the weeks are a leaf of the month, rather than year.