Omitting a null field in a calculation

[johneodell]

I have a group of calculations in my workflow that are simple addition. The issue is that some of the fields may be null so I have to convert them to "0" before the calculation takes place then convert them back to null before the output so my spreadsheet isn't littered was zeros. For example:

[data1] + [data2] + [data3] + [data4] + [data5] = [data total]

in some cases [data1] may be null, in other cases fields [data3] and [data5] may be null, etc. Is there a better way to tell Alteryx to skip the nulls and just add the info that is there?

[fmvizcaino]

Hi @johneodell ,

The easiest way is to use the function tonumber for all fields of your sum as below. 

tonumber([data1]) + tonumber([data2]) + tonumber([data3]) + tonumber([data4]) + tonumber([data5])

LEt me know if that works for you.

Best,

Fernando Vizcaino

[johneodell]

Hi @fmvizcaino 

That's basically what I'm doing now. I've got a Multi-Field formula that is changing the appropriate variables to "0" but i was just wondering if there was a better way. In Excel I'd just use a SUMIF.

[fmvizcaino · Accepted solution]

Hi @johneodell 

The way I have showed you, you don't need to convert all to 0 and then transform once again to null. It is converting and summing directly at the same formula and not changing the original columns as well.

I'm attaching an example to show you also another option to sum without convert the column values.

Best,

Fernando V.

[johneodell]

Thanks @fmvizcaino !!!

I was under the impression that the "ToNumber" function would alter the field going forward through the rest of the workflow. That's the solution I'll be using!