Hi Mosha / Akhay / Deepak,

i m facing the following problem. can u guys plz help me out ?

In the following query i m just trying to apply standard deviation
function, if i apply a FORMAT_STRING property then i get -1.#IND in
some cell results. Why it is giving this result? and if i do not apply
any FORMAT_STRING property then it gives me -1 as a rsult in the same
cell ? Std Dev cannot be negative . whats the problem.

If i try to calculate the stdev for the same data in EXCEL its giving
me correct result ? whats is the problem with STDEV function ? and how
can i resolve this PROBELM ?


Thanks in advance,
Faraz


---------------------------------------------------------------------------*-------------------

-- QUERY START
---------------------------------------------------------------------------*-------------------



With MEMBER [Position].[-STDEV] AS
' IIF (IsNumeric(STDEV ({[Position].[Management Role].[Senior
Management],[Position].[Management Role].[Middle
Management],[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],[Position].[Management
Role].[Store Full Time Staf]},Measures.CurrentMember) ) AND ( STDEV
({[Position].[Management Role].[Senior
Management],[Position].[Management Role].[Middle
Management],[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],[Position].[Management
Role].[Store Full Time Staf]},Measures.CurrentMember) > 0 ),STDEV
({[Position].[Management Role].[Senior
Management],[Position].[Management Role].[Middle
Management],[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],[Position].[Management
Role].[Store Full Time Staf]},Measures.CurrentMember), 0 ) ',
SOLVE_ORDER = 10001
---------------------------------------------------
-- FORMAT STRING IS COMMMENTED
--, FORMAT_STRING= '###.#####'
----------------------------------------------------
SELECT {{[Employees].[CEO].[Sheri Nowmer].CHILDREN}} ON COLUMNS ,
{{[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],[Position].[Management

Role].[Store Management],[Position].[Management Role].[Store Temp
Staff],
[Position].[Management Role].[Store Full Time
Staf],[Position].[-STDEV]}} ON ROWS
FROM [HR]
WHERE ([Measures].[MeasuresLevel].[Count]) CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL]


---------------------------------------------------------------------------*-------------------

-- QUERY END
---------------------------------------------------------------------------*-------------------

Hi Faraz,

Please keep query text lines short, otherwise they get split in the
posting process, and it becomes difficult to reproduce results. Here's
the reconstructed query which, when I run it, returns no -1 values or
errors. So maybe this differs in some detail from the original:

'IIF(IsNumeric(STDEV({[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf]},
Measures.CurrentMember) )
AND ( STDEV({[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf]},
Measures.CurrentMember) > 0 ),
STDEV({[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf]},
Measures.CurrentMember), 0 ) ',
SOLVE_ORDER = 10001
---------------------------------------------------
-- FORMAT STRING IS COMMMENTED
, FORMAT_STRING= '###.#####'
----------------------------------------------------
SELECT
{{[Employees].[CEO].[Sheri Nowmer].CHILDREN}} ON COLUMNS ,
{{[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf],
[Position].[-STDEV]}} ON ROWS
FROM [HR]
WHERE ([Measures].[MeasuresLevel].[Count]) CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi deepak,
Thanks a lot for replying the question. my question was that why is
STDEV return -1 value ? Is there any problem in the query or the data
or any parameter i hav provided ?

Actually when i try to apply the STDEV on same data in Excel, it does
the correct result. How can i resolve this problem that OLAP will give
me the correct result ? or what is the reason of these in Correct
result bcoz Standard Deviation cannot be a negative value.

Thanj in Advance ,

Faraz

Faraz,

The problem is that when I run the query in the MDX Sample App, I don't
get any -1 values. Are you testing with the MDX Sample App, and in which
cell(s) are you seeing -1?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi deepak,

This is the query which returns -1 when i try to execute it. i m using
MDX sample Application.
I want to know that why it returns -1 bcoz standard deviation cannot be
a negative value.

How can i resolve this problem ? bcoz when i try to apply stddev
formula on the same data
in Excel sheet, it gives me positive result.

Thanks in advance,

Faraz

