Formula Assistance - Formula contingent on value? 'unless' condition?

building reports webintelligence
1

Hi,

I have the formula shown below. I need to add one more condition, but I am struggling to figure this out. With the formula below (which does work well), I want to modify the formula so that IF [Note Pmt Sched Total Amount] - [Note Pmt Sched Escrow Amount] is less than 0.01, then “test”

If [Note Bill Type Code] InList (“B”;“C”) And [Note Pmt Sched Type Code] InList (“A”;“B”;“C”;“D”;“E”;“F”;“G”;“J”) Then [Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Note Pmt Sched Escrow Amount] ElseIf

It’s almost like I need an ‘unless’ formula, but I don’t think that exists. I guess another way of putting it would be…Then [Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Note Pmt Sched Escrow Amount] UNLESS < 0.01 THEN “test”

Any ideas?

2

Actually, let’s do this, here is a better/clear/easier example of what I want:

In this formula below, how can I modify to say that IF THE RESULT OF [BILLING PRINCIPAL INTEREST AMOUNT]/2 IS NULL THEN and do a different calculation here?

=If [Note Bill Type Code] = “A” Then ([Billing Principal Interest Amount]/2) ElseIf

3

Have you tried something like this?

=If [Note Bill Type Code] = “A” Then 
  If IsNull([Billing Principal Interest Amount]/2) Then
    SomeOtherCalculation
  ElseIf
    [Billing Principal Interest Amount]/2
Else
  "If you do not have and Else clause the formula will evaluate to NULL if the logic makes it here"

I would think the only way [BILLING PRINCIPAL INTEREST AMOUNT]/2 would result in NULL is if [BILLING PRINCIPAL INTEREST AMOUNT] is NULL, so why not just check that?

=If [Note Bill Type Code] = “A” Then 
  If IsNull([Billing Principal Interest Amount]) Then
    SomeOtherCalculation
  ElseIf
    [Billing Principal Interest Amount]/2
Else
  "If you do not have and Else clause the formula will evaluate to NULL if the logic makes it here"
4

Thanks. Yeah, I am struggling a bit getting the outcome I want, I got some strange issues with your example, but I think you have the right idea of what I want.

Is there a way to structure the formula to say (this didn’t seem to work, I don’t think I have IsNull correctly used here):

=If [Note Bill Type Code] = “A” Where([Billing Principal Interest Amount] IsNull) Then ([Query 1 with LoanDly].[Note Pmt Sched Total Amount] - [Query 1 with LoanDly].[Note Pmt Sched Escrow Amount]) ElseIf [Note Bill Type Code] = “A” Then ([Billing Principal Interest Amount]/2) ElseIf …more conditions here

5

You cannot have a Where clause in an If statement. What believe you want is a nested If statement. Something this…

=If ([Note Bill Type Code] = "A") Then 
    If (IsNull([Billing Principal Interest Amount])) Then 
        [Note Pmt Sched Total Amount] - [Note Pmt Sched Escrow Amount]
    Else 
        /* [Billing Principal Interest Amount] is not null is implied here */
        [Billing Principal Interest Amount] / 2
Else 
/* [Note Bill Type Code] <> "A" is implied here */
    0

What version of BOBJ are you using? My text within “/…/” is a comment which is new to 4.3.

You can read up on the IsNull function here.

You can get the same information with WebI here…

image
image1477×565 62.8 KB

6

Thanks. I am using 4.2. I tried the comment, but that does not look like it works in 4.2. We should be on 4.3 soon.

I actually think I did get what I need using the nested formula! Thank you for your help!

7

If you just take out my text between and including “/**/” it should work for you.