# Period over Period

### Question

• All,

I need to find a way to do a period over period calculation which works with any member of my time dimension.  So, basically I want to be able to pick like q1 07, or 07 or 1-1-07.  And in each case the period over period calculation should work just as well.  Furthermore, I have the added requiremment that I need to be able to specify an arbitrary range as well so like the first 45 days of 07 or lets say day 5 through day 57 of 2007.  I need the Period over Period calculation to be able to handle each of these use cases equally well.

I haven't seen an example of a calculation that does this, so if you can point me to any resources for this that would help greatly.

Thanks,

dcb99

Tuesday, October 09, 2007 5:00 PM

• Are you able to apply the ideas in this blog entry, which I think discusses a similar problem?

## Time calculations in UDM: Parallel Period

Comparing current data to the data in the same unit (day, month, week) in the previous period (year, quarter, semester) is one of the most fundamental tasks in the OLAP system. In MDX this is usually archived through the ParallelPeriod function. However, the classic approach to ParallelPeriod doesn't work well in UDM where Time dimension has many attributes and hierarchies, and where symmetric hierarchy structure is hard to achieve across Calendar, Fiscal and other hierarchies. Time Intelligence wizard doesn't address all these issues either. In this article we will discuss strategies how to build MDX Script calculations for parallel period to work properly across all attributes and hierarchies.

...
Tuesday, October 09, 2007 6:28 PM
• Well, I'm not sure whether a short example exists which covers most general cases - but simplification may be possible for your specific scenario. Could you describe what your time dimension looks like - does it have mutltiple hierarchies, like calendar vs. fiscal (if not, that may simplify matters)?

Also, when you say: "Furthermore, I have the added requiremment that I need to be able to specify an arbitrary range as well so like the first 45 days of 07 or lets say day 5 through day 57 of 2007..", which client tool(s) will be used to access the cube; and does this analysis apply to multiple measures? This is important to consider because, as you may have read elsewhere in Mosha's blog, there is not yet a general solution for multi-select across different AS clients.

Tuesday, October 09, 2007 8:35 PM
• When you say "natural", weeks typically can overlap 2 calendar months, unless the months are organized to avoid this. Also, you didn't describe the AS client tool(s), and whether period-over-period analysis will apply to multiple measures?

Tuesday, October 09, 2007 9:22 PM
• Client tool is primarily Reporting Services 2005.  They may also do ad-hoc analysis using Excel 2007, but if that complicates it don't worry about Excel.

Regarding whether there would be multiple measures- the answer is yes.  If I have to create multiple calculations to simplify it I can do that as well.

dcb99

Wednesday, October 10, 2007 1:14 AM
• @KLIMENTINA, to set some context around my scenario and skillset, I was developing a business analytics graphical dashboard using the .DBF data file structure (which is NOT a cubing database) and a procedural programming language.  The user interface was to have the user select the timeframe (Today, Yesterday, Week, Week-to-Date, etc..., Date Range; a total of 15 different time dimensions) and also select a comparison timeframe (None, Period-Over-Period, Year-Over-Year, Date Range).  The goal was to develop a "black box" routine that would calculate the start and end dates for the two timeframes to be compared against each other.  Once I had the four date values calcuated based on the parameters selected by the user, I could then use them in my SQL statements along with other parameters selected by the user.  So, the goal was to have a routine that would accept two parameters (Timeframe, Comparison) and return 4 date values representing the start and end values for the base timeframe along with the comparison timeframe---this is the key logic to the whole algorithm!!!  Once you get the 4 dates calculated, you can compute against all transactional records that meet the user's criteria.

I need to head out to work now...if you have additional questions, please let me know.  Like I said in my previous post, the code to accomplish this was 450 lines (including lots of comments if I remember correctly) and can be called from anywhere within my application.

Gene
Thursday, August 25, 2011 11:31 AM

### All replies

• Are you able to apply the ideas in this blog entry, which I think discusses a similar problem?

## Time calculations in UDM: Parallel Period

