MDX for 2 sets of dates

# MDX for 2 sets of dates

• Thursday, June 28, 2012 7:47 PM

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

### All Replies

• Thursday, June 28, 2012 11:03 PM

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
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,

• Edited by Thursday, June 28, 2012 11:04 PM
• Edited by Thursday, June 28, 2012 11:04 PM
• Edited by Thursday, June 28, 2012 11:08 PM
• Marked As Answer by Friday, June 29, 2012 7:56 PM
•
• Friday, June 29, 2012 7:56 PM

Thanks Phillip that worked great