IF...ELSE.. STATEMENT in BEx Query Designer

[Former Member]

Hi,

Why there is no IF, ELSE function in BEx Query Designer?

Assuming I need to write the following logic, how can it be accomplished?

IF (A=0 OR B=0)

THEN 0

ELSE IF ( B/A <0)

THEN ' *'

ELSE

B/A * 100

i.e.

if A or B is equal to 0, then display the value of 0

else if B is negative (as A is either 0 or positive), then display the following text: ' *'

otherwise, display the value of B/A * 100

Thanks!

[Former Member · Accepted solution]

Hi,

You can achieve this by creating a new formula.

You have to use the boolean operations and mathematical functions. But we cannot display *. u have to display some value for that.

Khaja

[Former Member · Accepted solution]

Hi Khaja,

Let's assume for time being the '*' is changed to 0.

How do you write the formula for the above logic?

[former_member189638 · Accepted solution]

Are Characteristics A and B numerical values????

If yes then you can populate them in a formula variable FA and FB and you can create a Formula Key Figure and write the formula as below,

(FA == 0 OR FB == 0) * 0 + (FB/FA < 0) * 0 + (FB/FA >= 0) * (FB/FA * 100)

[Former Member · Accepted solution]

Hi Rakesh,

Thanks, very much appreciate that.

Out of curiousity, what's the differences between

- Equal to, =

- is equal to, ==

Thanks in advanced.

Am I right to say that there is no way BEx Query Designer is able to display the '*' text, for above if else situation?

[former_member189638 · Accepted solution]

Equal to is where you assign value (e.g. A =8 . Here A is assigned a value 😎

Is Equal to is a logical operation where you check whether a value is equal to or not (e.g. A ==8 . Here it checks whether A is equal to 8 or not. If the condition is true it returns 1 or returns 0).

As you said there is no way to populate * in the else part using a formula key Figure

[former_member189638]

You can create a Formula Key Figure and can use the Boolean Operators and Mathematical Functions to achieve your requirement.

[Former Member]

Hi Rakesh,

Thanks for your reply. But could you please advise how?

FYI, A & B are not key figures, they are rows (reporting items) that share the same key figure.

Thanks.

[former_member189638]

ok........I thought A and B are Key Figures. Also using Formula Key Figure it would not be possible to populate the text '*'

[Former Member]

If they have the numeric values then u can create a formula with replacement variable and use them in the new formula.

Khaja