Syntax for Min in Designer

building semantic layer
1

Does anyone know how to use min function in designer (Syntax ) to pick up min of two values in BO Designer ?

thanks,

ashwin (BOB member since 2010-04-26)

2 
Min(table.column)

What RDBMS are you on?
Also, is it two values from the same column (if so, see above), or do you mean the min of column1 and column2? If it’s the latter, try:

CASE WHEN table.col1 < table.col2 then table.col1 else col2 END
3

Thanks Mark for the reply. It’s Oracle DB. I’m trying to get the min of two measures as they are calculated as well.

Is the below possible or any other workaround ? Coz it says Invalid definition.

Ex:

CASE WHEN @Select(class1/object1 < @Select(class1/object2 then @Select(class1/object1 else @Select(class1/object2 END

thanks,

ashwin (BOB member since 2010-04-26)

4

In oracle, you are looking for the “LEAST” function, but keep in mind that a null will win, so you should use NVL to account for each value potentially being blank:


LEAST( nvl(col1,col2), nvl(col2,col1) )

digpen :us: (BOB member since 2002-08-15)

5

In addition to what digpen has said, your syntax is wrong. It should be:

CASE WHEN  @Select(class1/object1) < @Select(class1/object2) then @Select(class1/object1)  else @Select(class1/object2) END

Cheers,
Mark

6

Thanks Guyz, it worked using the CASE statement.

Also can you please help me with the following logic in Designer.

MIN(ABS(M6),ABS(SUM(G6,J6,L6,AG6)-SUM(AC6,AB6,AA6)))

where M6, G6,J6, L6,AG6… are columns.

thanks,

ashwin (BOB member since 2010-04-26)

7

What have you tried so far?

I’d suggest something like:

CASE WHEN ABS(M6) < ABS(G6+J6+L6+AG6-AC6-AB6-AA6) THEN ABS(M6) ELSE ABS(G6+J6+L6+AG6-AC6-AB6-AA6) END

8

I’ve tried it & this one worked.

Do you know if this can be achieved in Webi too ? CASE function is not there in Webi, so is IF the best replacement ?

Can you please help with this one too.

thanks,

ashwin (BOB member since 2010-04-26)

9

Yes, nested IFs, just like in Excel are possible in Webi.