Multiple "IF" conditions in Bus. Obj. Report formula

building reports desktopintelligence
1

From the Bus Obj. User application, can you create a column and define a
formula with multiple ìifî statements? I have tried 2 ways: 1) Make each ìIFî statement a separate variable and try to combine them in the formula and 2) Write out all the ìIFî statements somehow trying to either nest them or use ìORî.

If specifics will help, I already have a number of complex formulas to convert a date to a datecode (datecode=a 3-digit number YWW) (WW=week number of the year, Y = last digit of the year i.e. 8 from 1998). Included in the formula is this conversion plus subtracting an older datecode from it. All this is defined as a variable and works fine.

I have constructed a field called YEARFILTER which subtracts only the 1st digit (the Y) between the 2 datecodes. If the Y=1, then I need to subtract 48 from the final result in order to show actual delta between years. For example, the difference in weeks between 905 and 850 is not 55 weeks as a straight subtraction would indicate. It is really 7 weeks. Hence, if Y=1 than I would need to subtract 48 from the final result and if Y=2 I would need to subract 96 from the final result, etc.

The formula I am gunning for is one that will say: Look at the YEARFILTER variable. If it equals 1, then subtract 48 from the FINALDELTA variable in that record. If it equals 2, then subtract 96 from the FINALDELTA variable. If it equals 3 subtract 144 from the FINALDELTA variable. Otherwise the result = FINALDELTA. (NOTE: FINALDELTA is the straight subtract between the 2 datecodes)

My futile attempts are below: (as you can see, I am new at this) Simplified (only 2 conditions for trying to figure this out) =If (( = 1) (if = 2 then - 96 else <finaldeltarev))) Then ( -48) Else <finaldeltarev

or --define the sub-if statements as variables and somehow combine them in the formula
=If ( = 1) Then ( -48) is variable and
=If ( = 2) Then ( -96) is variable etc.

You-all have bailed me out several times already. If anyone can tackle this, I would be very grateful.

–Beverly

B e v e r l y M c D o w e l l
Product Assurance

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 00-03-09 09:30:43 EST, you write:

The formula I am gunning for is one that will say: Look at the YEARFILTER
variable. If it equals 1, then subtract 48 from the FINALDELTA variable in that record. If it equals 2, then subtract 96 from the FINALDELTA variable. If it equals 3 subtract 144 from the FINALDELTA variable. Otherwise the result = FINALDELTA. (NOTE: FINALDELTA is the straight subtract between the 2 datecodes)

My futile attempts are below: (as you can see, I am new at this) Simplified (only 2 conditions for trying to figure this out) =If (( = 1) (if = 2 then - 96 else <finaldeltarev))) Then ( -48) Else <finaldeltarev

You are closer than you think. If you would just transcribe directly from the “english” version of your formula then you would have it!

=If =1 Then -48 Else If =2 Then -96 Else If =3 Then -144 Else

If you look at the formula, you will see that it is almost right out of your description of the formula that you need. So you were thinking along the correct lines! Here’s a hint: while you are building your formula, watch the “operators” section of the formula editor. It tries to show you what can come next. If you don’t see what you think you need, odds are you are trying to do something incorrect. If you work your way through building the formula as shown here, you should always see the “next piece” (except for the constants 1, 2, 3, 48, 96, and so on).

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

3

Beverly

What you need is the IF THEN ELSEIF statments.

Therefore the formula you are gunning for is as follows

IF yearfilter=1 THEN finaldelta - 48
ELSE IF yearfilter=2 THEN finaldelta - 96 ELSE IF yearfilter=3 THEN finaldelta - 144 ELSE finaldelta

Hope this helps

Min :slight_smile:

From the Bus Obj. User application, can you create a column and define a
formula with multiple “if” statements? I have tried 2 ways: 1) Make each “IF” statement a separate variable and try to combine them in the
formula and 2) Write out all the “IF” statements somehow trying to either
nest them or use “OR”.

If specifics will help, I already have a number of complex formulas to
convert a date to a datecode (datecode=a 3-digit number YWW) (WW=week
number of the year, Y = last digit of the year i.e. 8 from 1998). Included
in the formula is this conversion plus subtracting an older datecode from
it. All this is defined as a variable and works fine.

I have constructed a field called YEARFILTER which subtracts only the 1st
digit (the Y) between the 2 datecodes. If the Y=1, then I need to subtract
48 from the final result in order to show actual delta between years. For
example, the difference in weeks between 905 and 850 is not 55 weeks as a
straight subtraction would indicate. It is really 7 weeks. Hence, if Y=1
than I would need to subtract 48 from the final result and if Y=2 I would
need to subract 96 from the final result, etc.

The formula I am gunning for is one that will say: Look at the YEARFILTER
variable. If it equals 1, then subtract 48 from the FINALDELTA variable in
that record. If it equals 2, then subtract 96 from the FINALDELTA variable. If it equals 3 subtract 144 from the FINALDELTA variable. Otherwise the result = FINALDELTA. (NOTE: FINALDELTA is the straight
subtract between the 2 datecodes)

My futile attempts are below: (as you can see, I am new at this) Simplified (only 2 conditions for trying to figure this out) =If (( = 1) (if = 2 then - 96 else
<finaldeltarev))) Then ( -48) Else <finaldeltarev

or --define the sub-if statements as variables and somehow combine them in
the formula
=If ( = 1) Then ( -48) is variable and
=If ( = 2) Then ( -96) is variable
etc.

You-all have bailed me out several times already. If anyone can tackle
this, I would be very grateful.

–Beverly

B e v e r l y M c D o w e l l
Product Assurance

Listserv Archives (BOB member since 2002-06-25)