Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Formula Help: Custom Formula Statement

dshaw
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

 

 

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
brindhan
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

dshaw
8 - Asteroid

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

Labels