# 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.

regards

Tuesday, September 27, 2011 12:16 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 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 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 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