Omitting a null field in a calculation

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?

Help

Accepted answers

fmvizcaino

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.

2020-01-24 Omitting a null field in a calculation.yxmd

All comments

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.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335