Either a MDX statement or a IIF statement required 1

[eo]

Hi,

I have MDX which calculates a "Loss Ratio". But the divider is often null, in fact, all three components could be null, which then deliveres a "-1.#IND" result. I would prefer a NULL result rather. Can someone show me how to amed the MDX using either CASE or IIF (or anything else more appropriate) to do this?

CREATE MEMBER CURRENTCUBE.[MEASURES].[ClmGrsIncLR%]
 AS 100*([Measures].[ClmGrsIncIndem]+[Measures].[ClmGrsIncFee])/[Measures].[PrmGGAcc], 
FORMAT_STRING = "0.0", 
VISIBLE = 1;

EO
Hertfordshire, England

 

[MDXer]

try this

CREATE MEMBER CURRENTCUBE.[MEASURES].[ClmGrsIncLR%]
 AS 
IIF(
	IsEmpty([Measures].[PrmGGAcc]),
	Null,
	100*([Measures].[ClmGrsIncIndem]+[Measures].[ClmGrsIncFee])/[Measures].[PrmGGAcc]
), 
FORMAT_STRING = "0.0", 
VISIBLE = 1;

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[eo]

The NULL still gave the same result, but replacing that with =0 gave me what I want...I read somewhere that =0 is like NULL in MDX??

CREATE MEMBER CURRENTCUBE.[MEASURES].[ClmGrsIncLR%]
 AS 
IIF(
    [Measures].[PrmGGAcc]=0,
    Null,
    100*([Measures].[ClmGrsIncIndem]+[Measures].[ClmGrsIncFee])/[Measures].[PrmGGAcc]
), 
FORMAT_STRING = "0.0", 
VISIBLE = 1;

Thanks for this!!

EO
Hertfordshire, England

 

[eo]

Sorry, it was not the NULL, but rather the IsEmpty

EO
Hertfordshire, England

 

[MDXer]

Null and 0 get treated seperately I always make it a practice of doing a double check

IIF(Isempty(val),0,IIF(val = 0, Val+val,0))

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck