# 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

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

• Proposed as answer by Wednesday, March 30, 2011 2:47 AM
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.