IF ELSE condition formula - BEx query designer

[Former Member]

Hello guys,

I have 3 columns - C1, C2 and ( C1 %A C2 ).

In C1 % C2, i have to write a formula.

If ( C2 = 0 ) THEN ( C1 * 100 ) ELSEIF (C2 NE 0 ) THEN ( C1 %A C2 ).

The formula I wrote does not seem to be working. I am using this formula in cell reference

(C2 == 0) * C1 * 100 + (C2 <> 0) * C1 %A C2.

This is not working.

When I use this formula in the cell reference, then the result is still 'X' (i.e. undefined)

If I use only this:

(C2 == 0) * C1 * 100

Then the formula works fine (eg. if C1 = 2, C2 = 0 result is 200%).

I tried to use 'OR' instead of '+' in the formula. Nothing seems to work.

Please advise.

Thanks

[former_member200211 · Accepted solution]

Hi Sachin,

You can try the following.

( C1 %A C2 ) * Count (C1) + ( C1 * 100 ) * DELTA(C1)

This will calculate as follows:

If Column C1 is having value ie NE 0, then, ( C1 %A C2 ) will be calculated. And if column C1 is equal to 0 then ( C1 * 100 ) will be calculated.

Just try this and let me know if it works....

Regards

Lavanya

[Former Member · Accepted solution]

Hi Lavanya,

Thanks for your response.

In my question, I have to check for C2 == 0 or C2 <> 0.

I guess the formula might be,

( C1 %A C2 ) * Count (C2) + ( C1 * 100 ) * DELTA(C2).

Regards

Sachin

[Former Member · Accepted solution]

Hi Lavanya,

I tried the same formula now. It doesn't work. I still get 'X' for the records where the denominator is zero.

Thanks.

[neelesh_jain3 · Accepted solution]

Try this, (C2 == 0) * C1 * 100 + NDIV0(C1 %A C2) * (C2 <> 0)

If not then try this, (C2 == 0) * C1 * 100 + NDIV0(C1 %A C2) * (C2 > 0) + NDIV0(C1 %A C2) * (C2 < 0)

Cheers,

Neel.

Edited by: Neelesh Jain on Sep 17, 2009 11:56 AM

Edited by: Neelesh Jain on Sep 17, 2009 12:01 PM

[Former Member]

Hi Sachin,

Is your requirement that if in case C2 equals 0, then consider C2 as 1

In case you are still facing the issue, then can you try creating a new formula as (C2==0)1 + (C2 <> 0)C2. Keep this formula hidden

And to get C1%AC2 use C1%AFormula

Thanks

[Former Member]

Hi Guys,

Thanks to everyone for their responses. Using NDIV0 solved the issue. Thanks again.