If then else and add in query formula

[Former Member]

Hi

I have the following.

A add B add C add ( if D is < 20 add D) else (if D is >=20 add E)

I would like to know how to do this especially the adding of D and E with a else in between.

I know in the Bex formula a else is a + sign and this is confusing me.

Thanks so much,

Colin

[Former Member · Accepted solution]

A boolean true = 1 in BEx formula. False = 0. You have to remember this because boolean formulas are a bit different in BEx. It's based on the result of the operator (i.e. if D = 10 and you have a D < 20 check in your formula, D < 20 would equal 1 as the boolean result).

Try This in your BEx Formula:

A + B + C + (D < 20 * D) + (D >= 20 * E)

So, for instance, if

A = 1

B = 1

C = 2

D = 10

E = 15

1 + 1 + 1 + (1 * 10) + (0 * 15) = 13

In this case, D < 20 is true so in the BEx formula, true = 1 and false (i.e. D >= 20) = 0. It gives the result of adding the value of D and not adding the value of E (since it's zeroing it out).

Next example:

A = 1

B = 1

C = 2

D = 20

E = 15

1 + 1 + 1 + (0 * 20) + (1 * 15) = 18

This time it added the value of E and zeroed out the value of D, since D was >= 20.

I think this will work. Test it out to ensure the syntax is correct in the formula.

[Former Member]

Thanks for the help - have a good day.

[Former Member]

Hi,

First make in-between-results:

F for A + B + C

and G for ( if D is < 20 add D) else (if D is >=20 add E)

This last one would be: ((D < 20)(D)+((D>=)(E))

Then G+F for your result.

Success

Udo