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

Formula Help: Custom Formula Statement

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



Alteryx Certified Partner
Alteryx Certified Partner



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)




Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
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




8 - Asteroid

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