Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Formula Help: Custom Formula Statement

Highlighted
8 - Asteroid

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

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
9 - Comet

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

Highlighted
8 - Asteroid

Thank you!  Much appreciated. I learn something new everyday.

Labels