All Replies

• Wednesday, February 20, 2013 5:45 AM

   

   
  

  0

  try this please:

  With member [Hierarchy Name] as

  IIF([D Date].[Calendar].currentmember is [D Date].[Calendar].defaultmember,'Fiscal Calendar','Calendar')

  assuming that [D Date] is your date dimension that contains those only two hierarchies: [Calendar] and [Fiscal Calendar]
  

  
  

  • Edited by Butmah Wednesday, February 20, 2013 5:46 AM
  •  
  • Reply
  • Quote

   
• Wednesday, February 20, 2013 12:10 PM

   

   
  

  0

  no, because there is no such thing as a "current hierarchy". there is a current member in all hierarchies of all dimensions. for example if you have a month as the current selection, [fiscal].currentmember is the month, [calendar].currentmember is also the month. if the current selection is 2012 2nd semester, the [calendar].currentmember is 2012/s2, the [fiscal].currentmember is [All]. but beware, because if the current selection is 2012 3rd quarter, the [fiscal].currentmember will be something like 2012, the smallest unit that encapsulates the calendar quarter. there is no way to determine the original "reason" why the current selection looks like how it looks like. it is just what it is. again: all hiearchies have a current selection.

  it makes growth calculations rather tricky. you can not simply make a prev period growth that works with every time hierarchy.

  there are various ways to address this, you can catch the subcube in the where clause, you can examine the axes with the Axis() function, you can check if the current selection equals exactly to a certain aggregated member in a hierarchy, and so on. what is your actual goal?

  • Reply
  • Quote

   
• Wednesday, February 20, 2013 10:33 PM

   

   
  

  0

  Thanks for the reply Krisztin.

  My Actual goal is to have a calculated measure that returns a training twelve month calculation.

  Maybe something like this:

  SUM
  (
      {
          [Date].[Fiscal Calendar].currentmember.Lag(11)
          :[Date].[Fiscal Calendar].currentmember
      },[Measures].[Sales Order Amount]
  )

  I want the end user to be able to use either the fiscal calendar hierarchy or the standard calendar hierarchy with the same measure.  I was thinking if I knew which hierarchy they were using, I could use an IIF statement in the expression to calculate it using either  [Date].[Fiscal Calendar] or  [Date].[Calendar].

  
  

  ---

  John Schroeder

  • Reply
  • Quote

   
• Thursday, February 21, 2013 4:31 AM

   

   
  

  1

  Use the CurrentMember.Hierarchy.Name

  From BOL:

  The following example returns the name of the Calendar hierarchy in the Data dimension in the [Adventure Works] cube.
  
  WITH 
  
  MEMBER Measures.HierarchyName as 
  
  [Date].[Calendar].Currentmember.Hierarchy.Name
  
  SELECT {Measures.HierarchyName} ON 0,
  
  {[Date].[Calendar].[All Periods]} ON 1
  
  FROM [Adventure Works]
  

  
  Reference:

  http://msdn.microsoft.com/en-us/library/ms144781(v=sql.105).aspx

  • Proposed As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Friday, February 22, 2013 6:14 AM
  • Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Friday, March 01, 2013 1:47 AM
  •  
  • Reply
  • Quote

   
• Thursday, February 21, 2013 4:40 AM

   

   
  

  1

  With member [Hierarchy Name] as
       IIF([D Date].[Calendar].currentmember is [D Date].[Calendar].defaultmember,'Fiscal Calendar','Calendar')
  
       member [12 month calculation] as sum(strtoset('[D Date].['+[Hierarchy Name]+'].currentmember.lag(11):[D Date].['+[Hierarchy Name]+'].currentmember'),[Measures].[Sales Order Amount])

  ------------------

  Could be a workaround

  • Reply
  • Quote

   
• Thursday, February 21, 2013 8:38 AM

   

   
  

  1

  for this reason, the following solution is even better than getting the hierarchy. i assume you use the same attribute for the month level in both fiscal and calendar hierarchies. if not, you should.

  sum( lastperiods(12, tail(existing [Date].[Calendar].[Month].members, 1)), ... )

  in this expression, i use the calendar hierarchy, but it does not matter. in fact, i often use the attribute hierarchy even if it is invisible, i feel it is more elegant then using a level of a user hierarchy. remember, if we are at the month level in one hierarchy, we are in the month level in every hierarchy that contains the month attribute.

  how it works: let's get all the months that exist in the current selection. take the last one. then take the 12 month period ending at that month. it works for every level, year, quarter, etc. it also works with set-in-the-where selections, like

  where {[date].[calendar].[11-2012], [date].[calendar].[12-2012]}

  • Reply
  • Quote