MDX for 2 sets of dates

Question

Hi

I have the following MDX code that will bring back the amount for the customer for that day, the average amount for all the customers for that day and the standard deviation for all the customers that day

WITH 
  SET cust as NONEMPTY( [Customer].[Customer].[Customer].Members,measures.[Internet Sales Amount])
  MEMBER [measures].[total] AS 
    [Measures].[Internet Sales Amount] + [Measures].[Internet Tax Amount] 
  MEMBER [Measures].[Avg Total1] AS 
    measures.[total]/
count( cust )
  MEMBER [measures].[standard deviation1] AS 
    StdDev
    (
      cust
     ,[measures].[total]
    )
  MEMBER [Measures].[Avg Total] AS 
    IIF(ISEMPTY([measures].[total]),null,(measures.[Avg total1],[Customer].[Customer].[All Customers]))
  MEMBER [measures].[standard deviation] AS 
    IIF(ISEMPTY([measures].[total]),null,(measures.[standard deviation1],[Customer].[Customer].[All Customers]))
  MEMBER [Measures].[StatusValue] AS 
    IIF(ISEMPTY([measures].[total]),null,CASE 
      WHEN 
          [Measures].[Total]
        > 
          [Measures].[Avg Total] + [measures].[standard deviation] 
      THEN 'Good'
      WHEN 
          [Measures].[Total]
        < 
          [Measures].[Avg Total] - [measures].[standard deviation] 
      THEN 'Low'
      ELSE 'Normal'
    END) 
SELECT NON EMPTY
  {
   
   [measures].[total]
   ,[Measures].[Avg Total]
   ,[measures].[standard deviation]
   ,[Measures].[StatusValue]
  } ON COLUMNS
 ,NON EMPTY 
    [Customer].[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE
[Date].[Date].&[20071105];

What I am looking to do is to keep the total amount measure for the specified day in this case

[Date].[Date].&[20071105]

, but I would like the average and standard deviation measures to be over a date range say

[Date].[Date].&[20071016]:[Date].[Date].&[20071105]

How would I go about this?

Answer 1

Assuming you use the same query with date ranges in the WHERE clause.

As [Date].[Date].&[20071105] is in the scope of the where clause it can be referenced on the other axis

Try the following:

WITH 
  SET cust as NONEMPTY( [Customer].[Customer].[Customer].Members,measures.[Internet Sales Amount])
  MEMBER [measures].[total] AS 
    [Measures].[Internet Sales Amount] + [Measures].[Internet Tax Amount] 
MEMBER [measures].[alternate date total] AS 
   ([Date].[Date].&[20071105],measures.[total])
  MEMBER [Measures].[Avg Total1] AS 
    measures.[total]/
count( cust )
  MEMBER [measures].[standard deviation1] AS 
    StdDev
    (
      cust
     ,[measures].[total]
    )
  MEMBER [Measures].[Avg Total] AS 
    IIF(ISEMPTY([measures].[total]),null,(measures.[Avg total1],[Customer].[Customer].[All Customers]))
  MEMBER [measures].[standard deviation] AS 
    IIF(ISEMPTY([measures].[total]),null,(measures.[standard deviation1],[Customer].[Customer].[All Customers]))
  MEMBER [Measures].[StatusValue] AS 
    IIF(ISEMPTY([measures].[total]),null,CASE 
      WHEN 
          [Measures].[Total]
        > 
          [Measures].[Avg Total] + [measures].[standard deviation] 
      THEN 'Good'
      WHEN 
          [Measures].[Total]
        < 
          [Measures].[Avg Total] - [measures].[standard deviation] 
      THEN 'Low'
      ELSE 'Normal'
    END) 
SELECT NON EMPTY
  {
   
   [measures].[alternate date total]
   ,[Measures].[Avg Total]
   ,[measures].[standard deviation]
   ,[Measures].[StatusValue]
  } ON COLUMNS
 ,NON EMPTY 
    [Customer].[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE
{[Date].[Date].&[20071016]:[Date].[Date].&[20071105]};

In case you want the row set to be limited only by existing facts on that particular day, replace the row axis by the following:

 ,NON EMPTY 
    exists([Customer].[Customer].[Customer].Members,[Date].[Date].&[20071105],'Internet Sales') ON ROWS

Philip,

Answer 2

Thanks Phillip that worked great