MDX - Skipping levels in the hierarchy

[pilcher85]

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, just the year and week no?

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.

 

[MDXer]

you can't skip the week level if you want YTD through to last week. If you go straight to year it will give you YTD for your current year regardless of day or week. You need to go CurrentMember.Parent.PrevMember which will give you Last week. Once you have last week you can calculate YTD using the YTD() function.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[pilcher85]

Paul - Thanks for the quick response.

I made a mistake in my message. It was meant to be TW,LY - this week last year.

The issue is skipping over the month aspect (ie not having to reference month or sum at the month level since we only care about the week level).

 

[MDXer]

ParallelPeriods is the function you want to look at.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck