Hello,

In my cube, I have created quite a few time calculations. One of my
calculations is "Prior Month to Date". The client I am working with
uses 4-5-4 fiscal quarters. Meaning the first month of the quarter
always has 4 weeks, the second month has 5 weeks, and the final month
has 4 weeks.

My MDX calculation looks like this:

Aggregate
(
{ [Time].[Fiscal Time Calculations].DefaultMember } *
PeriodsToDate
(
[Time].[Fiscal Time].[Fiscal Month],
COUSIN([Time].[Fiscal Time].CurrentMember,
ANCESTOR([Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]).PREVMEMBER)
)
)

So as you can see, I'm using the COUSIN function to determine the
corresponding week in the previous month. However, if I am looking at a
month with 5 weeks, the 5th week shows up at blank since there is no
corresponding member in the previous month.

Does anyone know how I would tell the 5th week to carry over the value
from the 4th week?

Some more info would be helpful, like:

- What are the levels in [Time].[Fiscal Time] hierachy?

- Does the calculation have to work at all these levels?

- Is there an attribute to "tag" months with 5 weeks?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for the response Deepak.

[Time].[Fiscal Time] looks like this: Fiscal Year -> Fiscal Quarter ->
Fiscal Month -> Fiscal Week -> Actual Day

The calculation would only work on a month, week, and day level.
However, the issue arises at a lagged level such as week.

Per your final question; there is not a hard attribute that flags a
week as week number 5. However, I imagine I could get the index of the
member as related to its parent?? Because every second month of a
quarter has 5 weeks every time.

Does that give you better information to diagnose the issue?

Thanks again!
Todd

Deepak Puri wrote:

Hi Todd,

That certainly helps - I was asking about the attribute because that
might be useful in an MDX script scoped calculation. Anyway, what I came
up with so far is rather complex-looking, and may only work at the day
and week levels.

At the month level, if the current month has 5 weeks, does the 4th week
of the previous month have to be extrapolated out to 5 weeks as well?

(
iif(Rank(Ancestor([Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Week]),
Descendants(Ancestor(
[Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]),
[Time].[Fiscal Time].[Fiscal Week])) <= 4,
PeriodsToDate
(
[Time].[Fiscal Time].[Fiscal Month],
ParallelPeriod([Time].[Fiscal Time].[Fiscal Month])
),
{Ancestor([Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]).PrevMember,
Head(Descendants(ClosingPeriod(
[Time].[Fiscal Time].[Fiscal Week],
Ancestor([Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]).PrevMember),
[Time].[Fiscal Time].CurrentMember.Level),
Rank([Time].[Fiscal Time].CurrentMember,
Descendants(Ancestor(
[Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]),
[Time].[Fiscal Time].CurrentMember.Level))
- Descendants(Ancestor(
[Time].[Fiscal Time].CurrentMember,
[Time].[Fiscal Time].[Fiscal Month]).PrevMember,
[Time].[Fiscal Time].CurrentMember.Level).Count)}))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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