Two problems with calculated measures and members in a particular MDX query.
The results of the query represent the performance of a set of Australia/New
Zealand retail outlets at a given week in the quarter, with a summary line
for the whole region.

Here are the columns -

1. The actual sales up to the given week, for a particular outlet
2. The targeted sales up to the given week, for a particular outlet
3. The % the Actual to Date is of the Target to Date
4. The total Target for the Quarter
5. The Projected actual sales for the whole quarter (% Target to Date *
total Target for the Quarter)
6. The % the Actual to Date is over the actual for the same Qtr last year
7. The % the Actual to Date is over the actual for the previous Qtr this
year

Here is a sample of the results:

ACT TRGT %TRGT QTR QTR %SAME QTR %PREV
TD TD TD TRGT PROJ LAST YEAR QTR
AUK 126 123 102% 480
-80.1% -65.5%
WEL 277 220 126% 890
-54.3% -99.3%
MLB 85 47 181% 324 586 23.1%
78.1%
PER 304 267 114% 915 1042 50.6%
57.5%
SYD 26 24 108% 115
-30.9% -89.7%
ANZ 818 681 120%
-1.#IND -80.4% -99.5%

FIRST PROBLEM
--------------------
I get an '-1.#IND' error in the Projected column for the region.

I think the problem is down to the fact that there is an outlet that is no
longer operating (Adelaide- ADL) which is a member of the dimension but has
no fact table records in this period. I think this because this report is
also run for a whole lot of other outlets and regions around the world and
only the regions with inactive outlets get this error. The outlets and the
region come from a dimension called Outlets with levels Region and Outlet.
The Outlet level has a property called Active which is false for ADL but I
don't know how to use it to stop this error occuring.

SECOND PROBLEM
-----------------------
The %SAME QTR LAST YEAR and %PREV QTR columns give the wrong percentages.
Something is wrong with the formula.

Any ideas ???

Here is the MDX query:
------------------------

With

Member [Measures].[QTD] as
'Sum( PeriodsToDate( [StatDate].[Quarter], StatDate.CurrentMember),
[Measures].[Value] ) ' , solve_order = 1

Member [Measures].[Same Qtr Last Year] as
' Sum( PeriodsToDate( [StatDate].[Quarter], ParallelPeriod(
[StatDate].[Year] , 1, [StatDate].CurrentMember ) ) ,
[Measures].[Value] ) ' , solve_order = 1

Member [Measures].[% Over Same Qtr Last Year] as
' ( [Measures].[Value] - [Measures].[Same Qtr Last Year] ) /
[Measures].[Same Qtr Last Year] ' , format = '0.0%' , solve_order = 2

Member [Measures].[Last Qtr] as
' Sum( PeriodsToDate( [StatDate].[Quarter], ParallelPeriod(
[StatDate].[Quarter] , 1, [StatDate].CurrentMember ) ) ,
[Measures].[Value] ) ' , solve_order = 1

Member [Measures].[% Over Last Qtr] as
' ( [Measures].[Value] - [Measures].[Last Qtr] ) / [Measures].[Last Qtr]
' , format = '0.0%' , solve_order = 2

Member [Statistic].[Sales].[Net Act] as
' [Statistic].[Sales].[Sold] - [Statistic].[Sales].[Returned] + 0 ' ,
solve_order = 2 , format = '0;-0;0;0'

Member [Statistic].[Sales].[Net TRGT] as
' [Statistic].[Sales].[Targeted] + 0 ' , solve_order = 2 ,
format = '0;-0;0;0'

Member [Statistic].[Sales].[%TRGT] as
' PosNegDivision ( [Statistic].[Sales].[Net Act] ,
[Statistic].[Sales].[Net TRGT] , "0.0%" ) ' , solve_order = 3 ,
format = '0%'

Member [Statistic].[Sales].[Net Proj] as
'Sum(Descendants([Outlets],CurrentMember,,LEAVES),
(StatDate.[2004].[1], [Statistic].[Sales].[Net TRGT], [Measures].[Value]) *
(StatDate.[2004].[1].[5], [Statistic].[Sales].[Net Act] , [Measures].[QTD])
/
(StatDate.[2004].[1].[5], [Statistic].[Sales].[Net TRGT],
[Measures].[QTD]))',
solve_order = 4, format = '0'

Select
{
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Act]
,[Measures].[QTD] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Trgt]
,[Measures].[QTD] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[%Trgt]
,[Measures].[QTD] ) ,
( StatDate.[2004].[1] , [Statistic].[Sales].[Net Trgt]
,[Measures].[Value] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Proj]
,[Measures].[Value] ),
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Act] , [Measures].[%
Over Same Qtr Last Year] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Act] , [Measures].[%
Over Last Qtr] )
}
ON COLUMNS ,

{
Hierarchize( [Outlets].Members , POST )
}
ON ROWS

FROM Stats

Reposting cleaned up results table ...

ACT TRGT %TRGT QTR QTR %SAME QTR %PREV
TD TD TD TRGT PROJ LAST YEAR
QTR
AUK 126 123 102% 480
-80.1% -65.5%
WEL 277 220 126% 890
-54.3% -99.3%
MLB 85 47 181% 324 586 23.1%
78.1%
PER 304 267 114% 915 1042 50.6%
57.5%
SYD 26 24 108% 115
-30.9% -89.7%
ANZ 818 681 120%
-1.#IND -80.4% -99.5%