Formula Help: Custom Formula Statement

Question

I need some formula suggestions or help.  Below are two formulas in which I am trying to assign a 1 if below a certain threshold.  I am trying to say if the metric is below a certain level or if it is blank then assign it a 1, else 0.  I have tried the following below but it is not picking up on the null or blank portion.  I will admit that I have a basic level of craftsmanship when it comes to writing formulas, so I would appreciate your help with the following below:

If [Net_Patient_Rev_3Y_Growth]<0 or "" then 1 else 0 endif

If [Ebitda_3Y_Growth]<=-.10 or "" then 1 else 0 endif

MarqueeCrew · Accepted Answer

@dshaw,

If we assume that the incoming data for these fields is a NUMERIC field, then "" isn't reasonable.  A number can be NULL but not blank.  IsEmpty() will test for either Null or Empty variables.

I happen to like In-Line IF statements:

IIF([Net_Patient_Rev_Growth] < 0 or IsEmpty([Net_Patient_Rev_Growth]),1,0)

IIF([Ebitda_3Y_Growth] < -.1 or IsEmpty([Ebitda_3Y_Growth]),1,0)

Cheers,

Mark

brindhan · Accepted Answer

Hi @dshaw

You need a separate statement to deal with Nulls.

This should give you your result.

If [Net_Patient_Rev_3Y_Growth]<0  or

isnull( [Net_Patient_Rev_3Y_Growth]) then 1 else 0 endif

Cheers,

B