Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Skipping levels in a hierarchy

  1. #1
    Join Date
    Sep 2008

    Default Skipping levels in a 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?

    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.

  2. #2
    Join Date
    Jul 2007


    Just create a dimension without the month level
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  3. #3
    Join Date
    Sep 2008

    Default RE:

    The way the data is setup in the summary table means that the weeks are a leaf of the month, rather than year.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.