MDX Multiselect and existing keyword usage

WITH

MEMBER LastYearSales
as
'
sum(
existing [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].MEMBERS,
(ParallelPeriod([Time].[Fiscal Calendar Hierarchy].[Fiscal Year]),
[TY Net Sales Amount])
)
'

MEMBER [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total]

AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101],

[Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010103], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010104] }, [Time].[Fiscal Calendar Hierarchy] ) }

}, { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] } ) )', SOLVE_ORDER = 1000

SELECT { LastYearSales } ON COLUMNS,

{ { [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010101], [Time].[Fiscal Calendar Hierarchy].[Fiscal

Week].&[2006010102], [Time].[Fiscal Calendar Hierarchy].[Fiscal Week].&[2006010103], [Time].[Fiscal Calendar

Hierarchy].[Fiscal Week].&[2006010104] }, ( [Time].[Fiscal Calendar Hierarchy].[All Fiscal Periods].[ Grand Total] ) } ON

ROWS

FROM [Sales]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

========

OUTPUT

LastYearSales
Week 1 100
Week 2 200
Week 3 300
Week 4 400
Grand Total 50000 -> This is not the sum of the four weeks which should be 900. Instead it is sum across all weeks across all years in the time dimension. So effectively, the Existing statement is not getting the correct context (which is

the set of four fiscal weeks) while processing the aggregate (grand total) row. The only part of the code above that we can change is the definition of the calculated measure LastYearSales. The remaining is generated by proclarity.

We followed the recommendations by Mosha in his blog,
http://sqljunkies.com/WebLog/mosha/archive/2007/01/13/multiselect_parallelperiod.aspx

There is a problem with how Proclarity generates MDX in this case. (In fact I just reviewed very similar scenario this week). This is actually not multiselect scenario for which I wrote blog, but Visual Totals scenario, which is harder. The calculated member with Aggregate is not in the WHERE clause, therefore context for current coordinate is not set, and EXISTING in the definition of calculated member does nothing. Sorry, but the solution for multiselect won't work for Visual Totals.