locked
Multiple Time Hierarchy RRS feed

  • Question

  • Hi,

    I have a calculated member that would need to be applied to 2 distinct hierarchy - ie Fiscal and Calendar

    Below is a sample of how the calculation looks like. How can i create another calculation to show in Fiscal Quarter without the affecting the figures in Calendar Quarter?

    CREATE MEMBER CurrentCube.[Measures].[Days in Period] AS Count( Descendants( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Date] ) ), VISIBLE = False; SCOPE( [Date].[Calendar].[Calendar Quarter] ); this = SUM( { [Date].[Calendar].CurrentMember.Children} , [Measures].[Internet Sales Amount] * [Measures].[Days in Period] ) / [Measures].[Days in Period]; END SCOPE;

    Monday, May 21, 2012 7:53 AM

Answers

  • One thing you should do is make the measure [Days in Period] generic so it is not tied to the Calendar Date hierarchy.

    Define it as

    CREATE MEMBER CurrentCube.[Measures].[DaysinPeriod]AS Count(EXISTING [Date].[Date].[Date]),VISIBLE =False;


    Then you can SCOPE it on the quarter level to look at months (since months are pointing to the same attribute in the dimension)

    SCOPE([Date].[Calendar].[CalendarQuarter], [Date].[Fiscal].[FiscalQuarter]);([Measures].[Internet Sales Amount])=SUM(EXISTING [Date].[Month].[Month],[Measures].[InternetSalesAmount]*[Measures].[DaysinPeriod])/[Measures].[DaysinPeriod];ENDSCOPE;

    hth


    -Remember to mark as helpful/the answer if you agree with the post.

    • Proposed as answer by Lola Wang Wednesday, May 23, 2012 3:02 AM
    • Marked as answer by Ivan WK Monday, June 25, 2012 2:49 AM
    Monday, May 21, 2012 4:01 PM

All replies

  • It is good to re-use existing members, but not in this case. The project will be more manageable with distinct members.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, May 21, 2012 7:57 AM
  • Are you trying to calculate average sales/day ? at fiscal and also at calendar hierarchy ? If so, why in SUM function you are multiplying with number of days and dividing by the same ?

    Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

    Dont forget to mark it as Answered if found useful | myspeakonbi

    Monday, May 21, 2012 1:47 PM
  • Hi Rakesh,

    The formula above performs the computation for a business calculation as below

    ( [month 1 value * no. of days in month1] + [month 2 value * no. of days in month2] + [month 3 value * no. of days in month3] ) / (no. of days in that quarter)

    Monday, May 21, 2012 3:39 PM
  • One thing you should do is make the measure [Days in Period] generic so it is not tied to the Calendar Date hierarchy.

    Define it as

    CREATE MEMBER CurrentCube.[Measures].[DaysinPeriod]AS Count(EXISTING [Date].[Date].[Date]),VISIBLE =False;


    Then you can SCOPE it on the quarter level to look at months (since months are pointing to the same attribute in the dimension)

    SCOPE([Date].[Calendar].[CalendarQuarter], [Date].[Fiscal].[FiscalQuarter]);([Measures].[Internet Sales Amount])=SUM(EXISTING [Date].[Month].[Month],[Measures].[InternetSalesAmount]*[Measures].[DaysinPeriod])/[Measures].[DaysinPeriod];ENDSCOPE;

    hth


    -Remember to mark as helpful/the answer if you agree with the post.

    • Proposed as answer by Lola Wang Wednesday, May 23, 2012 3:02 AM
    • Marked as answer by Ivan WK Monday, June 25, 2012 2:49 AM
    Monday, May 21, 2012 4:01 PM