I'm having trouble understanding the results I am getting from a query. The goal is to get the sum of a measure from the beginning of time (at the month level) through the current month.
Here is my query:
WITH
MEMBER [Date].[Fiscal].[ThruNow] AS
AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))
MEMBER Temp AS ([Date].[Fiscal].[ThruNow], [OO Units])
MEMBER Temp2 AS SUM([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])
SELECT
{ Temp, Temp2 } ON 0
,{ [Product].[Products].[Division].&[R-B-D].Children } ON 1
FROM [Merchandising]
WHERE ([Date].[Fiscal].[Fiscal Week].&[2007 16])
The Temp2 member is giving me the correct results. However the Temp member gives me the sum of the measure across all time. Can anyone explain this to me?
MEMBER [Date].[Fiscal].[ThruNow] AS
AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))
Since [ThruNow] is defined on the [Date].[Fiscal] hierarchy, [Date].[Fiscal].CurrentMember is [Date].[Fiscal].[ThruNow] when the latter is computed, which may not be what you intended. On the other hand, this may work fine:
MEMBER Temp AS AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])
