How to use YTD on a mirror period

Hi friends

I have a funny situation. I have a period dimension which contains Year, Qtr, Month. I also have another Time dimension which contains Mth1 .. Mth25.

The 2nd Time dimension matches that of the 1st Time dimension ie. Mth25 is 2005.April. and Mth24 is 2005.March and so on...

However I need to calculate YTD when MTH25 is selected from the dimension.

I can't exactly do YTD (Mth25) as Mth25 is not a time dimension. Is there a way to Retrieve the equivalent time dimension in the form of 2005.April from Mth25 so that I can use in YTD Calculation ?

Thanks

Tom

Do you have a property saying the month?
If so you can do YTD(STRTOMEMBER("[Time].[Month].["+[MthDim].CURRENTMEMBER.PROPERTIES("Month Name")+"]"))
I hope it helps you.|||Thinking out loud a bit (i.e., have not tried this), but you might be able to do something like this. Use the Rank() function to determine what month number you have within the current member's siblings:

Rank([MthDim].CurrentMember, [MthDim].CurrentMember.Siblings)

This should return 1 for Mth1, 14 for Mth14, and so on.

Then, use this with the Item() function (substracting 1 given that Rank() returns a 1-based position while Item() expects a 0-based position) to decide which member in the time dimension to return:

Descendents(StrToMember("[Time].[Year].[" + [MthDim].CurrentMember.Parent.Name + "]"), [Time].[Month]).Item(<rank function> - 1)

This assumes you have a [Year] level in [MthDim] as well as [Time]. Might work...

Dave Fackler
|||That was helpful... however when this is done

sum(YTD(StrToMember("[Period2].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Year") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Quarter") + "].
&[" + [Period].CURRENTMEMBER.PROPERTIES("Month") + "]"
)), [Measures].[Unit] )

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

the sum returned is not for Year To Date ie. April figure + March Figure + Feb.. + Jan..

When I pivot to Month 25 in Period2 which is 2005-April. The value thats displayed is ONLY the figure for APRIL instead of April figure + March Figure + Feb.. + Jan..

do you have any idea why this is the case ?

Thanks
Tom|||sorry made a mistake in the above post

Where Period2 is Month1 .. 25
where Period is Year, Quarter, Month

is actually

Where Period2 is Year, Quarter, Month
where Period is Month1 .. 25 but contains Year, Quarter, Month as properties.

So, sorry but just to repeat the problem is still there if anyone could help I'd appreciate it

Thanks
Tom
|||I think I know.. Because we're returning a Member using StrToMember instead maybe we should use StrToSet. Because we're only returning a member when YTD is performed it is only calculating that member.

Maybe we should use StrToSet ?

though I'm not entirely show how the expression should go.

any help is appreciated

Thankyou
Tom|||Hi Tom,

YTD() does want a member, so I believe using StrToMember is the right approach. Does your [Period] dimension (the one with Mth1...Mth25) contain a [Year] level? I am assuming it does since it sounds as if your time span is greater than one year (since [Period2] has [Year], [Quarter], etc.). If this is the case, you need to make sure the dimension is marked as a "Time" dimension (property of the dimension) and the [Year] level is marked as a "Years" level (property of the level).

The YTD(<member>) function is a shortcut for the PeriodsToDate([Year], <member>) function. The YTD() function, however, needs to be able to determine what your [Year] level is. It does this using the properties of the levels in the dimension to determine which one has been marked as "Years". This allows the YTD() function to know the year parent for the <member> in question and then correctly aggregate all the members from the first child under that year up through the <member> specified.

Dave Fackler
|||

Hi david

You're right YTD() only take member.

The [Period] dimension with Mth1..25 is set as a Time Type and it also contains a [Year] level which currently is hidden with visible set to false and it is marked as a Years level.

Something strange that I've noticed is that the below query only returns the YTD for the month [Period2].&[2005].&[2].&[4], instead of from [Period2].&[2005].&[1].&[1] + [Period2].&[2005].&[1].&[2] + [Period2].&[2005].&[1].&[3] +
[Period2].&[2005].&[2].&[1]...

I think the issue lies here. what is the difference between

1. sum(ytd([Period2].&[2005].&[2].&[4]), [Measures].[Unit])

and

2. sum(ytd([Period2].currentmember), [Measures].[Unit])
2. which when pivoted to 2005 april returns the ytd total of jan + feb+ march +april which is what i want.

however 1. only returns the unit for [Period2].&[2005].&[2].&[4] only

Thanks alot for your help !
Tom

|||I've really ran out of ideas.

Is there another way to do this ? surely it shouldn't be this difficult to do something like it ?

:) any suggestions

Thanks
Tom