Parallel Period in asymetric hierarchies

#1

Dear all

I have a custom Date hierarchy that implement following levels:
- year
- half year
- quarter
- month
- week

When I have a current date, e.g. 9/18/2006 (monday), I want to calculate
the date of monday one year ago --> 9/19/2005 (monday).

This works very well with the Parallelperiod funktion, but it fails when the
current year has more weekdays than the last year.

Any idea how I can handle this?

Kind regards,

Marc

#2

Hello Marc,

To understand the issue better, I'd like to know how you use Parallelperiod
in MDX and what is the exact purpose of it. Will you provide us more
informaiton on issues when a year has more weekdays?

I once use the following Mdx to get the data for a different year that's in
the same relative week of year.

WITH MEMBER [SRP DIM].[SRP].[Year2003] AS '([SRP DIM].[SRP].[All SRP],
ParallelPeriod([TIME DIM].[Time Week Of Year].[Year], 1, [TIME DIM].[Time
Week Of Year].CurrentMember))'

SELECT { [TIME DIM].[Time Week Of Year].[All
Time].[2004].[200440].[10/01/2004] }
ON COLUMNS,
Crossjoin( {[SRP DIM].[SRP].[Year2003]}, {[Measures].[Rooms]})
ON ROWS
FROM [SRP]

Also, you may want to look some discussions in preivous posts:

http://groups.google.com/group/micro...warehouse/brow
se_thread/thread/50255845deddbd2c/910e6359023bcf69?lnk=st&q=%22Selecting+the
+same+week+day+a+year+ago%22&rnum=1&hl=en#910e6359 023bcf69

http://groups.google.com/group/micro.../browse_thread
/thread/45c1638d74827fc7/10b119d6d3496d6d?lnk=st&q=%22Parellelperiod+hates+ m
y+years%22&rnum=1&hl=en#10b119d6d3496d6d

I look forward to your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

#3

Hi Peter

Thanks for your answer. OK, first a sample mdx that I use:
select
ParallelPeriod([Date].[Fiscal].[FiscalYear],1,
[Date].[Fiscal].[Date].&[20060331] on 0,
[Measures].[Sales] on 1
from TestCube

In this Fiscal Calender, starting Feb 01 to Jan 31, week 9 (last week of
march) has in 2006 5 days, but in 2005 only 4 days.

When I use the ParallelPeriod function, I receive a blank value. My problem
arises when I use the following query in SSRS:

....
sum(Parallelperiod([Date].[Fiscal].[FiscalYear],1,[Date].[Fiscal].[Date].&[20060331])
:
Parallelperiod([Date].[Fiscal].[FiscalYear],1,[Date].[Fiscal].[Date].&[20060601]), [Measures].[Sales])
....

The query will not see the first date and makes from a from to a
whateverexists to.

Thanks for your help,

Regards, Marc

#4

Hello Marc,

From your description, I think this behavior is normal since the paralle
period does not exist. Since it does not exist, it makes sense that it
aggregate from the date that exist in the members of the query.

If you have any thoughts of the normal behavior it should be, I'd like to
know and I will surely route it to the product team. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

#5

Hello Peter,

mmmm... what I think happens with the querry above is that it sums up all
sales from beginning of the date hierarchy - lets say it starts Jan 31 2003 -
the querry shows the sales form Jan31 2003 to June 02 2005.

I think a wished normal behaviour in this case would be:
If a week in the previous year has 4 days and the current year has 5 then
take the first weekday of the following week, this would be April 1st. So the
querry shows the sales from April 1 2005 to June 02 2005.

Best Regards,

Marc

#6

.... I was unclear in the point that it should not always take the first
weekday of the following week (this is coincidence here), it should take the
same WEEKDAY as in the current year.

Regards, Marc

#7

Hi Marc,

You didn't specify what should happen on the last day of the year - for
example, do you want the year-ago date for 12/31/2005 to be 01/01/2005
or 12/25/2004? Assuming the former option, could you use something like:

sum({[Date].[Fiscal].[Date].&[20060331].Lag(364)
: [Date].[Fiscal].[Date].&[20060601].Lag(364)},
[Measures].[Sales])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

#8

Hi Deepak

That's exactly the formula I used. But it does not work all the times.
Consider this case:

sum({[Date].[Fiscal].[Date].&[20080501].Lag(364)
: [Date].[Fiscal].[Date].&[20080801].Lag(364)},
[Measures].[Sales])

Aug 1st 2008 is a Friday and Aug 2nd 2007 is a Thursday. So this formula
fails to compare the same weekdays...

Regards,

Marc

#9

Hello Marc,

Thank you for your reply. I agree the "normal" behavior should return the
date from current year other than the a null or empty one in such
situaiton. However, this function is not supposed to be so smart and
actually for ParallelPeriod to work as expected you are supposed to have
the same descendant structure.

I think at least it shall provide some indications on this so that we can
use this information to get the correct result. I will certainly provide
your feedback to the proper channel. Also, you could submit feedback as I
mentioned.

Currrently you could use lag method as Deepak mentioned to work around this
issue since 364=52*7 and it can return the correct weekday. Also, you could
use nextmember/premember function to workaround the issue for thsi specific
query:

sum(Parallelperiod([Date].[Fiscal].[FiscalYear],1,[Date].[Fiscal].[Date].&[2
0060331].nextmember).premember
:
Parallelperiod([Date].[Fiscal].[FiscalYear],1,[Date].[Fiscal].[Date].&[20060
601]), [Measures].[Sales])

As per your question, I think 20080801 (Friday)-364=20070803 (Friday) and
this is because 2008 is a leap year so it shall has 2 days on date (8/1 and
8/3). I think 363 still works under this situation. Correct me if my
understanding is wrong.

If you have further questions or concerns on this, please feel free to
let's know. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====