Constants to Sets

Hi,

I guess that's an easy one... I'm quite sure that my brain is gone for weekend... And I'm still getting used to MDX...

I'm using the min function to find out the minimum of two measures, so basically:

min({[Measures].[MeasureA],[Measures].[MeasureB]})

This works perfectly. But what I need is to have something like that:

min({[Measures].[MeasureA],[Measures].[MeasureB],0})

So that when A and B are both greater than 0 the result is 0. OK, I can do that with iif but that's not nice... There must be a way to add a constant to a set... I even need something like that:

min ({sum(...),sum(...),0})

I guess it's the same problem and the same solution...

Any help is very appreciated...

is this what you mean?

func(1,2) -> 0

func(-1,2) -> -1

func(-1,-2) -> -2

its not much prettier than iif -

Min(Min(0, a), Min(0, b))

|||

Well, you understood what I'm looking for... However I'm quite sure that this doesn't work...

The MDX min function expects a set. What you provide is value (as far as I know normally called an expression), that doesn't seam to work at all...

|||

sorry. . mdx is not my forte. . .

from the help -

min(Set-Expression, [Numeric-Expression]) evaluates Numeric over Set

can't you make a set from two constants?

Evalute Min Zero over a, And Min Zero over b,

'set' results and min -

Min( { Min({a},0), Min({b},0) } )

|||

ught. . Im not thinking. . .

isnt it

Min({a,b},0)

|||That was my first try... Doesn't work... The result is always 0, no idea why...|||

Hi Thomas,

How about using a calculated measure for the constant lower limit, like in this Adventure Works example:

>>

With Member [Measures].[LowLimitOrder] as 2500

Member [Measures].[MinOrderCount] as

Min({[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[LowLimitOrder]})

select {[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[MinOrderCount]} on 0,

[Product].[Product Categories].[Category].Members on 1

from [Adventure Works]

Internet Order Count Reseller Order Count MinOrderCount
Accessories 18,208 1,315 1315
Bikes 15,205 3,153 2500
Clothing 7,461 2,410 2410
Components (null) 2,646 2500

>>

|||

Deepak,

once again... THANKS for your superb support! Works like a charme...