Comparing current data to the data in the same unit (day, month, week) in the previous period (year, quarter, semester) is one of the most fundamental tasks in the OLAP system. In MDX this is usually archived through the ParallelPeriod function. However, the classic approach to ParallelPeriod doesn't work well in UDM where Time dimension has many attributes and hierarchies, and where symmetric hierarchy structure is hard to achieve across Calendar, Fiscal and other hierarchies. Time Intelligence wizard doesn't address all these issues either. In this article we will discuss strategies how to build MDX Script calculations for parallel period to work properly across all attributes and hierarchies.

...
Tuesday, October 09, 2007 6:28 PM
•

Deepak,

I've already read that blog, and it doesn't quite do it for me.  I need a more straightforward example of how this can be done.  I'm really looking for a short/concise example of how to do this "generic" period over period calculation.

Thanks,

dcb99

Tuesday, October 09, 2007 8:20 PM
• Well, I'm not sure whether a short example exists which covers most general cases - but simplification may be possible for your specific scenario. Could you describe what your time dimension looks like - does it have mutltiple hierarchies, like calendar vs. fiscal (if not, that may simplify matters)?

Also, when you say: "Furthermore, I have the added requiremment that I need to be able to specify an arbitrary range as well so like the first 45 days of 07 or lets say day 5 through day 57 of 2007..", which client tool(s) will be used to access the cube; and does this analysis apply to multiple measures? This is important to consider because, as you may have read elsewhere in Mosha's blog, there is not yet a general solution for multi-select across different AS clients.

Tuesday, October 09, 2007 8:35 PM
• The hierarchy is the natural calendar hierarchy:

year - qtr - month - week - day

However, I can change my time dimension to make the implementation of Period over Period more easy, so if there is a problem with this, then I could make changes there.

dcb99

Tuesday, October 09, 2007 8:51 PM
• When you say "natural", weeks typically can overlap 2 calendar months, unless the months are organized to avoid this. Also, you didn't describe the AS client tool(s), and whether period-over-period analysis will apply to multiple measures?

Tuesday, October 09, 2007 9:22 PM
• Client tool is primarily Reporting Services 2005.  They may also do ad-hoc analysis using Excel 2007, but if that complicates it don't worry about Excel.

Regarding whether there would be multiple measures- the answer is yes.  If I have to create multiple calculations to simplify it I can do that as well.

dcb99

Wednesday, October 10, 2007 1:14 AM
• I know it has been 3.5 years on this thread, but I have developed an algorithm that take any timeframe, including user entered date range, and produce period-over-period as well as year-over-year associated values, including user entered second date range for comparison.  The algorithm is only 450 lines of code and it handles any timeframe and any comparison times.
Gene
Wednesday, March 30, 2011 2:52 AM
• I belive that your solution will be helpful for my problem.

I am looking for help on similar meter.

Here is my post: http://social.msdn.microsoft.com/Forums/eu/sqlanalysisservices/thread/327dfe2d-7d47-4f5f-be98-28476f4d0179.

Could you please explain how have you done this.

Thursday, August 25, 2011 8:40 AM
• @KLIMENTINA, to set some context around my scenario and skillset, I was developing a business analytics graphical dashboard using the .DBF data file structure (which is NOT a cubing database) and a procedural programming language.  The user interface was to have the user select the timeframe (Today, Yesterday, Week, Week-to-Date, etc..., Date Range; a total of 15 different time dimensions) and also select a comparison timeframe (None, Period-Over-Period, Year-Over-Year, Date Range).  The goal was to develop a "black box" routine that would calculate the start and end dates for the two timeframes to be compared against each other.  Once I had the four date values calcuated based on the parameters selected by the user, I could then use them in my SQL statements along with other parameters selected by the user.  So, the goal was to have a routine that would accept two parameters (Timeframe, Comparison) and return 4 date values representing the start and end values for the base timeframe along with the comparison timeframe---this is the key logic to the whole algorithm!!!  Once you get the 4 dates calculated, you can compute against all transactional records that meet the user's criteria.

I need to head out to work now...if you have additional questions, please let me know.  Like I said in my previous post, the code to accomplish this was 450 lines (including lots of comments if I remember correctly) and can be called from anywhere within my application.

Gene
Thursday, August 25, 2011 11:31 AM