Calculated Member (beginner)

Hi,

I'm building a calculated member which should only calculate the measures in the calculated member if specific measure is > 0 (otherwise the value should be Null)

For example:

If physicalStock > 0

[Measures].[Netweight] * [Measures].[Physical]

How do I build this in the calculated member...?

(Using Analysis2000)

Thanks in advance

Haakon

With AS2000 Mosha have a blog post of this subject here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/30/mdx_is_empty.aspx

HTH

Thomas Ivarsson

|||

Here is what I am trying....

Iif([Measures].[Physical] > 0), [Measures].[Netweight] / [Measures].[amount

_count] * [Measures].[Physical]

Which don't work....any ideas - what I should do...?

Thanks

|||

IIF(([Measures].[Physical] > 0, [Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

HTH

Thomas Ivarsson

|||

Thanks for your input - but it still seems to be causing problems:

I am getting the following error in the calculated member builder:

"Unable to update the calculated member. Formula error - Syntax error - token is not valid"

I am using the following suggested value expression:

Iif(([Measures].[Physical] > 0,[Measures].[Netweight] / [Measures].[amount_count] * [Measures].[Physical]),Null)

Your suggestions are appriciated,

Thanks,

|||

It is my error. Try this:

IIF([Measures].[Physical] > 0,[Measures].[Netweight] / ([Measures].[amount_count] * [Measures].[Physical]),Null)

Copy and paste this example above.

Regards

Thomas Ivarsson

sql