If Then Else in BEx query

[former_member203111]

Hi

I need a help in BEx Boolean operator syntax.

I am trying to write "If Then Else" statement in BEx query.

I have a KF called "Actual Production" and then I have another KF called "Planned production"

Normally I have values in Actual production in all the months from Jan to Dec but for planned production I only have values from Oct to Dec so all prior months Jan to Sept are blank.

I like to write If Then Else statement like....

If planned production is blank Then populate it with Actual production Else show planned production

In other words in my planned production field, the blank values from Jan to Sept will be populated from actual production (same as actual) but it should show the planned values which I already have from Oct to Dec.

This is simple to achieve in webi but not working for me in BEx as I am not too familiar with Boolean expressions.

OR please let me know if there is another way I can achieve this.

Thanks and Regards

[Loed]

Hi SAP BRG,

Try this:

FORMULA = ( planned_production == 0 ) * actual_production + planned_production

Regards,

Loed

[former_member302041]

If you are working with Bex in HANA Studio/Eclipse then you have built-in function "if-then-else".

Otherwise use as suggested above...

[kohesco]

Hi BRG

(Month <= 9 )* (planned == 0 ) * (actual )

+ (Month <= 9 )* (planned <> 0 ) * (planned )

+ (Month > 9 ) * planned

• month <= September and planned is 0 -> actual
• month > September and planned <> 0 -> planned
• month > September -> planned

[kohesco]

([Planned Production]==0) * [Actual Production] + ([planned Production]<>0)*[Planned Production]

if planned production = 0 -> Boolean = 1 -> (1* actual=actual ) + (0*planned=0) = actual

otherwise planned production is not 0 than (0* actual =0) + (1* Planned=planned) = planned

[former_member203111]

Hi Koen,

Thanks for your reply.

Your syntax logic is absolutely correct but I made a little mistake while writing my question.

I want to get result based on the certain month, for example:

From January to Sept ---> If planned production = 0 Then Actual production Else Planned production

but from Oct to December-----> if the planned production = 0 then leave it blank or 0

In other words I have two logic in the one column.

Is it even possible in BEx ?

Thanks

[kohesco]

Hi,

if you translate your month to a formula variable , you will have the month as a keyfigure. With that key figure you can create yet again a Boolean expression:

([month_formula_variable] > 9 ) *( If planned production = 0 Then Actual production Else Planned production)

etc..

So yes you can

[former_member203111]

HI Koen,

The logic you mentioned with formula var is not working.

I have created a formula variable on cal month. May be I am not able to ask the question correctly, let me rephrase.

This is what I want.

(for Jan to Sept cal month)----> IF planned production = 0 THEN actual production ELSE planned production

(for Oct to Dec)-----> IF planned production is not equal to 0 THEN planned production

so in other words from Oct to Dec if there are numbers in planned production, populate those instead of actual production unlike the first part of the syntax.

Thanks

[kohesco]

Hi,

add your formula variable into your structure and run your report to check the formula variable whether it gives the correct output

[former_member203111]

Yes the formula variable for calender month gives me the correct value when add it to the struture but it doesnt work when I use in the syntax.

Can you help me write the accurate syntax correctly, may be I am not writing it correctly.

Assume: formula var = calmonthvar

thanks

[former_member203111]

FYI, this is what I wrote but it is not populating the desired results although there is no syntax error.