Sum Total Issue - NULL ?

[ChrisMcN]

Hi

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

[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]

Hi

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

Chris

[danilang]

Hi @ChrisMcN 

To attach a workflow click the the area beside the paperclip below your reply

Dan

[ChrisMcN]

Thanks - its a huge workflow - so maybe the screenshots are better ... am hoping its a simple solution : 

Total formula

Data Cleansing

Selection

Incorrect Output

Thanks

Chris

[JoshuaGostick]

Hi @ChrisMcN ,

I think the order of the tools may be causing troubles for you.

Try adding the Select tool where you change everything to Double first.

Then, add the data cleansing tool to convert all nulls to 0s second.

Finally, add the formula tool where you calculate the total last.

Let me know if that works for you.

Thanks,

Josh

[ChrisMcN]

Thanks Josh - unfortunately I am still getting the same results when I do that ...

Chris

[JoshuaGostick · Accepted solution]

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

[ChrisMcN]

Those steps are the last part of a huge workflow - the problem I have is that I cant find in the previous formulas where a NULL result appears ... is there an easy way to track that down ?

I would also have thought that using the select / data cleansing function at the end of the workflow would have resolved any format type issues  ... 

Chris

P.S. I tested the model again and when I add the first two columns everything works but when I add the third "ICR $000" it seems to go wrong so guessing that column is at least one of the issues

[JoshuaGostick · Accepted solution]

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