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.

Question regarding calculated fields in reporting services 2005

Is there a concept of scope for calculated fields? I tried the
following and VS crashed.
=IIf(RowNumber("GroupName") = 1, MyValue, 0)
Example of what I am trying to accomplish.
GroupID, MyValue
1, 5
1, 5
1, 5
2, 10
3, 20
3, 20
========== Average MyValue should be (5 + 10 + 20)/3...not (5+5+5+10+20+20)/5.
MyValue would be the same for each GroupID. Does this make sense?Hi,
Infact you can use "Avg()" itself it has a scope parameter.
Amarnath
"Nergock@.gmail.com" wrote:
> Is there a concept of scope for calculated fields? I tried the
> following and VS crashed.
> =IIf(RowNumber("GroupName") = 1, MyValue, 0)
> Example of what I am trying to accomplish.
> GroupID, MyValue
> 1, 5
> 1, 5
> 1, 5
> 2, 10
> 3, 20
> 3, 20
> ==========> Average MyValue should be (5 + 10 + 20)/3...not (5+5+5+10+20+20)/5.
> MyValue would be the same for each GroupID. Does this make sense?
>

Question on MDX

I am trying to write a calculated field. I need to calculate the Sum of a Item Sold over a period of time where 2 measures called BV value and Unit Price are zero. Can someone suggest what functions I should use to get this done

Thanks

Ann

Hello Ann,

Did you mean to use a calculated member? If yes, please, try the following:

WITH MEMBER [Measures].SumOfSoldItems AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[BV Value] = 0 AND [Measures].[Unit Price] = 0 ) , [Measures].[Item Sold] ) '
SELECT [Measures].SumOfSoldItems on 0
FROM [Adventure Works]

In the first argument of the filter function you should put a set representing the period of time that you are interested in.

I hope this helps

Greg

|||

Hi Greg

Thanks for your post. Thats exactly what I am trying to achieve. I tried using the above method, but it gives me a syntax error . It says, Syntax for "WITH" is incorrect.

Thanks

Ann

|||

Could you tell me what client application you are using? I wrote 2 more MDX queries for you so you can use them as a small tutorial. They both work with Adventure Works.

1. This one doesn't use calculated member but should give you the right results. After you try it, you can change the query to work with your own cube. Change the time dimension members ( [Date].[Fiscal Year].members ) to a set representing the time period you need. The two other measures in the filter you can change to [Measures].[BV value]=0 and [Measures].[Unit Price] = 0. The measure in the WHERE clause you may want to change to [Measures].[Item Sold]

SELECT filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 3500000 AND [Measures].[Order Quantity] > 90000 ) on 0
FROM [Adventure Works]
WHERE [Measures].[Sales Amount]

2. This is the query that I showed you yesterday but I changed it back slightly so now it works with Adventure Works. Try it first against AW and then change the time period and measures for your own needs as I explained in the first paragraph:

WITH MEMBER [Measures].x AS 'sum ( filter ( [Date].[Fiscal Year].members, [Measures].[Amount] > 0 AND [Measures].[Order Quantity] > 0 ) , [Measures].[Sales Amount] ) '
SELECT [Measures].x on 0
FROM [Adventure Works]

I hope this works for you. If it doesn't or you have more questions, feel free to ask :)

- Greg

Question on calculated members

Hi, all experts,

As we know the calculated members are only calculated at query time which means the analysis services engine wont preaggregate any thing for calculated members across different levels of dimensions? I am a bit confused then what is the point to have a calculated member in a cube especially if the cube is a large cube with over millions records of data? In this case, the query including any calculated members against the cube with millions of records will be extremly slow?

I am looking forward to hearing from you shortly for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

You are right that calculated members are evaluated at query time. While a cube may contain millions of records of data, a given query may be answered by using a small portion of the data. Therefore, a calculated member may be calculated pretty fast if the query space is much smaller than the entire cube.|||

Dear Helen,

You are right, but you could use named calculations in the datasourceview at the each row nivel... you dont agregations and is better for performnace.

Calculated members is important to use in the cube, aggregations nivel...

Regards!

|||

Hi, PedroCGD,

Thank you for your advices. So as you are saying, the analysis services engine will be able to locate the proportion of the cube easily whenever the MDX query is launched against the cube?

Thank you.

Best regards,

|||Avoid calculated members if you could create named calculations in datasource view. The named calculation will be a measure like other measure you select from your factTable. But of course, that if you need calculation inside aggregation, I must use calculated members...|||

Hi, PedroCGD,

Thanks a lot for your kind advices. It's been very helpfu.

With best regards,

Yours sincerely,

|||

It's a pleasure Helen.

If your answer is responded check the post as resolved, or if not tell me how can I help!

Thanks and regards!

Question on calculated members

Hi, all experts,

As we know the calculated members are only calculated at query time which means the analysis services engine wont preaggregate any thing for calculated members across different levels of dimensions? I am a bit confused then what is the point to have a calculated member in a cube especially if the cube is a large cube with over millions records of data? In this case, the query including any calculated members against the cube with millions of records will be extremly slow?

I am looking forward to hearing from you shortly for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

You are right that calculated members are evaluated at query time. While a cube may contain millions of records of data, a given query may be answered by using a small portion of the data. Therefore, a calculated member may be calculated pretty fast if the query space is much smaller than the entire cube.|||

Dear Helen,

You are right, but you could use named calculations in the datasourceview at the each row nivel... you dont agregations and is better for performnace.

Calculated members is important to use in the cube, aggregations nivel...

Regards!

|||

Hi, PedroCGD,

Thank you for your advices. So as you are saying, the analysis services engine will be able to locate the proportion of the cube easily whenever the MDX query is launched against the cube?

Thank you.

Best regards,

|||Avoid calculated members if you could create named calculations in datasource view. The named calculation will be a measure like other measure you select from your factTable. But of course, that if you need calculation inside aggregation, I must use calculated members...|||

Hi, PedroCGD,

Thanks a lot for your kind advices. It's been very helpfu.

With best regards,

Yours sincerely,

|||

It's a pleasure Helen.

If your answer is responded check the post as resolved, or if not tell me how can I help!

Thanks and regards!