BEx Query Cell Definition (If ..then...else with AND)

[Former Member]

Hello Experts,

I am struggling with a requirement which I need to define in the Cell definitions in Query designer. I have tried going through the numerous posts regarding this if..then...else in SCN but were of no help to me.

My requirement is as follows:

if expression1 then result1, else if (expression2 AND expression3) then result2, else (expression4 AND expression5) then result3

I tried to define this in the cell as below:

<expression1> * result1 + (<expression2> AND <expression3> ) * result2 + (<expression4> AND <expression5>) * result3

but somehow it does not work.

Any pointers of how to achieve this?

Thanks.

[yasemin_kilinc · Accepted solution]

Hi,

What do you mean by "not working for me". Does it provide wrong result or does it provide an 'X' . If you think it provides a wrong value, then try seperating the if else statements into different cells and check the results, the logic mentioned above is definitely correct for your statement. Please check step by step.

Regards

Yasemin...

[Former Member · Accepted solution]

Hi Yasemin,

by "not working for me", i mean the result is not coming according to my expectations. And in this case, I am getting an "X" in the report as result.

Further, can you pls explain --> "then try seperating the if else statements into different cells"

Do you mean I should use some more Helpcells and separate the if else statements?

Thanks.

[former_member209606 · Accepted solution]

Hi,

Create 4 more cells.

In the first cell logic as => <expression1> * result1

Second Cell =>  (<expression2> * (<expression3>  * result2 ))

Third Cell     =>  (<expression4> * (<expression5> * result3))

Fourth cell = cell1 +cell2+cell3.

Try this concept.

Regards

Sureshkumar C

[yasemin_kilinc · Accepted solution]

Hi,

If you are getting 'X' then this may be related with the dimensions you use in comparing variables. Try the whole formula such as

(nodim(var_1) > nodim(var_2) * nodim(kyf_1)

"then try seperating the if else statements into different cells" : for example try adding a formula for

each item in your formula (one for buchungsperiode, one for buchungsperiode ein gabe etc. I mean you can find the formula vaiable or key figure that causes 'X' in the results by seperating the formula into the items of the formula.

Regards

Yasemin...

[former_member182470 · Accepted solution]

Did you miss my reply? You will get great understanding with my blog.

[Former Member · Accepted solution]

this helped me to trace the source of error.

Thanks a lot!

[yasemin_kilinc · Accepted solution]

Your welcome

[BenedictV]

hi Kiran, Try this

<expression1> * result1 + (<expression2> * (<expression3>  * result2 )) + (<expression4> * (<expression5> * result3))

Thanks,

Benedict

[Former Member]

Thanks Benedict for your response.. but unfortunately this is also not working for me..

as per your response, i did change the formula as below..

[former_member182470]

I have tried going through the numerous posts regarding this if..then...else in SCN but were of no help to me.
Have you gone through this post?

How to achieve Slab Wise or Bucket Wise and Age Wise requirements in BEx

Please search this..