October 18, 2010 at 8:23 am
hi All,
Just wondered if you had any thoughts on why the YTD function is not working
whereas using the equivalant periods to date function does
I have created a simple star schema on Fact Internet Sales in Adventure
works and created my own calendar hierachy
Calendar Hiearchy ->Calendar Year->Month Year-> Day Month Year
CAlendar Year type is years e.g 2002
Month Year type is months e.g Jul 2002
Day Month Year type is days e.g 01 Jul 2002
mdx
// Year To Date - using PERIODS TO DATE on year month level
WITH MEMBER [Measures].[YearToDate] AS 'SUM(PERIODSTODATE([Order Date].[CalendarHierarchy].[Calendar Year]),
[Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Measures].[YearToDate]} ON 0,
[Order Date].[CalendarHierarchy].[Year Month Desc] ON 1
FROM Sales
gives :
Year MonthSales AmountYearToDate
Jul 2001 473388.163473388.163
Aug 2001 506191.691200001 979579.854200001
Sep 2001 473943.031200001 1453522.8854
Oct 2001 513329.474000001 1966852.3594
Nov 2001 543993.405800001 2510845.7652
// Year To Date - using PERIODS TO DATE at day level
WITH MEMBER [Measures].[YearToDate] AS 'SUM(PERIODSTODATE([Order Date].[CalendarHierarchy].[Calendar Year]),
[Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Measures].[YearToDate]} ON 0,
[Order Date].[CalendarHierarchy].[Year Month Day Desc] ON 1
FROM Sales
gives :
Day Mth YearSales AmountYearToDate
01 Jul 200114477.338214477.3382
02 Jul 200113931.52 28408.8582
03 Jul 200115012.178243421.0364
04 Jul 20017156.5450577.5764
05 Jul 200115012.178265589.7546
06 Jul 200114313.08 79902.8346
07 Jul 20017855.638287758.4728
08 Jul 20017855.638295614.111
09 Jul 200120909.78 116523.891
10 Jul 200110556.53 127080.421
11 Jul 200114313.08 141393.501
However using the YTD which is a shortcut which defaults to periods to years gives nulls
i.e.
// Year To Date - using YTD on month level
WITH MEMBER [Measures].[YearToDate] AS 'SUM(YTD(), [Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Measures].[YearToDate]} ON 0,
[Order Date].[CalendarHierarchy].[Year Month Desc] ON 1
FROM Sales
gives
Month YearSales AmountYearToDate
Jul 2001 473388.163(null)
Aug 2001 506191. 691200001 (null)
Sep 2001 473943.031200001 (null)
Oct 2001 513329.474000001 (null)
Nov 2001 543993.405800001 (null)
Dec 2001 755527.891400002 (null)
// Year To Date - using YTD at day level
WITH MEMBER [Measures].[YearToDate] AS 'SUM(YTD(), [Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Measures].[YearToDate]} ON 0,
[Order Date].[CalendarHierarchy].[Year Month Day Desc] ON 1
FROM Sales
gives
DAy Mth YearSales AmountYearToDate
01 Jul 200114477.3382(null)
02 Jul 200113931.52 (null)
03 Jul 200115012.1782(null)
04 Jul 20017156.54 (null)
05 Jul 200115012.1782(null)
06 Jul 200114313.08 (null)
07 Jul 20017855.6382(null)
08 Jul 20017855.6382(null)
October 21, 2010 at 3:12 pm
Is your date dimension setup with the Type on column properties mapped to Year/Month/Day, etc?
October 23, 2010 at 12:08 pm
hi Naveen,
[Calendar Years] is set to type "Years", YearMonths type is set to "Months" and [Year Month Days] is set to "Days" and the Time type was set to "regular" but I did change it to "time" when regular did not work. Is this what you mean ?
October 23, 2010 at 1:27 pm
Now you can use the following MDX and pass the CurrentMember reference to calculate YTD
WITH MEMBER [Measures].[YearToDate] AS 'SUM(YTD([Order Date].[CalendarHierarchy].CurrentMember), [Measures].[Sales Amount])'
SELECT {[Measures].[Sales Amount], [Measures].[YearToDate]} ON 0,
[Order Date].[CalendarHierarchy].[Year Month Day Desc] ON 1
FROM Sales
October 27, 2010 at 8:49 am
hi Naveen
Yeah that works...thx you very much for yr assistance..much appreciated
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply