none
calculate difference between quarters

    Question

  • Hi just trying out some MDX for my understanding as I am still new and have the following statement

    Select {[Measures].[Bounce Count]} on columns,
    {([Dim Date].[Calendar Quarter].&[1],[Dim Date].[Calendar Year].&[2011]),
    ([Dim Date].[Calendar Quarter].&[2],[Dim Date].[Calendar Year].&[2011]),
    ([Dim Date].[Calendar Quarter].&[3],[Dim Date].[Calendar Year].&[2011]),([Dim Date].[Calendar Quarter].&[4],[Dim Date].[Calendar Year].&[2011])}
      on rows
    FROM [TBGold]
    

    what I would like to do is to calculate the difference in Bounce counts between the quarters

    so the bounce count for Q1 is 180336 and the bounce count for Q2 is 630247 so I would be looking for a answer of 449911.

    What would be the best way of going about this?

    regards

    Tuesday, September 27, 2011 12:16 PM

Answers

  • Hi,

    The best way would be to have a calendar user hierarchy that would have Year-Quarter-... This way you would have specific quarters of years in the quarter level (Q1 2011, Q2 2011,...Q1 2012, Q2 2012,..). This requires that you use composite keys (quarter, year) so you can create proper attribute relationships. Note that it is quite common to have both "Quarter of  Year" attribute  (generic eg 1-4) and also the "Quarter" attribute (Years*4 member) in the date dimension.

    Please have a look in the AdventureWorks sample database to understand the design better.

    Your calculation would then simply be:

    with 
    member [Measures].[Bounce Count Prev Period] as
    ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    
    member [Measures].[Bounce Count Prev Period Diff] as
    [Measures].[Bounce Count] - [Measures].[Bounce Count Prev Period]
    
    select
    {
    	[Measures].[Bounce Count]
    	[Measures].[Bounce Count Prev Period],
    	[Measures].[Bounce Count Prev Period Diff]
    } on 0,
    Descendants
    (
    	[Dim Date].[Calendar].[Calendar Year].&[2010],
    	[Dim Date].[Calendar].[Calendar Quarter]	
    ) on 1
    from
    [TBGold]
    


    Note that this calculation would work on any level used (eg Year, Quarter, Month) and it would return the previous member value relative to the current calendar member (for Year -> Year-1, Quarter -> Quarter-1, Month->Month-1 ...)

    If you want it to work on Quarters exclusively then you would use a simple SCOPE assignment in the cube mdx script:

    CREATE MEMBER CurrentCube.[Measures].[Bounce Count Prev Period Diff] AS NULL;
    
    SCOPE([Dim Date].[Calendar].[Calendar Quarter], [Measures].[Bounce Count Prev Period Diff]);
    	this = [Measures].[Bounce Count] - ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    END SCOPE;
    


    HTH,

    Hrvoje Piasevoli


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    • Marked as answer by aivoryuk Tuesday, September 27, 2011 7:14 PM
    Tuesday, September 27, 2011 1:10 PM

