none
Create a measure to count the number of months in the selected time period

    Question

  • Hi all,

    I am here stuck in creating a measure which will be the count of number of months in the selected period.

    My Date dimension(Reported Paid Date) hierarchy is:

    Paid Date :: Paid Years -> Paid Quarters -> Paid Months.

    The user will have the flexibility of selecting any levels from the date dimension and my measure should display the number of months in the selected period. There are a few cases to consider while creating this measure. For example:

    1. If a user selects year 2010,  the measure value should be 12(assuming that all the months in the year 2012 are present in the date dimension).

    2. If a user select 2010 Q3(jul, aug and sept) the value should be 3.

    3. If a user select Yr 2010, Quarter 2011 Q1 and months Nov 2011 and Dec 2011, then the measure value should be 12+3+2 = 17.

    In general, the measure should count the leaf level months count in the period of selection. Any selection can be made in the date dimension.

    Any brilliant ideas?

    Thank you in advance,

    Anish


    Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)


    Monday, May 21, 2012 2:27 PM

Answers

  • Thanks sandhya and martin ... i got the answer for solving this.... its a different approach ...

    The solution for this is to create a "count measure" from the dimension table and treat it as a measure group, this would work in all scenarios in terms of calculating a count of leaf member.


    Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

    Wednesday, May 23, 2012 3:34 AM

All replies

  • try this sample query..

    WITH member  MEASURES.MonthCount as

    Descendants([Date].[Calendar].currentmember,[Adjudicated Date].[Calendar].[Month],self).count

       SELECT 
       {MEASURES.MonthCount} on 0 
        from [Cubename] where  [Date].[Calendar].[Year].&[2011]

    above query will give count of months for any selected level in hierarchy


    Thanks and Regards, Sandhya

    Monday, May 21, 2012 3:53 PM
  • Hi Sandhya,

    My Date dimension(Reported Paid Date) hierarchy is:

    Paid Date :: Paid Years -> Paid Quarters -> Paid Months.

    I am using the following MDX to count the number of paid months for any selected levels:

    Member [Measures].[Number of Months] as           
    Descendants([Reported Paid Date].[Paid Date].currentmember,[Reported Paid Date].[Paid Date].[Paid Months],self).count    

    This gives correct months count when only a single year is selected, but when two years are selected and aggregated to form a member, this measure fails to give the output.

    The error is: CellOrdinal 2 VALUE #Error Query (7, 13) The MDX function CURRENTMEMBER failed because the coordinate for the 'Pd Yr' attribute contains a set.

    What am i missing here??

    Please give your thoughts.

    Thank you,

    Anish


    Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)


    Monday, May 21, 2012 5:12 PM
  • Hi Anish,

    it is throwing error because multi select is not allowed with Current Member

    Try this..

    WITH  SET Months as 
       [Date].[Calendar].currentmember.children
    MEMBER measures.X AS
      descendants([Date].[Calendar],[Date].[Calendar].[Month],self).count
    SELECT Measures.X ON 0, distinct(Months) on 1
    from (select   {[Date].[Calendar].[Calendar Year].&[2005]:[Date].[Calendar].[Calendar Year].&[2006]} on 0
    FROM [Adventure Works])

    or this...


    WITH  SET Months as 
       descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Month],self)
    MEMBER measures.X AS
      descendants([Date].[Calendar],[Date].[Calendar].[Month],self).count
    SELECT Measures.X ON 0, distinct(Months) on 1
    from (select   {[Date].[Calendar].[Calendar Year].&[2005]:[Date].[Calendar].[Calendar Year].&[2006],[Date].[Calendar].[Calendar Quarter].&[2007]&[1]} on 0
    FROM [Adventure Works])


    Thanks and Regards,

    Sandhya


    Monday, May 21, 2012 7:05 PM
  • Sandhya is correct. You cannot use CurrentMember when multiselects are present in your query. If your query tool uses the WHERE clause to filter members, you can use the EXISTING declaration on the set of months.

    WITH MEMBER [Measures].[# of Months] AS
    	COUNT( EXISTING [Date].[Calendar].[Month].Members )
    SELECT	{
    		[Measures].[# of Months]
    	} ON COLUMNS
    FROM	[Adventure Works]
    WHERE	({
    		[Date].[Calendar].[Calendar Year].&[2007],
    		[Date].[Calendar].[Calendar Semester].&[2008]&[1],
    		[Date].[Calendar].[Month].&[2008]&[8]
    	})

    If your query tool is using subselects, it won't that easy. You may want to look at some of Mosha's older blog post also.

    http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm

    HTH, Martin


    http://martinmason.wordpress.com

    Tuesday, May 22, 2012 1:50 AM
  • Thanks sandhya and martin ... i got the answer for solving this.... its a different approach ...

    The solution for this is to create a "count measure" from the dimension table and treat it as a measure group, this would work in all scenarios in terms of calculating a count of leaf member.


    Anish Tuladhar - Do not forget to mark as answer, only if helpful.:)

    Wednesday, May 23, 2012 3:34 AM