 Multiple Time Hierarchy • 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

• 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 Wednesday, May 23, 2012 3:02 AM
• Marked as answer by 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.

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 Wednesday, May 23, 2012 3:02 AM
• Marked as answer by Monday, June 25, 2012 2:49 AM
Monday, May 21, 2012 4:01 PM