Question to ytd()

Hi,

Ihave a Time-Dimension with 2 hierarchies: (a) Year - Semester - Quarter - Month and (b) Year -Week.

Than I have a calculated member in my Cube like:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(ytd(), [Measures].[Sales Volume KG]),
FORMAT_STRING = "#,#.00",
VISIBLE = 1 ;

When I browse the cube and I use the (a) hierarchy on the colums, all works fine; I get the accumulated sums over Semesters, Quarters, Month. When I use the (b) hierarchy, in each cell I get the normal week-sum and not the accumulated. What's my mistake?

Thanks
Hans

Not sure whether you've read the "Time calculations in UDM: Parallel Period" entry in Mosha's blog, but it discusses approaches to designing time calculations which work with multiple time hierarchies. In your specific case, YTD() may always be using hierarchy (a) by default, since you didn't specify a parameter. And, as explained in the blog entry: "If Time dimension has hierarchies which share some attributes, then it might not be possible to say by which hierarchy user browsed". So, my guess is that, when a week is selected on hierarchy (b), the current member of hierachy (a) becomes the corresponding year, in which case YTD() returns a set with that single member.

If you post more info on the relationships and shared attributes between the 2 time hierarchies, maybe someone here can give a good solution?

|||

Hi Deepak,

Thanks for the reply. My hierarchies have the following relationships (from top down; the indent-part are the attribute relationships):

Name: [Date - Year]
- Year
- Semester
- SemesterDescription
- Year
- Quarter
- QuarterDescription
- Semester
-Month
- MonthDescription
- Quarter

Name: [Date - Week]
- Year
- Week
- WeekDescription
- Year

The only shared attribute between the 2 hierarchies is "Year". The [Date] Dimension is a "hand-made" Table (with fields for FullAlternateDate, Year, Semester, ...) and not the built-in on of AS2005.

Hope this helps for finding an answer.

Thanks
Hans

|||

Hi Hans,

Am I correct in assuming that the key attribute of the dimension is [Date], though it isn't explicitly shown in any hierarchy - and that [Date] is also the granularity attribute for the measure group(s) in question?

|||

Hi Deepak,

The key attribute of the Dimension is [TimeKey] which is a runnung number (1 ... 9497) from 01.01.2000 until 31.12.2025 and the TimeKey ist the granulatiy attribute. You are correct.

The DimTime is handmade (by me with a small Microsoft Access Function) and has the following fields:

TimeKey (PrimaryKey), FullDateAlternateKey, DayNumberOfWeek, GermanDayNameOfWeek, EnglishDayNameOfWeek, SpanishDayNameOfWeek, FrenchDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear, GermanMonthName, EnglishMonthName, SpanishMonthName, FrenchMonthName, MonthNumberOfYear, CalendarQuarter, CalendarYear, CalendarSemester, FiscalQuarter, FiscalYear, FiscalSemester

Example 1	01.01.2000 00:00:00	7	Samstag	Saturday	Sábado	Samedi	1	1	52	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1
2	02.01.2000 00:00:00	1	Sonntag	Sunday	Domingo	Dimanche	2	2	1	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1

Here is the Hierarchy with the original Names and not the abbreviations:

Name: [Date - Year]
- CalendarYear
- CalendarSemester
- SemesterDescription
- CalendarYear
- CalendarQuarter
- QuarterDescription
- CalendarSemester
- MonthNumberOfYear
- MonthDescription
- CalendarQuarter

Name: [Date - Week]
- CalendarYear
- WeekNumberOfYear
- WeekDescription
- CalendarYear

Thanks for your help!

Hans

|||

Hi Hans,

Based on the info for the [Date] dimension above, maybe something like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(YTD([Date].[Date - Year].CurrentMember) *

YTD([Date].[Date - Week].CurrentMember),

[Measures].[Sales Volume KG])

|||

Hello Deepak,

Thanks a lot, this works. But please, can you tell me the background, why this crossjoin work? Is it, because if the current hierarchy is Weeks, the other hierarchy is "Null" and reverse?

Thanks
Hans

|||Hans, on the contrary, from what I can tell, when you select a week in [Date - Week], a year member is selected in [Date - Year], because the year attribute is shared between the 2 hierarchies. However, this works with YTD() calculations, because YTD(year member) returns that year member. You could confirm this by adding calculated measures to the query like [HierYearName]: [Date].[Date - Year].CurrentMember.Name and [HierWeekName]: [Date].[Date - Week].CurrentMember.Name.