Post reply

Calculated members against several hierarchies within the same dimension

  • July 25, 2007 at 5:53 pm

    #125127

    I have a time dimension which has four hierarchies: Fiscal Month, Fiscal Week, Calendar Month and Calendar Week. Each hierarchy has three levels (year, week/month, day).

    I want to write a calculated member which calculates the rolling average over the last three periods. The periods should be whatever the level two is of the hierarchy.

    I don't want to define this member four times (once for each hierarchy). Given that my hierarchies have similar and compatible structures, can I make a calculated member which dynamically applies itself against each of my time hierarchies?

  • August 14, 2007 at 7:40 am

    #726771

    Yes and no.

    First, in considering the problem, ignore the fact that you want it to work against the hierarchies. Remember that hierarchies are a navigation through attributes.

    Now consider the attributes used and the order of importance in resolving the expression over these attributes. In general, it's bottom up (Weeks --> Months -- > Years ), but I'll leave the details to you.

    Create your expression with a Case statement:

    Case

      When [Time].[Week].CurrentMember.Level.Ordinal > 0

              Then "week expression"

      When [Time].[Month].CurrentMember.Level.Ordinal > 0

              Then "month expression"

      When [Time].[Year].CurrentMember.Level.Ordinal > 0

              Then "Year expression"

      Else

              "Unable to calculate expression"

    End

    What thuis does, is checks to see if any of the "items" are active for the attribute, to detect which level you're at in the hierarchy.

    Hope this helps.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply