Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Sum Total Issue - NULL ?

ChrisMcN
7 - 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

18 REPLIES 18
JoshuaGostick
11 - Bolide

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
7 - Meteor

Hi

 

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

 

Chris

danilang
19 - Altair
19 - Altair

Hi @ChrisMcN 

 

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

 

Attach.png

 

Dan

ChrisMcN
7 - 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

JoshuaGostick
11 - Bolide

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
7 - Meteor

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

 

Chris

JoshuaGostick
11 - Bolide

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

JoshuaGostick
11 - Bolide

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