I'm getting wrong results in one of my Time hierarchies for Year Ago
measures. I have 2 Period hierarchies:
- [Period].[Gregorian]
- [Period].[TCCC 445]

I'm using the ParallelPeriod function and it works perfectly on the
Gregorian hierarchy. The [Period].[TCCC 445] hierarchy is working well,
except for the lowest level (DAY level) in the first week of each year.

MDX CODE:
iif(MembertoStr([Period].[TCCC 445].CurrentMember) = "[Period].[TCCC
445].[All Periods]",
(ParallelPeriod([Period].[Gregorian].[Year]), [Measures].[Unit Cases]),
(ParallelPeriod([Period].[TCCC 445].[Year]), [Measures].[Unit Cases])
)

The [Period].[TCCC 445] is a custom hierarchy, and these are the requirements:
- Year always starts on Jan 1st and ends on Dec 31st
- Year always has 52 weeks
- Weeks has 7 days, from Sat to Fri (Except the first and last week of each
year, that it could have more or less days)
- these are the levels: Year ---> Quarter ----> Month ----> Week ----> Day

This is what is happening

PERIOD UC UC YrAgo (WRONG) UC YrAgo (RIGHT)
------------------------- ---------
--------------------- -----------------------
- week1 2004
- 01/01/2004 (Thu 846 365
- 01/02/2004 (Fri) 614 719
- 01/03/2004 (Sat) 600 011
- 01/04/2004 (Sun) 965
- 01/05/2004 (Mon) 208
- 01/06/2004 (Tue) 536
- 01/07/2004 (Wed) 001 365
- 01/08/2004 (Thu) 039 719
- 01/09/2004 (Fri) 843 011
- week1 2005
- 01/01/2005 (Sat) 536 846 600
- 01/02/2005 (Sun) 570 614 965
- 01/03/2005 (Mon) 810 600 208
- 01/04/2005 (Tue) 243 965 536
- 01/05/2005 (Wed) 883 208 001
- 01/06/2005 (Thu) 215 536 039
- 01/07/2005 (Fri) 940 001 843

UC is my base measure, UC YrAgo is the one I'm calculating. MDX is returning
as Year Ago the same position in the particular Week. In this example for
01/01/2005 (Sat), MDX is returning 01/01/2004 (Thu), even though they are not
the same day of the week. --- see 2nd column

My business requiremens is to return the same day of the week, for example.
01/01/2005 (Sat) should return 01/03/2004 (Sat) ---> see 3rd column
the first 2 days of Year 2004 will not be used anywhere for Year Ago.

I understand what MDX is doing, but it does NOT meet my business requirements.

Any ideas how can I create a MDX to return what I want?

Another way to solve my problem would be creating an attribute in my
relational tables that it would tell me which period is my Year Ago and
mapped it as a Member property. This would involve changes on the Data Mart
and I would prefer not to use this solution, unless I can't find a way in
MSAS.

Thanks for any inputs.

Ercilia Hirata
Coca Cola Company

Assuming that the Day member names are as in the sample data, so that
the Day of Week can be derived from them:

is [Period].[TCCC 445].[All Periods],
(ParallelPeriod([Period].[Gregorian].[Year]),
[Measures].[Unit Cases]),
iif([Period].[TCCC 445].CurrentMember.Level
is [Period].[TCCC 445].[Day] and
[Period].[TCCC 445].Parent
is OpeningPeriod([Period].[TCCC 445].[Week],
Ancestor([Period].[TCCC 445].CurrentMember,
[Period].[TCCC 445].[Year])),
(Generate({[Period].[TCCC 445].CurrentMember} as CurDay,
Filter(ParallelPeriod([Period].[TCCC 445].[Year], 1,
[Period].[TCCC 445].Parent).Children,
Right(CStr([Period].[TCCC 445].CurrentMember.Name), 5)
= Right(CStr(CurDay.Item(0).Name), 5))).Item(0),
[Measures].[Unit Cases]),
(ParallelPeriod([Period].[TCCC 445].[Year]),
[Measures].[Unit Cases])))

But could there be an issue when the first week of a year is less than 7
days (which was listed as a possibility)? How can you then be sure to
find the same day of week?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your reply, but I found a solution, working with the position of
the day in the week 1 Current year vs Last Year.

Ercilia