Allow calculations on Null numeric values.

[jarrod]

I'm sure there's a reason behind it, but can we please be allowed to run calculations on null values in a formula tool? right now, if we sum three values (1 + 3 + [null]) it produces [null], can the formula tool just ignore the null values? the only way around this is to fill the [null] cells with a value and that adds an additional step to what should be a fairly straight forward process. That value would have to be different for a multiplication formula vs an addition formula in order to not change the answer materially whereas ignoring the value is a more consistent solution. 

[joshuaburkhow]

@jarrod good idea, the main issue is that any of the R tools will throw a fit as most cant take in Null values. Maybe a checkbox "ignore null values" would work? 

[SeanAdams]

This is an interesting one @jarrod - null technically means "unknown" so null should fail all comparisons and numeric operations all get tainted with the null (1 + 2 + unknown = unknown)

That being said - I do get that this is a nuance which we have to teach to people, it is not obvious to a non Database-centric audience.

I'm torn ...

[NicoleJohnson]

Perhaps a middle ground solution - a warning in the results window that at least let’s you know when this occurs, and then possibly something along the “check box to ignore nulls” route as well. Makes it more obvious when it occurs, with fewer steps than having to add a tool, but still defaults to normal database behavior?

[jarrod]

not that we want to model data behavior off of Excel, but if we type in a formula there and reference an empty cell, it basically treats it as 0. logically, i think that's the way to treat it with regard to the formula tool specifically.

Here's how i think about this issue:

I can put a data cleansing tool in front of the formula tool and replace all nulls with 0's (default behavior there). Then i can create the formula which references these newly created "0" values. Or i can have the formula tool do that innately. If i need those nulls to be something else, like 1, then i'd still have to put the cleansing tool in front anyway, so i'm not really saving steps there by having a different "default".

I guess i can't see a circumstance where i would ever want a formula tool to output 1+2+null = null. In the case of multiplication, it's a moot point as either way i'm ending up with 0 or null, it just mucks up the downstream processes by having null count as a "value" in this instance. 

Now, this doesn't take away the importance of database values needing null - we aren't replacing all values as null, just the output from the formula tool.

[jdrummey]

When we're doing a SQL SUM() and have row values of 1, 2, and null then SUM([value]) = 1 + 2 + null = 3, i.e. the null has an implicit 0/is ignored. So this behavior is part of the SQL standard for adding by rows. Why not have a similar capability for summing across columns? I regularly run into data that requires this addition and either do a Transpose/Summary/Crosstab pattern so I can use the SQL SUM() or have to write something like this:

IF ISNULL([columnM]) AND ISNULL([columnN]) THEN

    NULL()

ELSE

    IF ISNULL([columnM]) THEN 0 ELSE [columnM] ENDIF

   + IF ISNULL([columnN]) THEN 0 ELSE [columnN]) ENDIF

ENDIF

FYI @jdunkerley79 built a SUM() function that ignores Nulls in his Alteryx formula add-ons: https://github.com/jdunkerley/AlteryxFormulaAddOns/blob/master/README.md.

Jonathan