Wednesday, March 7, 2012

Different Results using Aggregate and Sum

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])

No comments:

Post a Comment