May 16, 2009 at 2:29 am
Hello,
i build a query to get YTD-Values:
WITH
MEMBER [Measures].[Gewinn v. St. YTD] as
SUM ( periodsToDate([Dim Zeit].[Jahr Monat].currentmember.parent.level,
[Dim Zeit].[Jahr Monat].currentmember)
, [Measures].[Gewinn v. St.]
)
select
[Dim Zeit].[Jahr Monat].members
ON ROWS,
{ [Measures].[Gewinn v. St. YTD]
, [Measures].[Gewinn v. St.]
}
ON COLUMNS
from ..
The problem is, if i select more than one year, the ytd should break at every new year, but with my query it is running through the years.
Can you help me?
To
May 16, 2009 at 7:15 am
I translated your MDX to English and I am wondering is the hierarchy you have defined in the Time dimension called 'Year month'? Just trying to determine how to modify your MDX so you can see the modification correctly. Anyway, I will do my best without knowing all of the information about your time dimension (I am assuming you have a hierarchy defined in the Time dimension that has some sort of Year-->Quarter-->Month-->Date type of relationship setup or maybe you just have Year-->Month).
WITH
MEMBER [Measures].[Gewinn v. St. YTD] as
SUM ( periodsToDate([Dim Zeit].[Jahr Monat].[Jahr],
[Dim Zeit].[Jahr Monat].currentmember)
, [Measures].[Gewinn v. St.]
)
select
[Dim Zeit].[Jahr Monat].[Jahr].members
ON ROWS,
{ [Measures].[Gewinn v. St. YTD]
, [Measures].[Gewinn v. St.]
}
ON COLUMNS
from ..
If you want to reference an example from the Adventure Works SSAS solution this would be how you would set this up.
WITH
MEMBER [Measures].[Reseller Sales Amount YTD] as
SUM ( periodsToDate([Date].[Calendar].[Calendar Year],
[Date].[Calendar].currentmember)
, [Measures].[Reseller Sales Amount]
)
select
[Date].[Calendar].[Calendar Year].members
ON ROWS,
{ [Measures].[Reseller Sales Amount YTD]
, [Measures].[Reseller Sales Amount]
}
ON COLUMNS
from [Adventure Works]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
May 16, 2009 at 11:49 am
I don't see the difference to my query in spite of the translation..
What I want is:
Period YTD
..
200811 110
200812 120
200901 10 <-- accumulation starts an january
200902 20
...
What I get is:
..
200811 110
200812 120
200901 130
200902 140
...
May 16, 2009 at 12:15 pm
You will need to provide more information as to how your dimension is setup. Do you have a hierarchy defined with multiple levels and attribute relationships defined properly that would allow you to role this information up. It doesn't appear that you do. From looking at it you are looking at a single attribute for Year/Month and you don't have a Year and then a seperate Month attribute with the relationship defined rolls this information up.
Do you have the type property of the dimension setup with time and have you associated the attributes with the appropriate levels of the time as in Year, Quarter, Month, Day, etc.?
This is fairly straight forward as long as you have a time dimension properly configured and a hierarchy defined with proper relationships. You might want to download the Adventure Works database sample so that you can get an idea of what you need to do.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
May 19, 2009 at 3:49 pm
Sorry for the delay..
I have installed the Adventure Works DW Example and tried to get the YTD-Values there:
WITH
MEMBER [Measures].[Amount YTD] as
SUM ( periodsToDate(
[Dim Time].[CalendarYear - CalendarSemester - CalendarQuarter].[CalendarYear],
[Dim Time].[CalendarYear - CalendarSemester - CalendarQuarter].[CalendarQuarter].currentmember
)
, [Measures].[Amount]
)
select
[Dim Time].[CalendarYear - CalendarSemester - CalendarQuarter].[CalendarQuarter].members
ON ROWS,
{
[Measures].[Amount]
, [Measures].[Amount YTD]
}
ON COLUMNS
from
[Adventure Works DW]
the result is #Error for the YTD column. Do you know the sample?
May 19, 2009 at 7:12 pm
You need to make a slight modification to your YTD calculation and remove the calendar quarter member reference in the second portion of the PeriodsToDate function (just reference the hierarchy with the CurrentMember.
WITH
MEMBER [Measures].[Reseller Sales Amount YTD] as
SUM ( periodsToDate(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].currentmember
)
, [Measures].[Reseller Sales Amount]
)
select
[Date].[Calendar].[Calendar Quarter].members
ON ROWS,
{
[Measures].[Reseller Sales Amount]
, [Measures].[Reseller Sales Amount YTD]
}
ON COLUMNS
from
[Adventure Works]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
May 20, 2009 at 12:47 am
Great, you found my mistake. Now the query works like I expected.
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply