Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Sum Total Issue - NULL ?

Highlighted
Meteor

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

Highlighted
Alteryx Certified Partner

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

Highlighted
Meteor

Hi

 

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

 

Chris

Highlighted
Castor
Castor

Hi @ChrisMcN 

 

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

 

Attach.png

 

Dan

Highlighted
Meteor

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

 

Total formulaTotal formula

 

 

 

 

 

 

 

 

 

Data CleansingData Cleansing

 

 

SelectionSelection

 

Incorrect OutputIncorrect Output

 

Thanks

 

Chris

Highlighted
Alteryx Certified Partner

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

Highlighted
Meteor

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

 

Chris

Highlighted
Alteryx Certified Partner

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

 

 

Highlighted
Meteor

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

Highlighted
Alteryx Certified Partner

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

Labels