All replies

  • Hi,

    You can use below code -

    WITH 
    MEMBER [Measures].[Q1_Bounce Count] AS ([Measures].[Bounce Count], ([Dim Date].[Calendar Quarter].&[1])
    MEMBER [Measures].[Q2_Bounce Count] AS ([Measures].[Bounce Count], ([Dim Date].[Calendar Quarter].&[2])
    MEMBER [Measures].[Q1-Q2_Bounce Count] AS ([Measures].[Q1_Bounce Count] - [Measures].[Q2_Bounce Count])
    
    SELECT [Measures].[Q1-Q2_Bounce Count] ON 0
    FROM [TBGold]

    Thanks,
    Santosh

     

    Tuesday, September 27, 2011 1:07 PM
  • Hi,

    The best way would be to have a calendar user hierarchy that would have Year-Quarter-... This way you would have specific quarters of years in the quarter level (Q1 2011, Q2 2011,...Q1 2012, Q2 2012,..). This requires that you use composite keys (quarter, year) so you can create proper attribute relationships. Note that it is quite common to have both "Quarter of  Year" attribute  (generic eg 1-4) and also the "Quarter" attribute (Years*4 member) in the date dimension.

    Please have a look in the AdventureWorks sample database to understand the design better.

    Your calculation would then simply be:

    with 
    member [Measures].[Bounce Count Prev Period] as
    ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    
    member [Measures].[Bounce Count Prev Period Diff] as
    [Measures].[Bounce Count] - [Measures].[Bounce Count Prev Period]
    
    select
    {
    	[Measures].[Bounce Count]
    	[Measures].[Bounce Count Prev Period],
    	[Measures].[Bounce Count Prev Period Diff]
    } on 0,
    Descendants
    (
    	[Dim Date].[Calendar].[Calendar Year].&[2010],
    	[Dim Date].[Calendar].[Calendar Quarter]	
    ) on 1
    from
    [TBGold]
    


    Note that this calculation would work on any level used (eg Year, Quarter, Month) and it would return the previous member value relative to the current calendar member (for Year -> Year-1, Quarter -> Quarter-1, Month->Month-1 ...)

    If you want it to work on Quarters exclusively then you would use a simple SCOPE assignment in the cube mdx script:

    CREATE MEMBER CurrentCube.[Measures].[Bounce Count Prev Period Diff] AS NULL;
    
    SCOPE([Dim Date].[Calendar].[Calendar Quarter], [Measures].[Bounce Count Prev Period Diff]);
    	this = [Measures].[Bounce Count] - ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    END SCOPE;
    


    HTH,

    Hrvoje Piasevoli


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    • Marked as answer by aivoryuk Tuesday, September 27, 2011 7:14 PM
    Tuesday, September 27, 2011 1:10 PM
  • Hi,

    The best way would be to have a calendar user hierarchy that would have Year-Quarter-... This way you would have specific quarters of years in the quarter level (Q1 2011, Q2 2011,...Q1 2012, Q2 2012,..). This requires that you use composite keys (quarter, year) so you can create proper attribute relationships. Note that it is quite common to have both "Quarter of  Year" attribute  (generic eg 1-4) and also the "Quarter" attribute (Years*4 member) in the date dimension.

    Please have a look in the AdventureWorks sample database to understand the design better.

    Your calculation would then simply be:

     

    with 
    
    
    
    member [Measures].[Bounce Count Prev Period] as
    
    
    
    ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    
    
    
    
    
    
    
    member [Measures].[Bounce Count Prev Period Diff] as
    
    
    
    [Measures].[Bounce Count] - [Measures].[Bounce Count Prev Period]
    
    
    
    
    
    
    
    select
    
    
    
    {
    
    
    
    	[Measures].[Bounce Count]
    
    
    
    	[Measures].[Bounce Count Prev Period],
    
    
    
    	[Measures].[Bounce Count Prev Period Diff]
    
    
    
    } on 0,
    
    
    
    Descendants
    
    
    
    (
    
    
    
    	[Dim Date].[Calendar].[Calendar Year].&[2010],
    
    
    
    	[Dim Date].[Calendar].[Calendar Quarter]	
    
    
    
    ) on 1
    
    
    
    from
    
    
    
    [TBGold]
    
    
    
    

     


    Note that this calculation would work on any level used (eg Year, Quarter, Month) and it would return the previous member value relative to the current calendar member (for Year -> Year-1, Quarter -> Quarter-1, Month->Month-1 ...)

    If you want it to work on Quarters exclusively then you would use a simple SCOPE assignment in the cube mdx script:

     

    CREATE MEMBER CurrentCube.[Measures].[Bounce Count Prev Period Diff] AS NULL;
    
    
    
    
    
    
    
    SCOPE([Dim Date].[Calendar].[Calendar Quarter], [Measures].[Bounce Count Prev Period Diff]);
    
    
    
    	this = [Measures].[Bounce Count] - ([Dim Date].[Calendar].CurrentMember.PrevMember, [Measures].[Bounce Count])
    
    
    
    END SCOPE;
    
    
    
    

     


    HTH,

    Hrvoje Piasevoli


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Hi with tht first part of your code it does not bring me back any rows? What would this be down to? ( edit I have tried it without anything on the rows and I get the bounce count but your with members bring back nulls)
    • Edited by aivoryuk Tuesday, September 27, 2011 2:19 PM
    Tuesday, September 27, 2011 2:15 PM
  • Hi,

    Well do you have the Calendar hierarchy built? The code is written to work against a Calendar hierarchy that has Year level and Quarter level.


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    Tuesday, September 27, 2011 2:18 PM
  • Hi,

    Well do you have the Calendar hierarchy built? The code is written to work against a Calendar hierarchy that has Year level and Quarter level.


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Hi yes I do, I managed to get this to work I just had to tweak the code slightly
    with 
    member [Measures].[Bounce Count Prev Period] as
    ([Dim Date].[Calendar quarter].CurrentMember.PrevMember, [Measures].[Bounce Count])
    


    I just added the quarter to the calender and the orignal row data from my query and it has me back what I was after.

    when I do use the orignal code you presented it no rows.

    It may be me still being new to MDX

    Tuesday, September 27, 2011 2:47 PM
  • Hi,

    Well the code must always be adjusted to the structure of your dimension and hierarchy naming.

    When you write [Dim Date].[Calendar quarter].CurrentMember you are referring to current member in the [Calendar quarter]  hierarchy of the [Dim Date] dimension. So you wouldn't normally name a calendar hierarchy as [Calendar quarter] but instead something like [Calendar]. This calendar hierarchy is different than other hierarchies because unlike attribute hierarchies that have only a single attribute (+ the all level) the user hierarchies typically describe the natural data groupings. For example Calendar hierarchy could have 4 levels Years, Quarters, Months, Dates that naturally roll up from lower levels to higher levels. This would be backed up by defining attribute relationships between attribute hierarchies.

    Anyway, I'm glad if you are ok with this, but I have a strong feeling you haven't done it right, and would again suggest to have a look in AdventureWorks date dimension.

    Regards,

    Hrvoje Piasevoli

     


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    Tuesday, September 27, 2011 2:57 PM
  • Hi,

    Well the code must always be adjusted to the structure of your dimension and hierarchy naming.

    When you write [Dim Date].[Calendar quarter].CurrentMember you are referring to current member in the [Calendar quarter]  hierarchy of the [Dim Date] dimension. So you wouldn't normally name a calendar hierarchy as [Calendar quarter] but instead something like [Calendar]. This calendar hierarchy is different than other hierarchies because unlike attribute hierarchies that have only a single attribute (+ the all level) the user hierarchies typically describe the natural data groupings. For example Calendar hierarchy could have 4 levels Years, Quarters, Months, Dates that naturally roll up from lower levels to higher levels. This would be backed up by defining attribute relationships between attribute hierarchies.

    Anyway, I'm glad if you are ok with this, but I have a strong feeling you haven't done it right, and would again suggest to have a look in AdventureWorks date dimension.

    Regards,

    Hrvoje Piasevoli

     


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Hi thanks very much for coming back to me, I have now tried what you supplied on the Adventures and it works as you described, I shall try to figure out how to get the same performance from the cube that I am working with.

     

    You have also introduced the Descendants function to me and that looks interesting if using the Self_and_after etc

    Best Regards

    Alex

    Tuesday, September 27, 2011 7:22 PM