I am trying the following calculated field which does not error in the editor but doesn't return the expected values.
IF (SUM([P_L_Revenue]) = 0 AND SUM([BP_Revenue_Target]) > 1) THEN 0
ELSEIF SUM([P_L_Revenue]) >= SUM([BP_Revenue_Target]) THEN 1
ELSEIF SUM([P_L_Revenue]) < [Revenue Below Target] THEN 3
ELSE 2
END
Where I have null P_L_Revenue and $40,000 in BP_Revenue_Target, I do not get 0, ELSE 2 comes in to effect.
hi Mary,
...or you can put a ZN function in-front of each value...this just means any nulls get read as zeros.
So would be
IF (SUM(ZN([P_L_Revenue])) = 0 AND SUM(ZN([BP_Revenue_Target)]) > 1) THEN 0
ELSEIF SUM(ZN([P_L_Revenue])) >= SUM(ZN([BP_Revenue_Target])) THEN 1
ELSEIF SUM(ZN([P_L_Revenue])) < [Revenue Below Target] THEN 3
ELSE 2
END
