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
Solved! Go to Solution.
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
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
Thank you! Much appreciated. I learn something new everyday.