Sum Total Issue - NULL ?

I am having an issue with a total formula - I am trying to create a total column to sum 5 x columns of data - for 3 of the 20 rows I am getting 0 as the total unexpectedly.

I searched the forum and used the select function to ensure each of the subsidiary columns were "Double" - I also used data cleansing to ensure that blanks / 0 were replaced but still I am getting the nil values for these rows.

Would anyone have any ideas of how I might resolve ?

Thanks

Chris

Best Practices

Accepted answers

JoshuaGostick

Could you send me a screenshot of how the data looks like at each step? In particular, could you show me a row where the total is coming out as null?

Thanks,

Josh

JoshuaGostick

The reason why the select and data cleansing tools need to be added first is because Alteryx will automatically give a null value if it adds a number with a null value. The null value has to be set to 0 first if you want to add correctly.

With regards to the "ICR $000" field, perhaps it contains some strange data like null values?

Thanks,

Josh

JoshuaGostick

Could you show me how the data looks like just before the formula tool?

Thanks,

Josh

JoshuaGostick

That's great!

The workflow attached should hopefully fix the issue.

Tracker Output.PNG

Thanks,

Josh

Tracker Output Solution.yxzp

JoshuaGostick

Great, I'm glad to hear that!

Basically, Alteryx was adding the columns incorrectly because some of the columns contained null values. If you look at the picture of the excel file below, you can see how some columns just have empty values rather than 0s.

Tracker Output Data.PNG

Alteryx treats 0s and nulls differently. It can add 0s but not nulls. That's why when Alteryx tried to add the null values with other numbers in the Formula tool, it automatically outputted a null value.

That was why the first two columns worked because it was adding two 0s. But when it tried to add the third column, it was adding a null value and therefore not outputting the correct value.

The way to solve this was to add the data cleansing tool before the formula tool and selecting Replace with 0. This replaces all the null values with 0s and ensures that the column does not contain any null values. Now, that there are no null values, the formula tool was able to correctly add the values.

Thanks,

Josh

All comments

JoshuaGostick

Hi @ChrisMcN,

Based on what you've said, it sounds like the total should be showing the correct value. Would it be possible by any chance to show the workflow or a sample of the data?

Thanks,

Josh

ChrisMcN

Dumb question - how can I copy from an Alteryx model onto the page here ?

Chris

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703