Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MDX - Skipping levels in the hierarchy

MDX - Skipping levels in the hierarchy

MDX - Skipping levels in the hierarchy

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.

RE: MDX - Skipping levels in the hierarchy

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.

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

RE: MDX - Skipping levels in the hierarchy

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).

RE: MDX - Skipping levels in the hierarchy

ParallelPeriods is the function you want to look at.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close