We have a calculation as follows:

We track sales and inventory weekly (fact table time granularity).
Measures of interest = [Sales Qty] and [On Hand Qty]
We need to define a calculation that should return (Average Sales Qty /
Current Week On Hand Qty)
Such that if we filter on Current Year = 2006 we would get (Avg Weekly Sales
Through Week 26 / On Hand Qty in Week 26)

How do we specifiy the divisor value to always be the value for the last
week in the time dimension selected (Year, Month or Week)

I hope this question makes sense.

Thanks

Someone else with a little more skill in this area may speak up as well, but
the way I've tackled this in the past is as follows:

** For your cube, if users are required to pick a year, I would suggest that
you create a default member on the time dimension to be the current year...

case
when [Time].[Year].CurrentMember = datepart("yy", VBA!Now()) then
([Measures].[Average Sales Qty] / [Measures].[On Hand Qty],
{ StrToMember to create Week 1 this year : StrToMember to
create Week ~n this year})
else
([Measures].[Average Sales Qty] / [Measures].[On Hand Qty],
{ StrToMember to create Week 1 that year : StrToMember to
create Week x based on 12/31/that year})

end

I apologize for the pseudocode, but after a couple glasses of wine, my
synapses dont' connect enough to provide the detail.

Depending on your time dimension layout

[Time].[Year].[Quarter].[Month].[Week].[Day] ?

You could assemble the correct week member using a combination of:


STRTOMEMBER( "[Time].&[" & datepart("yy", VBA!Now()) & "].&[" &
datepart("q", VBA!Now()) & "].&[" &
datepart("m", VBA!Now()) & "].&[" &
datepart("w", VBA!Now()) & "].&[" &
datepart("dy", VBA!Now()) & "]")

The two codes I'm not positive on are "w' (for week in year) and "dy" (for
day of year)'

Again, it depends on the layout of your time dimension.

You can also supply "CDate("12/31/2005")" in place of "VBA!Now()" to do the
equivalent for prior year.

HTH


"Steven Heinz" <steven.heinz (AT) ilg (DOT) com> wrote

Thanks Tim. Actually it is much easier than what you suggest. The answer
is create a semi-additive measure using the Last Nonempty Value method.
This provides the "current value in any time slice.

~Steven

"Tim Dot NoSpam" <Tim (AT) MindYourSpammy (DOT) spam> wrote

Like I said... <g>

Actually, I knew this, but I was just testing you. Thanks, I learned
something new today.

"Steven H" <steven.heinz (AT) ilg (DOT) com> wrote