Hi,

I am a new MDX programmer and have a question regarging using business days
in metrics.

First, i have a column in the Time table (day grain) that is called
work_days_remaining which indicates the number of business days remaining in
the month. I would like to create a calculated member that compares
production for the work_days_remaining in the current month to the
production count last month with the same work_days_remaining value. It is
very anaolgous to the parrallelPeriod function. Should I make the
work_days_remaing a dimension, virtual dimension, or a member property? Any
ideas on how to write a MDX function to do this?

Joe

"Joe Genshlea" <joegensh (AT) yahoo (DOT) com> wrote

Here's a suggestion that may work.

Create a virtual dimension called perhaps "Workdays" of "Year", "Month", and
"WorkDaysRemaining" from the time dimension after adding WorkDaysRemaining
as a property of the day level.

Then try a calculated measure in the measures dimension. The idea is to use
the WorkdaysRemaining property off of the date as the workdays remaining
level in the other dimension.

IIF (isleaf([time].currentmember), -- assuming day is the leaf member of
your base time dimension
Crossjoin({[measures].[Production]},
StrToSet("[Workdays].[Month].[" +
[Time].currentmember.Properties("WorkdaysRemaining") + "]")
) -
Crossjoin(
{[Measures].[production]},
parallelperiod(
[Workdays].[Month],
-1, -- -1 gives "Next Month" Note that this does not assure that
next month exists...
StrToMember("[workdays].[month].[" +
[Time].currentmember.Properties("WorkdaysRemaining") + "]")
)
,
NULL
)
This should give you the difference between the current production and next
month's. Note that I'm stretching a bit here on my MDX and hope the StrToSet
and StrToMember give the right values. The trick is to get the
workdaysremaining property of the current day from the time dimensino to
become the active member of the other Workdays dimension. Maybe the above
will work. I've not done much with the StrTo... Functions.
HTH
Jim

Amazing... !

I will give this a shot ...thanks for your help.

Ironically, I think this would be fairly straight forward is SQL

Joe



"Jim Kaiser" <jkaiser-no-spam-att-henwoodenergy.com> wrote