----------------------------------------------------------------------------
With MEMBER [Position].[-STDEV] AS
' STDEV (
{
[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf]},
Measures.CurrentMember ) ',
SOLVE_ORDER = 10001
---------------------------------------------------
-- FORMAT STRING IS COMMMENTED
--, FORMAT_STRING= '###.#####'
----------------------------------------------------
SELECT

{{[Employees].[CEO].[Sheri Nowmer].CHILDREN}}
ON COLUMNS ,
{{[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf],
[Position].[-STDEV]}}
ON ROWS

FROM [HR]
WHERE
([Measures].[MeasuresLevel].[Count])
CELL PROPERTIES [VALUE],[FORMATTED_VALUE],[CELL_ORDINAL]
---------------------------------------------------------------------------------------------------------------

Hi Faraz,

This time I was able to see -1 in a cell; but if you double-click it,
the VALUE = -1.#IND. So it isn't really -1.

Here's what I think is happening: STDEV() computes the unbiased standard
deviation, so the denominator is N-1. Thus, the number of (non-null)
samples N should be > 1, otherwise strange results are returned. Now,
should the function STDEV() itself check for this condition - that's a
question for [MS] folks like Mosha. But you can incorporate a test like:

Set [STDSET] as
'{[Position].[Management Role].[Senior Management],
[Position].[Management Role].[Middle Management],
[Position].[Management Role].[Store Management],
[Position].[Management Role].[Store Temp Staff],
[Position].[Management Role].[Store Full Time Staf]}'
MEMBER [Position].[-STDEV] AS
' iif(Filter([STDSET], Not IsEmpty([Measures].CurrentMember)).Count > 1,
STDEV ( [STDSET]), NULL) ',
SOLVE_ORDER = 10001, FORMAT_STRING = '###0.0###'
SELECT
{{[Employees].[CEO].[Sheri Nowmer].CHILDREN}}
ON COLUMNS ,
{[STDSET],
[Position].[-STDEV]}
ON ROWS
FROM [HR]
WHERE
([Measures].[MeasuresLevel].[Count])
CELL PROPERTIES [VALUE],[FORMATTED_VALUE],[CELL_ORDINAL]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Once again thanks for the prompt reply. The following are the results i
m getting from the above query if i comment the FORMAT_STRING property.
It gives me -1 and with format_string property it givrs me -1.#IND.

My question is that why it is showing inconsistent behaviour ? bcoz we
have empty values in all the columns.
if N-1 is the problem then it shoud only show -1 or -1.#IND in the
second last column (Donna). Why it is giving -1 or -1.#IND
in the Maya and Wheply columns ?

how can i notify Mosha about this porblem ? Mosha, ur comment will be
required on the situation. n thanks for ir effort Deepak


----------------------------------------------------------------------------------------------------------------------------------------------------------
Q U E R Y R E S U L T S
----------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Senior Management | 576 | 576 | 288 |
2,304 | 288 | | 576
Middle Management | 864 | 1,728 | | |
-STDEV | 204 | 815 | -1 |
-1 | 7,990 | -1 | 2,036
-------------------------------------------------------------------------------------------------------------------------------------------------------------

just trying to correct the display the results
----------------------------------------------------------------------------------------------------------------------------------------------------------
Q U E R Y R E S U L T S
----------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Senior Mgmt| 576 | 576 | 288 | 2,304
1,440 | 3,456
Store Mgmt | | | 10,944 | 31,104 | 2,304 |
-STDEV | 204 | 815 | -1 | -1 |
7,990 | -1 | 2,036
-------------------------------------------------------------------------------------------------------------------------------------------------------------

just trying to correct the display the results
----------------------------------------------------------------------------------------------------------------------------------------------------------
Q U E R Y R E S U L T S
----------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Senior Mgmt| 576 | 576 | 288 | 2,304
1,440 | 3,456
Store Mgmt | | | 10,944 | 31,104 | 2,304 |
-STDEV | 204 | 815 | -1 | -1 |
7,990 | -1 | 2,036
-------------------------------------------------------------------------------------------------------------------------------------------------------------

hi Deepak,


Your response is required here,

Thanks,
Faraz